May 6, 2009 at 2:30 pm
I know how to make a single login view only user databases that it is owner of by using VIEW ANY DATABASE Permission.
Please see the attached image where TEST052009 login sees only one database[_a] that it created.
Is it possible to arrange a groupof logins that all see only certain subset of databases on an instance of SQL Server.
This is a model of the following situation: A hosting company has ~ 100 databases on its server. We as a client company have ~10 databases among them. We want all our ~20 customers to see only databases that are relevant to their common needs. We definitely do not want use any kind of "generic" -- one-for-all -- login.
May 6, 2009 at 3:05 pm
I'm probably missing something in your scenario.
Because of SQL Server security architecture all users have to have a LOGIN entry at the server level then you map that LOGIN entry to a specific USER entry in each database you want to grant access to the user.
Pretty straighforward, easy to implement and follow.
What's wrong with that standard approach?
_____________________________________
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.May 6, 2009 at 7:06 pm
>>What's wrong with that standard approach?
Nothing is wrong (or "a lot is wrong" if you wish...). It's just irrelevant.
Pablo:
You are talking about access to a database - one of a hundred that a human user views in her Management Studio Object Explorer pane.
While I am talking about their visibility: [human] user does not want look at 96 extraneous, meaningless names that look simply like a garbage on her screen.
Technically
[font="Courier New"]select name from sys.databases[/font]
should return 4 rows, the rest 96 should be unobservable for the login and its peers.
Please see my previously attached image.
Thanks for you attention.
May 6, 2009 at 7:36 pm
Igor Makedon (5/6/2009)
...You are talking about access to a database - one of a hundred that a human user views in her Management Studio Object Explorer pane.
While I am talking about their visibility: [human] user does not want look at 96 extraneous, meaningless names that look simply like a garbage on her screen. ...
A user can view any thing that they can access. Just give them CONNECT access to the database and they will be able to see it (though not necessarily much else in it).
[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]
May 6, 2009 at 9:07 pm
>>A user can view any thing that they can access. Just give them CONNECT access to the database and they will be able to see it (though not necessarily much else in it).
Yes, but it does not mean that the user CANNOT -- that's what I want -- view names of databases (s)he has not access to.
I want database to which the user does not have access were invisible, unobservable, transparent for the user whatever (s)he does. E.g., when (s)he opens Management studio (s)he can view only 4 his databases, and he does not have to rummage in 100 [garbage for him] names to find which of them are his 4.
May 6, 2009 at 9:47 pm
How are they seeing Databases that they do not have any access too? Have you given their Logins some additional privilege (like VIEW ANY DATABASE)?
[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]
May 7, 2009 at 7:54 am
>>How are they seeing Databases that they do not have any access to?
Easy, please see the attached image.
My question is: "Is it possible and if yes, what needs to be done to hide this score of embarrassing database names from users (many of them) who does not have access to them (databases)?"
May 7, 2009 at 8:20 am
Igor Makedon (5/7/2009)
My question is: "Is it possible and if yes, what needs to be done to hide this score of embarrassing database names from users (many of them) who does not have access to them (databases)?"
1- Yes, it is possible.
2- By granting privileges the right way.
If your users can see that "score of embarrassing database names" is just because somebody has granted embarrassing wrong privileges to those users 😉
Remember: it is never the arrow, it is always the archer's fault.
_____________________________________
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.May 7, 2009 at 8:43 am
>>By granting privileges the right way.
Now I know I was wrong when I thought "by granting pivileges the wrong way."
May 7, 2009 at 9:27 am
One thing I don't see listed here is that VIEW ANY DATABASE is granted to the public role by default when you install SQL Server, so you would have to revoke it from public before it really takes the ability to see the databases away from any of the logins.
REVOKE VIEW ANY DATABASE TO public
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
May 7, 2009 at 10:22 am
revoke view any database to public
create login te0507 with password='111111'
Now my login te0507 cannot see a single user database name (the two sytem databases apart).
[font="Courier New"]
select name from sys.databases
name
--------------------
master
tempdb
(2 row(s) affected)[/font]
How can I allow this login to view a few databases that I - sa - own?
May 7, 2009 at 10:44 am
The login has to be the database owner, not just a member of the db_owner role, but the actual database owner for the database to show up in the object explorer. This is covered in the following connect feed back as a by design limitation in SSMS that won't be fixed:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=182665&wa=wsignin1.0
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
May 7, 2009 at 11:07 am
Hmm, well the Microsoft response to that CONNECt seems somewhat contradictory in that they are equating "Not being the owner" of a database with "not having any permissions" on the database.
[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]
May 7, 2009 at 11:24 am
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.
[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]
May 7, 2009 at 11:28 am
Let me rephrase my question.
I am a system administrator and my login is "sa".
sa login owns database [pacrat]
What TSQL command(s) should sa execute in order to make a given database, say [_a] VISIBLE for login te0507, sa created by the script of 5:22 PM?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply