:::: MENU ::::

Who has access to your database

  • Nov 11 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Who has access to your database

Once a user is logged in to your SQL Server instance, he may or may not have access to your databases. In order to access a database, SQL Server login of that user must be mapped to database user created inside the database. Use following command to list the logins who have access to your databases. I have used sys.database_principals system view for this information.

 
use DBName  
 GO  
 SELECT UserName = dp.name, UserType = dp.type_desc,  
 LoginName = sp.name, LoginType = sp.type_desc  
 FROM sys.database_principals dp  
 JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id  
 GO  
Consult us to explore the Databases. Contact us