Discussion Forum

Main >> ASP.NET Technical Articles and Discussions>> How to restrict SQL Login to see only ONE database - SQL 2005

All Threads Register Login New Discussion
AuthorPostings
RickB

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

Post a Replyreply

AlexT
Developer
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

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

Post a Replyreply

AT Integrated Inc. | 909 Ridgebrook Road, Suite 112, Sparks, MD 21152 | T: 410-472-2490
Copyright © 2019 AT Integrated Inc. All rights reserved.