SQL Server 2005 Get Login name from User name

问题:

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

Thanks.

回答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.

回答2:

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##')

回答3:

Or you can use the system stored procedure sp_helpuser.

exec sp_helpuser 'Username'