Wednesday, December 23, 2009

Make DB Invisible to users in SSMS

To prevent the user from the seeing the System catalog i.e. sys.sysdatabases,sys.database etc and make it invisible to users from SQL management Studio (SSMS) you can use following command.

DENY VIEW ANY DATABASE TO 'Usersloginname'

However still user can access databases by application if require permission is set but if they try to log in SSMS , databases are not visible in SSMS and query window. The list of databases not visible in dropdown box. Still user can access and query it thru SQL Query analyzer.

USE DatabaseName
GO
Select Empno from Employee
Go

On best practice do not allow any users direct access at table level. Whatever they need grant permission by store procedure / functions. Normally application users have access to execute the store procedure.