My Technical Notes

Tuesday, 15 March 2016

SQL Server: How logins and users are linked

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:


select [login].* from sys.server_principals [login]

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 
    [user].name, [login].name, [user].sid 
FROM 
    sys.database_principals [user]
    INNER JOIN sys.server_principals [login] ON [user].sid = [login].sid

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:


SELECT SUSER_NAME() /* get login name */, USER_NAME() /* get user name */

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

References

No comments: