question

Larry Standard avatar image
Larry Standard asked

permissions to master database

We want to give a user access to one view in a specific database. We have limited the user's acess to database so that he can only select from view. However, When we set up an ODBC connection on the user's PC and then use MS Query from Excel to look a the data, we can see all views in the MASTER database as well as the view in the database that we want him to see. How can we stop this user from viewing the data (i.e select columns from the views) in the master database.

So how can I remove permissions from Master so that the user cannot look at data in the tables?

permissionssystem-databasessystem-views
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Kev Riley avatar image
Kev Riley answered

This is because the views and tables in master have select permission granted to public. When a user is not explicitly granted or denied permissions, then the effective permission is inherited from public.

Also, make sure that the users default database is set to the database where the view is, rather than master.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MileHiger avatar image
MileHiger answered
public has select permissions on them, so if you were to DENY the select permission... But, public is a built in default - and the Master does not list Public to deny the Select. Where is a solution for this? There are countless threads on this, but no step-by-step solution Example: http://www.sqlservercentral.com/Forums/Topic671366-359-2.aspx?Update=1
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MileHiger avatar image
MileHiger answered
Using odbc (SQL Server native client) this is the problem. The dbo.vgiswell is a shared view for this user. But, all of the system tables show up too. How can Public be set to deny Select so this does not happen? ![alt text][1] [1]: /storage/temp/496-\link+table+with+sys.png

\link table with sys.png (24.1 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.