Sunday 28 June 2020

Get SQL Server Database Users

The following command will provide a list of all database users Including SQL Users and Windows Users:

Use DATABASE_NAME
SELECT
FROM sys.database_principals
WHERE default_schema_name = 'dbo'

If your requirements are to only fetch SQL Users or Windows Users, use:

SQL Users only:

SELECT 
FROM  sys.database_principals
WHERE  type_desc = 'SQL_USER' AND 
default_schema_name = 'dbo'

Windows Users only:

SELECT 
FROM  sys.database_principals
WHERE  type_desc = 'WINDOWS_USER' AND 
default_schema_name = 'dbo'