June 7, 2015 at 1:25 am
I have one scenario-
Let one instance has 10 databases. Db1, Db2...Db10.
There are few logins.sa is sysadmin. userA can view all databases with read permission to all Db
Case1
User1 has access to Db1 & he can see only Db1
Case2
User2 has access to Db2 & he can see only Db2
Both cases are possible by creating the owner of Db1 to user1 & Db2 to user2.& Deny view any database.
Problem 1-
Login user3 should access to Db1, Db2.& he should see only Db1, Db2.
Problem 2-
Can first two cases be created without making owner of the database or restrict some permission user1 on Db1.but he should see only Db1
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
June 7, 2015 at 3:11 pm
Instead of making people db_owners you might do better to assign users to only those fixed database roles that they actually need - make use of db_datareader and db_datawriter and so on rather than always going for db_owner. The fixed roles arr documented at https://msdn.microsoft.com/en-us/library/ms189121%28v=sql.90%29.aspx.
Tom
June 7, 2015 at 5:41 pm
Thank you dear for your reply. I know all this. But by this they can see all databases present on server.although they can't access them.but i had a condition they should see respective databases not all.
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
June 8, 2015 at 8:24 am
I got an identical request from a client a couple years ago, where they had multiple client databases on the same instance, and had to give their clients some SQL Server access. As far as I'm aware, if they don't want the login to be able to see any other databases in SSMS object explorer or sys.databases, denying VIEW ANY DATABASE and making them the database owner is the only hack.
If the problem is that the other databases have names that pick out other clients, obfuscating the DB names is probably a better way to do this. That way just seeing the name doesn't give away any information.
That's also important because there's a strange hole in the "deny VIEW ANY DATABASE and make database owner" hack. A login created like that can still pass the integers starting at 5 to DB_NAME() and get the names of all the user databases anyway.
For situations like this, obfuscating database names is probably the simplest approach. There's also the possibility of running multiple instances, and isolating customer DBs that way, but if you have a large number of databases, that can be impractical.
Cheers!
June 8, 2015 at 8:33 am
If you want to make some users unable to see some databases (other than system databases), you need to do two things:
1) make sure that any login you want to see a database (other than system databases) has their login mapped to a suitable user in that database
2) revoke or deny permission for GUEST to connect to databases (other than system databases) - run either REVOKE CONNECT FROM GUEST or DENY CONNECT TO GUEST (or preferebly both) in all non-system databases. Maybe deny VIEW DATABASE STATE and VIEW DEFINITION too (I tend to be a bit paranoid about security so I do things like that, but I suspect it's not really needed).
I say "other than system databases" although I think this will work for some system databases too (everything except MASTER and TEMPDB). But it would might be a nuisance to over-restrict access to, for example, MSDB.
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply