March 27, 2008

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

1 Response to "View the Windows Logins and Groups in SQL Server 2005"
  1. Anonymous said...
    November 22, 2009 at 11:56 AM

    Who knows where to download XRumer 5.0 Palladium?
    Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions