How can i specify a user name for a s开发者_开发知识库pecific database and get the Login for that user in a query?
Thanks.
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.
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##')
Or you can use the system stored procedure sp_helpuser.
exec sp_helpuser 'Username'
精彩评论