Tahir Hassan's Blog

My Technical Notes

Tuesday, 15 March 2016

Logins are mapped to users via sharing the sid value.

To list all the users of the current database (this also lists the roles):


SELECT [user].* FROM sys.database_principals [user]


To get all the logins of the current server:




There is a sys schema in very database which contains these tables so I'd imagine that this schema must be "virtual" in some sense.

To views the user to login mappings:


SELECT
FROM
sys.database_principals [user]

Note that if a member of the server role sysadmin logs in, they will be mapped across to the user dbo even if dbo is mapped to something else. This can be easily verified by executing:


You will notice that for a sysadmin, the USER_NAME() will be dbo.