May 17, 2013 at 11:22 am
We have a group of users who need SSMS access to a group of databases on SQL Server 2008 R2. Preventing them from connecting to or reading data in other databases on the same instance is easy enough, but I also want to prevent them from easy seeing the other databases.
I've read this can be done my making the user the Owner of the DB they need, then running DENY VIEW ANY DATABASE on user.
But first of all, I prefer to use AD groups when setting up security. I don't believe you can make AD Groups an object owner, and I will be using TWO AD groups to manage the security, one for users who will be able to import data and make schema changes, and another for users who should have read-only access.
How do I configure these users to have the minimum required access on their databases and to not be able to even see the others?
May 20, 2013 at 2:58 am
you can hide system databases => tools -options->open object Exlporer then click on Hide system objects in object Exlorer. resart instance.
You give user acess to each database by mapping with different rights.
May 20, 2013 at 11:13 am
Don't the settings in tools -options->open object Exlporer affect the local workstation only?
May 21, 2013 at 5:38 am
dan-572483 (5/17/2013)
We have a group of users who need SSMS access to a group of databases on SQL Server 2008 R2. Preventing them from connecting to or reading data in other databases on the same instance is easy enough, but I also want to prevent them from easy seeing the other databases.I've read this can be done my making the user the Owner of the DB they need, then running DENY VIEW ANY DATABASE on user.
But first of all, I prefer to use AD groups when setting up security. I don't believe you can make AD Groups an object owner, and I will be using TWO AD groups to manage the security, one for users who will be able to import data and make schema changes, and another for users who should have read-only access.
How do I configure these users to have the minimum required access on their databases and to not be able to even see the others?
You cannot hide just the databases a user does not have access too unless you make them the database owner and take away the VIEW ANY DATABASE server permission. It's not really an SSMS issue per se. It's really a limitation in the granularity of the SQL Server permission scheme although it appears in SSMS most often because that's what everyone uses as a client. In reality the list of databases shown in SSMS is fed by sys.databases and some of the row-level access checks are what is missing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply