Tags

, , , ,


I wanted to write about this topic from a long time but for some reason it was going into back burner, but today, one of my blog reader asked this question and I thought I should write about it right away.

When we work in shared environments, by default SSMS shows the list of all the databases that are there in the server, but we might want that our databases should not be visible to someone from the other group.

There is a workaround to hide the list of databases from SSMS but in may not be application in all the scenarios. But if our environment supports then we can do it. Lets get on with our Lab work 🙂

USE master
GO
— Creating two Test databases named “user1” and “user2”
CREATE DATABASE user1
GO
CREATE DATABASE user2
GO

— Creating two Logins “User1” and “User2”
CREATE LOGIN [User1] WITH
PASSWORD = N’user1′,
DEFAULT_DATABASE = [user1],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;
GO
CREATE LOGIN [User2] WITH
PASSWORD = N’User2′,
DEFAULT_DATABASE = [User2],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;
GO

— Creating Users in the Respective databases with DEFAULT_SCHEME=[DBO]
USE [user1] ;
GO
CREATE USER [User1]
FOR LOGIN [User1]
WITH DEFAULT_SCHEMA = [dbo];
GO
USE [user2] ;
GO
CREATE USER [User2]
FOR LOGIN [User2]
WITH DEFAULT_SCHEMA = [dbo];
GO

— REVOKING the PERMISSION to VIEW any Databases from the two Logins 
USE [master];
GO
DENY VIEW ANY DATABASE TO [User1];
DENY VIEW ANY DATABASE TO [User2];
GO

Now if you will connect as User1 then you will not be able to see User2 database and if you connect as User2 then you will not be able to see User1 database. There is one catch though, you won’t be able to see your own database in Object Explorer as DENY VIEW settings prevents this. The below screenshot shows the databases that will be visible when we will connect as User1:

User1

Since database is not shown in the object explorer hence the user won’t be able to perform many activities from GUI.

There is a work around too if you want to see the database in object explorer, by making the user as the database owner: ( this solution may not be viable in many scenarios)

— Making User1 as the database owner of User1 database
USE user1 ;
GO
DROP USER user1;
GO
ALTER AUTHORIZATION ON DATABASE::User1 TO [User1];

Now if we will connect with User1 then we can see the database in Object Explorer as well..

user1_2

Hope this helps.

Cheers,

Subhro Saha

Advertisements