I'm cleaning up permissions on my SQL Server 2000, and I want to verify that a particular account still has sysadmin privileges.
What non-destructive tasks can only sysadmins do that I can use to confirm this?
You don't have to perform any task (destructive or non-destructive) at all.
You can use the built-in function
IS_SRVROLEMEMBER to find if a certain
LOGIN is a memeber of the
sysadmin server role:
Note that for roles other than
sysadmin the result will be positive (=1) for implicit membership as well.
You can use the built-in stored procedure
sp_helpsrvrolemember to get a list of all the explicit members of a certain role:
EXEC sys.sp_helpsrvrolemember @srvrolename = 'sysadmin';
Or a complete list of all explicit members of all server roles:
For SQL Server 2000 there is also the option to sort or filter the result set using:
CREATE TABLE #ServerRoles ( ServerRole VARCHAR(20), MemberName sysname, sid VARBINARY(85) ); INSERT INTO #ServerRoles (ServerRole, MemberName, sid) EXEC sys.sp_helpsrvrolemember; SELECT SR.MemberName, SR.ServerRole FROM #ServerRoles SR WHERE 1 = 1 -- Change this to any filter you want ORDER BY SR.MemberName, SR.ServerRole; -- Change this to any sorting you want DROP TABLE #ServerRoles; GO
For SQL Server 2005+ there is already a system view to be queried directly:
sys.server_role_members which is a many-to-many relationship table between