SQL Server 2005 Create Sysadmin Accounts

[Joe K.]

I have numerous SQL Server 2005 database instance that I would like to check
to see if the T1,T2,T3, and T4 user account have been created with sysadmin
permission.

If user account has been created, do not drop and recreate the user account.
Move to the next and account and make sure sysadmin permssion is the only
server role applied to these accounts.

Please help me create this scipt.

[Dan Guzman]

You can list sysadmin role members with sp_helpsrvrolemember:

EXEC sp_helpsrvrolemember @srvrolename = 'sysadmin';

Alternatively, you can query the catalog views directly for more
flexibility:

--list all sysadmin role members
SELECT
,ServerRoles.name AS ServerRole,
ServerRoleMember.name AS MemberName
FROM sys.server_principals AS ServerRoles
JOIN sys.server_role_members ServerRoleMembers ON
ServerRoleMembers.role_principal_id = ServerRoles.principal_id
JOIN sys.server_principals ServerRoleMember ON
ServerRoleMembers.member_principal_id = ServerRoleMember.principal_id
WHERE
ServerRoles.type_desc = 'SERVER_ROLE'
AND ServerRoles.name = 'sysadmin'
ORDER BY
ServerRoles.name;

--list specified server principals and any server role memberships
SELECT
ServerPrincipals.name AS ServerPrincipal
,ServerRoles.name AS ServerRole
FROM sys.server_principals ServerPrincipalsLEFT
JOIN sys.server_role_members ServerRoleMembers ON
ServerRoleMembers.member_principal_id = ServerPrincipals.principal_id
LEFT JOIN sys.server_principals AS ServerRoles ON
ServerRoles.principal_id = ServerRoleMembers.role_principal_id
WHERE
ServerPrincipals.type_desc
IN('SQL_LOGIN','WINDOWS_LOGIN','WINDOWS_GROUP')
AND COALESCE(ServerRoles.type_desc, 'SERVER_ROLE') = 'SERVER_ROLE'
AND ServerPrincipals.name IN ('T1','T2','T3','T4')
ORDER BY
ServerPrincipal;

For future questions, consider posting to the appropriate forum
(http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity in this case).
There is more activity there so you will likely get a faster answer.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/