deny view master database

Question

Post reply

deny view master database

river1

SSC-Insane

Points: 24309
More actions
January 15, 2014 at 10:34 am

#302847

Hi,
How can I deny view Master database to a login?
When I create a linked server the login can see the master...

Viewing 5 posts - 1 through 4 (of 4 total)

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Take a look at this article:http://support.microsoft.com/kb/75291

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

and to recap, all users, since they are in the public role, have access to master and tempdb;

however what they see in the master database is limited by permissions, and can be further restricted(ie DENY VIEW ANY DATABASE)

they can only see objects in sys.objects like dbo.sptvalues, and not any other tables/views/procs that are not part of the default install, if any extra objects were added to master.

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!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

One funny note on the DENY VIEW ANY DATABASE bit: I once had a request to prevent a login from being able to view the names of any databases on the instance other than their own. It turns out that that can only be done in an incredibly hack-y and convoluted way, but in the process I discovered that even with DENY VIEW ANY DATABASE I could still pass the integers greater than 4 to DB_NAME and get the names of all the user databases. A funny little loophole 🙂

SqlServer-MadMan Newbie Points: 1 More actions · Answer 4

Lowell,

Is there any other way to restrict what a user can actually "see" from the Master/Temp DB's? I'm asking because I have included the restriction for "Deny View Any Database" while also restricting schema permissions to Information_Schema and Sys. When I deny "execute" on the Sys schema nothing shows up for a user to see including their own database's data. I also get the error "The Execute permission was denied on the object 'sp_tables'" so I remove that restriction and everything shows back up again.

Is there a role that perhaps I could create that would trump the Public role and take away those tables while still allowing access to data within a created user database?

As you can see in the screenshot attached I have created a database called "performance" and over to the right there is a list of tables to choose from but look at all of the native db objects that are listed as well. The only objects I would like a user to see is the first table and the fourth from the top. The client will be confused as to what to choose and it will create chaos and long running queries from folks poking around.

Any suggestions?

Attachments:

You must be logged in to view attached files.