User datareader access problem

Question

User datareader access problem

jay-h

SSCoach

Points: 18816
More actions
August 21, 2019 at 2:37 pm

#3673151

I went to give a user (via AD account) datareader access to a number of databases. Everything seemed alright, her read permissions show up in server level security, and in each of the assigned databases. However she still cannot view them, getting a 'not accessible' message.
If I try viewing tables in these databases 'execute as user...' I can see them without problem.
The one other odd thing is that if she tries to look at properties of the server, she gets the error message
VIEW SERVER STATE permission was denied on object 'server', database 'master'(Microsoft SQL server , Error 300). That's never been an issue before so I tried granting view server state permission, but that still did not change anything.
...
-- FORTRAN manual for Xerox Computers --

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

It might help if you post the exact error message the user gets. If the user doesn't have permissions, the error is typically: select permission denied on the object...etc. An error about "not accessible" would be something different than permissions. And hopefully she is doing this against the same instance, using the same account that you are using for granting permissions.

Sue

Lowell SSC Guru Points: 323504 More actions · Answer 2

it sounds like you might have removed public permissions from the server,and specifically in master? could that have happened?

if the end user is using something like SSMS, they typically get errors related to SSMS trying select * from sys.databases and other DMV's to load the object explorer.

you could try GRANT VIEW SERVER STATE TO [domain\user] and see if that immediately fixes the issue.

VIEW SERVER STATE is overkill, but it adds permissions to all the DMV's.

if it does work, you will want to remove that permission, and grant some of the old permissions in master back to public.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jay-h SSCoach Points: 18816 More actions · Answer 3

To clarify

Yes I have confirmed the user is in the Public role

When attempting to connect to one of the datareader databases, she gets The database xxxxx is not accessible (Object Explorer)

If she right clicks on the server for properties (In include this because normally even a user with limited rights can get some property info without throwing an error)

Cannot show requested dialog (sql mgmt)

an exception ocurred while executing a Transact-SQL statement or batch (Microsoft.sqlServer.ConnectionInfo)

VIEW SERVER STATE permission was denied on object 'server, database 'master'. (Microsoft SQL Server, Error: 300)

When I run EXECUTE AS USER, I can successfully access tables in databases she has permissions, and cannot in other databases (exactly as expected)

...

-- FORTRAN manual for Xerox Computers --

Lowell SSC Guru Points: 323504 More actions · Answer 4

can you try EXECUTE AS LOGIN='domain\user' instead of execute as user, and execute from , say the master database as the context?

can you duplicate the user error then?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jay-h SSCoach Points: 18816 More actions · Answer 5

Following your instruction, I executed from within the database and from within master using EXECUTE AS LOGIN. Both were successful.

...

-- FORTRAN manual for Xerox Computers --

auaiomrn Ten Centuries Points: 1300 More actions · Answer 6

What version of Management Studio is the user using? Is it older than the version of the server she is connecting to?

Sue_H SSC Guru Points: 90860 More actions · Answer 7

Thanks for posting back the error message - that really does make a difference as it's an issue with permissions when trying to connect to the database, even though the user is mapped to the database. It is weird - especially with the execute as login and execute as user working fine from your end. I'd probably look at the user tokens in the database and see if I could see something odd with their account or groups. It should show her account as well as public...and any other groups she may belong to. Use execute as user:

EXECUTE AS USER = 'TheDatabaseUser'

SELECT * 
FROM sys.user_token

Sue

jay-h SSCoach Points: 18816 More actions · Answer 8

Duh. She was using SMSS 2012. I should have thought of that first, though we were working by email and I wasn't looking directly at her screen.

...

-- FORTRAN manual for Xerox Computers --