SQL View - Access problems

Category: sql server security

Question

cwilliams40 on Thu, 13 Dec 2018 11:47:09


Hi, hoping someone can help me with a problem I am facing. I have a SQL server with some databases in production use and everything is working. I need to provide a user access to a particular subset of data from one of the databases and to archive this I have created an SQL view. 

I added a login for the user (a domain user) with server role of public and no direct user mappings

I have then added a select permission on the view itself. If I authenticate as the user in SSMS I can see the DB but not expand to the view. Manually running the query select * from view returns The server principal domain\user is not able to access the database "DB" under the current security context.

If I follow the same process on other view in other databases it seems to work as I'm expecting but the views weren't created by me in that instance

Does it sound reasonable to assume this is a problem with this database only and if so what could the issue be? As a test I did grant the user dbreader on that DB that didn't make any difference to the ability to see the view, db owner did of course give full access

Thanks


 

Replies

Dan Guzman on Thu, 13 Dec 2018 12:38:15


I added a login for the user (a domain user) with server role of public and no direct user mappings

I have then added a select permission on the view itself. If I authenticate as the user in SSMS I can see the DB but not expand to the view. Manually running the query select * from view returns The server principal domain\user is not able to access the database "DB" under the current security context. 

You mentioned you added the login but no database user or mapping. To what user did you grant SELECT permissions on the view? Does the view access tables in different databases?

Mohsin_A_Khan on Thu, 13 Dec 2018 13:02:20


You said "no direct user mappings" but later you mentioned you granted the domain user "db_datareader" on the DB. Have you created the corresponding user (within the DB where the view exists) for that login?

To confirm, connect to the SQL instance in question--> expand Security-->Logins-->Right-click the AD login that you created-->Properties-->select the user mapping tab-->ensure that the login is correctly mapped to the user in that DB with "db_datareader". 

cwilliams40 on Thu, 13 Dec 2018 14:49:42


Hi, the view is limited to a single database. I've actually found my issue, I needed to grant connect explicitly on the database. All good now thanks for your response