September 6, 2012 at 10:49 pm
Hi All,
My req is that I have to give a user permission to view only the DB he is a member of. So when he logs in using SSMS and he expands the object explorer her should only see the DB that he is a member off.
I tried doing it by denying the View Any DB server level permission. But Now he is not even able to view the DB he is a member of.
How do I achieve this goal. The reason is I have a shared DB server and I don't want logins to see those DBs that they are not part of.
Thank you.
September 7, 2012 at 12:01 am
As far as I know, once you revoke VIEW ANY DATABASE from a user then the only DBs that user can see are master and tempdb + any databases the user actually owns. Role membership, even db_owner, doesn't override this.
September 7, 2012 at 1:11 am
So is there no way to achieve it??
September 7, 2012 at 2:35 am
Never say never...but there is no way that I'm aware of.
September 7, 2012 at 11:33 am
You can DENY VIEW ANY DATABASE (REVOKE is not enough) and make the user the database owner (different than being in the db_owner Role) of the database:
USE [master]
GO
DENY VIEW ANY DATABASE TO [YourServerLogin];
GO
USE [YourDatabase]
GO
-- note: the server login cannot be mapped to a database user. if the login is mapped to a
-- user you first have to drop the user before making the login the database owner
-- also note: a database can only have one owner so this technique only works if each login
-- has its own database and the login should have full control over that database
ALTER AUTHORIZATION ON DATABASE::[YourDatabase]TO [YourServerLogin]
GO
edit: fix dbo
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 7, 2012 at 2:13 pm
So accoriding to the above logic. I have to Deny View Any DB first. THen make the login owner of the DB. Then it will be not be able to access all the DBs but it will be able to view the DB that it is the part of? Right?
There are two issues here:
1> I donot want the login to have owner permissions on the DB I want the login to have minimal permissions.
2> What if I want to do this for 2 or more logins? Since I can have only one login as a owner I can not accomedate all the logins.
Is there any work aroud other than making the login Owner of the DB?
September 7, 2012 at 2:20 pm
I'm not sure if there is. However, if the user(s) do not have permissions to the other databases, they will not be able to expand those other databases and see what is in them.
September 7, 2012 at 2:33 pm
na1774 (9/7/2012)
So accoriding to the above logic. I have to Deny View Any DB first. THen make the login owner of the DB. Then it will be not be able to access all the DBs but it will be able to view the DB that it is the part of? Right?
If they have permission to access another database those permissions will not be affected by the DENY VIEW ANY DATABASE. i.e. if a login is denied VIEW ANY DATABASE but is mapped into a database and has SELECT permissions on table dbo.SomeTable that login can still execute:
USE OtherDatabase;
SELECT * FROM dbo.SomeTable;
However, the login still will not see the database OtherDatabase in the list in Object Explorer. Without VIEW ANY DATABASE they will only see databases they own in the list (or when selecting from sys.databases).
There are two issues here:
1> I donot want the login to have owner permissions on the DB I want the login to have minimal permissions.
Then you'll have to forget the option I presented.
2> What if I want to do this for 2 or more logins? Since I can have only one login as a owner I can not accomedate all the logins.
Supporting multiple users is also a limitation of the technique I presented.
Is there any work aroud other than making the login Owner of the DB?
I am with the others, not that I know of.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 7, 2012 at 4:01 pm
opc.three (9/7/2012)
na1774 (9/7/2012)
So accoriding to the above logic. I have to Deny View Any DB first. THen make the login owner of the DB. Then it will be not be able to access all the DBs but it will be able to view the DB that it is the part of? Right?If they have permission to access another database those permissions will not be affected by the DENY VIEW ANY DATABASE. i.e. if a login is denied VIEW ANY DATABASE but is mapped into a database and has SELECT permissions on table dbo.SomeTable that login can still execute:
...
Unfortunately, the OP is trying to get SSMS working for this user. In that case this issue is becomes more about the combination of limitations between what SQL Server allows, and what SSMS requires in order to function well. In particular, there is no way that I know of to get the SSMS Explorer pane's treeview to work inside of a database (showing/expanding its Tables and other objects), if it cannot browse to it from the Server level. Which it cannot unless the user has VIEW ANY DATABASE (effectively) or is the Owner of the database.
(I've gone through this before myself. Its a real pain in the neck...)
[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]
September 7, 2012 at 4:05 pm
RBarryYoung (9/7/2012)
opc.three (9/7/2012)
na1774 (9/7/2012)
So accoriding to the above logic. I have to Deny View Any DB first. THen make the login owner of the DB. Then it will be not be able to access all the DBs but it will be able to view the DB that it is the part of? Right?If they have permission to access another database those permissions will not be affected by the DENY VIEW ANY DATABASE. i.e. if a login is denied VIEW ANY DATABASE but is mapped into a database and has SELECT permissions on table dbo.SomeTable that login can still execute:
...
Unfortunately, the OP is trying to get SSMS working for this user. In that case this issue is becomes more about the combination of limitations between what SQL Server allows, and what SSMS requires in order to function well. In particular, there is no way that I know of to get the SSMS Explorer pane's treeview to work inside of a database (showing/expanding its Tables and other objects), if it cannot browse to it from the Server level. Which it cannot unless the user has VIEW ANY DATABASE (effectively) or is the Owner of the database.
(I've gone through this before myself. Its a real pain in the neck...)
I least, I *think* that's true. I sure wasn't able to find any workable way around 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]
September 7, 2012 at 4:08 pm
RBarryYoung (9/7/2012)
opc.three (9/7/2012)
na1774 (9/7/2012)
So accoriding to the above logic. I have to Deny View Any DB first. THen make the login owner of the DB. Then it will be not be able to access all the DBs but it will be able to view the DB that it is the part of? Right?If they have permission to access another database those permissions will not be affected by the DENY VIEW ANY DATABASE. i.e. if a login is denied VIEW ANY DATABASE but is mapped into a database and has SELECT permissions on table dbo.SomeTable that login can still execute:
...
Unfortunately, the OP is trying to get SSMS working for this user. In that case this issue is becomes more about the combination of limitations between what SQL Server allows, and what SSMS requires in order to function well. In particular, there is no way that I know of to get the SSMS Explorer pane's treeview to work inside of a database (showing/expanding its Tables and other objects), if it cannot browse to it from the Server level. Which it cannot unless the user has VIEW ANY DATABASE (effectively) or is the Owner of the database.
(I've gone through this before myself. Its a real pain in the neck...)
Agreed. I do not think SSMS should be used in this way, as the user interface between a client and a database hosting resource, and it is clear that it was not intended to be. I do see a lot of requests for this type of lock-down effect though, so who knows, maybe Microsoft will get around to closing these loops at some point.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 7, 2012 at 4:18 pm
Or maybe someone will write a client version of SSMS that meets this same need. Should be doable by some one who has excellent knowledge of SMO (isn't that what SSMS using any way?).
September 7, 2012 at 4:20 pm
Just found this:
Connect > Management Studio should only show the databases that the login has access to by Mohamed
Another one closed as Won't fix :rolleyes:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 7, 2012 at 4:22 pm
Lynn Pettis (9/7/2012)
Or maybe someone will write a client version of SSMS that meets this same need. Should be doable by some one who has excellent knowledge of SMO (isn't that what SSMS using any way?).
It should be as simple as "inheriting from the SSMS object" and then overriding the "get databases" method :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 9, 2012 at 5:17 pm
opc.three (9/7/2012)
You can DENY VIEW ANY DATABASE (REVOKE is not enough)
..actually REVOKE is enough I believe. Certainly worked when I tested because it was something I thought might just achieve what the OP required; simply REVOKE against ANY database, then specifically add in permissions at a DB level. However, REVOKE VIEW ANY DATABASE has the same effect as DENY in terms of what a user can see in SSMS.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply