CODEWITHSUNDEEP
sql-serversql-server-2008
Kyle
Kyle

Reputation: 33691

GRANT a user to view single database? (deny viewing the rest)

I want a user to log-in to SQL server and only see one database. This user can not be the database owner. I have tried doing DENY VIEW ANY DATABASE TO [username] and i would now like to GRANT to view a single database to [username] is this possible? how do i do this? Thank you.

edit: to clarify what i mean, i dont want the user to even see that other databases exist, not just that they cant go into the database.

thanks!

Upvotes: 14

Views: 38063

Answers (2)

user2566882
user2566882

Reputation: 84

After you deny view to any database to a specific user:

DENY VIEW ANY DATABASE TO <customerlogin>

If you make this user a db_owner for the specific database:

USE <customdatabase>
exec SP_changedbowner <customerlogin>

It will only be able to see and use the chosen database.

More details: https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/

Upvotes: 4

RThomas
RThomas

Reputation: 10882

I assume you are talking about SQL Server Management Studio here. In short, if you are unable to grant DBO to the user (which is perfectly understandable) then there is NOT currently a way to accomplish what you want.

You've gotten as close as you'll get by issuing 

DENY VIEW ANY DATABASE TO <customerlogin>

effectively hiding all databases from the user. Without making the user a DBO there is no way to open view ability back up to only one. This is not a feature that has ever been added per Mike Hotek well known SQL Expert.

You can see a much longer and more detailed discussion regarding this desire on this MSDN thread.

http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/a989ca87-660d-41c4-9dac-70b29a83ddfb

Upvotes: 12

Related Questions