View the Windows Logins and Groups in SQL Server 2005

You can retrieve permission and role information about a user from sql server's catalog views. The sys.server_principals lists the sql logins and groups having access to Sql Server. Let us see how to retrieve information using this view.

To find out all the Sql logins and groups

SELECT [name] as PrincipalName, type as PrincipalType, type_desc as TypeDescription, create_date as CreationDate,
modify_date as ModificationDate
FROM sys.server_principals
--WHERE type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP')
ORDER BY type_desc

To find out only the Windows login and groups

SELECT [name] as PrincipalName, type as PrincipalType, type_desc as TypeDescription, create_date as CreationDate,
modify_date as ModificationDate
FROM sys.server_principals
WHERE type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP')
ORDER BY type_desc

The different type of Principal Types are :
S = SQL login
U = Windows login
G = Windows group
R = Server role
C = Login mapped to a certificate
K = Login mapped to an asymmetric key

References : http://msdn2.microsoft.com/en-us/library/ms188786.aspx