Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Not View Any DB Other than The One He Has Access Expand / Collapse
Author
Message
Posted Thursday, September 6, 2012 10:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 4:04 PM
Points: 93, Visits: 262
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.
Post #1355727
Posted Friday, September 7, 2012 12:01 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:04 PM
Points: 286, Visits: 580
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?
Post #1355764
Posted Friday, September 7, 2012 1:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 4:04 PM
Points: 93, Visits: 262
So is there no way to achieve it??
Post #1355783
Posted Friday, September 7, 2012 2:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:04 PM
Points: 286, Visits: 580
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?
Post #1355818
Posted Friday, September 7, 2012 11:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1356140
Posted Friday, September 7, 2012 2:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 4:04 PM
Points: 93, Visits: 262
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?
Post #1356235
Posted Friday, September 7, 2012 2:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 20,732, Visits: 32,496
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.



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)
Post #1356241
Posted Friday, September 7, 2012 2:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1356248
Posted Friday, September 7, 2012 4:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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."
Post #1356279
Posted Friday, September 7, 2012 4:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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."
Post #1356283
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse