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