Granting User Access

[bikerboy718]

I have a SQL Server (2008 R2) with 10 databases. I have to grant a user access to a database using management studio. Is it possible for me to grant the user access to the server but restrict access to the other databases. Truthfully I would like for them to only see the database they need to access in the object explorer. Is this possible?

With Great Power Comes Great Responsibility!!!

Michael

 

[SQLBill]

Yes, there are three steps to do.
1. Create the login on the server
2. Provide the server login access to the database(s)
3. Provide the login the appropriate permissions on the database(s).

This can be done via the GUI or script.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[bikerboy718]

Thanks for the response Bill. I was able to restrict the access to the other databases. All except the system databases. Not sure of the best way to remove access for these. Also when the user account logs onto the server it still sees all of the databases in the object explorer. Now granted the user cannot connect but is there a way to limit them seeing the databases?

With Great Power Comes Great Responsibility!!!

Michael

 

[bikerboy718]

I figured it out. Once the users were created and mapped them to the appropriate databases. I was able to run the following command

DENY VIEW ANY DATABASE to login_name

and that allowed them to only see there databases from object explorer.


With Great Power Comes Great Responsibility!!!

Michael