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

Question

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!

Answer 1

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