Limit Databases Visibility for a Group of Logins

Question

Limit Databases Visibility for a Group of Logins

Viewing 8 posts - 16 through 22 (of 22 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I was going to rephrase my previous answer, but after re-reading it I cannot think of anyway to make it clearer:

RBarryYoung (5/7/2009)
After checking this a few times, there doesn't seem to be anyway around it. Either a login has VIEW ALL DATABASES and will see all of the datbases or they don't and then they will only see the ones that they own. There does not appear to be anyway to grant a Login the right to VIEW a specific database.

Either all databases are visible to a Login, or only master, tempdb and any databases that that Login is the owner of will be visible. There are no other choices for SSMS.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Igor Makedon Ten Centuries Points: 1235 More actions · Answer 2

Thanks, RBarryYoung.

This had been my conclusion, and having been discontented with it I initiated this thread. I thought I was missing something. The requirement looks so natural, and I -- not much of a rookie, but not a pro DBA either -- could not find how to implement it.

It looks like Pablo(3:20 PM) was mistaken with his "1." statement.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

I have to admit, before today I thought that it was possible too. The "you can view what you can access" rule is generally pretty consistent and I was not aware that they had this very unusual exception.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

I was somewhat taken aback by this as well. It certainly isn't how I would have expected this to work, especially since in SQL 2000 it worked the way expected. There are quite few connect items for this problem, and all are closed by design by Microsoft. It seems like they are not understanding what the community wants for this.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Listing the databases only to those accessable by a login is not supported but has been requested.

Title Need VIEW DEFINITION permissions per database

Opened By Erland Sommarskog

Opened 4/26/2007

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273830

Description

A common question on the newsgroups is "What do I need to do so that users in Mgmt

Studio only see the databases they have access to?". Surely, they are accustomed to

this from SQL 2000, where EM would behave this way. Alas, there were issues with

how EM implemented this. It went and checked every database, and this could be

costly, particularly if there were many databases on auto-close.

SQL = Scarcely Qualifies as a Language

Igor Makedon Ten Centuries Points: 1235 More actions · Answer 6

I suppose that any application (Mgmnt Studio included) takes databases list from sys.databases, and it is this place where unaccessible databases are expected to be excluded from.

Well, db naming conflicts may become a curse in this case though.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

Igor Makedon (5/7/2009)It looks like Pablo(3:20 PM) was mistaken with his "1." statement.

My fault, I'm sorry.

I was relying on my experience on previous SS versions; looks like SS2K5 has stabbed me in the back.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

PaulB (5/7/2009)
Igor Makedon (5/7/2009)It looks like Pablo(3:20 PM) was mistaken with his "1." statement.
My fault, I'm sorry.
I was relying on my experience on previous SS versions; looks like SS2K5 has stabbed me in the back.

You and me both. This seems like such a common sense type thing. Best thing to do is to vote for that connect item posted by Carl:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273830

and get others to vote on it as well. SQL 2005 wasn't supposed to get a SP3, but enough feedback was submitted through connect to make it happen. The more votes, the higher it moves up the food chain.