How to restrict SQL Login to see only ONE database - SQL 2005
Tuesday, January 04 2011 11:37 AM
I need to be able to dynamically create databases and logins but allow each user to see only their database in SQL management studio with SQL 2005 or above
Re:How to restrict SQL Login to see only ONE database - SQL 2005
Tuesday, January 04 2011 11:40 AM
I found this online and it actually works in SQL 2005 where user can only see the database that he/she has access to. I had to create a dll to handle the whole process and there are some tweaks that need to be done for proper execution but here is the raw logic. If you need the dll let me know.
You many need to drop teh schema first and it depends on how you are creating users
-- make sure they can view all databases for the moment.
GRANT VIEW ANY DATABASE TO UserLoginName_HERE
-- drop the user in the database if it already exists.
IF EXISTS (SELECT *
WHERE name = N'UserLoginName_HERE')
DROP USER UserLoginName_HERE
-- grant them ownership to of the database (ownership of dbo schema).
ALTER AUTHORIZATION ON DATABASE::DataBaseName_HERE to UserLoginName_HERE
-- deny ability to see ohter databases
DENY VIEW ANY DATABASE TO UserLoginName_HERE