SQL Server 2005 Get Login name from User name

Question

How can i specify a user name for a specific database and get the Login for that user in a query?

Thanks.

Answer 1

Using the Security Catalog Views, you can get database and server principal information, like so:

USE [database_name]

SELECT sp.name AS login_name
FROM sys.server_principals sp
JOIN sys.database_principals dp ON (sp.sid = dp.sid)
WHERE dp.name = 'user_name'

I can't find a view that will give you all users, regardless of database, so this needs to be run within the context of the database of the login.