I have received this query on my fb last night ,One of my friend asking me to how can I hide the database from user’s because there are many developers and vendors are working on different different applications
and i want that to each user’s or developers only see his/her specific databases.
There are 2 ways we can do this:-
- SSMS
- T-SQL
Using SSMS
- –We create Two databases.
Create Database [ProductionDb]
go
Create Database [StagingDb]
go
- Create two logins for demo purposes but not Mapped to any databases
CREATE LOGIN [Production] WITH
PASSWORD=’P@ssword@123′
go
CREATE LOGIN [Staging] WITH
PASSWORD=’P@ssword@123′
go
- Right Click on the SSMS Tree (SQL Server Name)>Properties>Permissions>Click on the Login account, and then select view any databases -> deny.
- Right Click on the newly created databases > Properties > Files, and change the Owner to the newly created accounts
- Then you see only one database is visible to both users
- Through T-SQL
Create Database [ProductionDb]
go
Create Database [StagingDb]
go
- Create two logins for demo purposes but not Mapped to any databases
CREATE LOGIN [Production] WITH
PASSWORD=’P@ssword@123′
go
CREATE LOGIN [Staging] WITH
PASSWORD=’P@ssword@123′
go
–Deny logins to see databases
REVOKE VIEW ANY DATABASE TO [Production]
go
REVOKE VIEW ANY DATABASE TO [Staging]
go
— Change the Database owner
use [ProductionDb]
go
sp_changedbowner ‘Production’
go
use [StagingDB]
go
sp_changedbowner ‘Staging’
go
Hope it helps to many users!!!