logins

How can I write a script to generate the list of SQL users that have sysadmin privileges. I would also like to do the same for the windows accounts as well.

Please let me know.

Thanks.

Hi,

SELECT CASE WHEN isntname = 1 OR isntgroup = 1 OR isntuser = 1 THEN 'WindowsLogin' ELSE 'SQLLogin' END

FROM syslogins

WHERE sysadmin = 1

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de

|||

The catalog view syslogins has been deprecated in SQL Server 2005 (for more information visit "sys.syslogins (Transact-SQL)" in BOL http://msdn2.microsoft.com/en-us/library/ms178593.aspx).

For SQL 2005 I would recommend using the new catalog views. Here is a sample on how to get the role membership information.

-- Select all role members (fixed role name, member name)

--

SELECT suser_name( role_principal_id ) as server_fixed_role_name,

suser_name( member_principal_id ) as member_login_name

FROM sys.server_role_members

ORDER BY role_principal_id

go

-- Select all role members

-- (fixed role name, member type, member name)

--

SELECT suser_name( sysrolemembers.role_principal_id ) as server_fixed_role_name,

principals.type_desc as member_login_type,

principals.name as member_login_name

FROM sys.server_role_members sysrolemembers,

sys.server_principals principals

WHERE sysrolemembers.member_principal_id = principals.principal_id

ORDER BY sysrolemembers.role_principal_id, principals.type_desc, principals.name

go

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Note that both queries in the above answers will miss Windows users that are not "directly" sysadmins, but inherit their sysadmin property from a Windows group to which they belong.

Thanks
Laurentiu