SQL Server 2005 Get Login name from User name

Question

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

Thanks.

Answer 1

select * 
FROM
sys.server_principals AS log
WHERE
(log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name <> N'##MS_AgentSigningCertificate##')

Answer 2

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.

Answer 3

Or you can use the system stored procedure sp_helpuser.

exec sp_helpuser 'Username'