How to restrict SQL Login to see only ONE database – SQL 2005/2008

I found this online and it actually works in SQL 2005/2008 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 

USE [master] 
GO 
— make sure they can view all databases for the moment. 
GRANT VIEW ANY DATABASE TO UserLoginName_HERE 
GO 

USE DataBaseName_HERE 
go 

— drop the user in the database if it already exists. 
IF EXISTS (SELECT * 
FROM sys.database_principals 
WHERE name = N’UserLoginName_HERE’) 
DROP USER UserLoginName_HERE 
GO 

— grant them ownership to of the database (ownership of dbo schema). 
ALTER AUTHORIZATION ON DATABASE::DataBaseName_HERE to UserLoginName_HERE 
go 

USE MASTER 
go 

— deny ability to see ohter databases 
DENY VIEW ANY DATABASE TO UserLoginName_HERE 
go

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s