Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Not View Any DB Other than The One He Has Access


Not View Any DB Other than The One He Has Access

Author
Message
na1774
na1774
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 273
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.
MissTippsInOz
MissTippsInOz
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 597
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.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
na1774
na1774
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 273
So is there no way to achieve it??
MissTippsInOz
MissTippsInOz
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 597
Never say never...but there is no way that I'm aware of.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8291 Visits: 14368
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
na1774
na1774
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 273
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?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24295 Visits: 37999
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8291 Visits: 14368
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9456 Visits: 9517
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...)

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9456 Visits: 9517
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search