2

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?

5

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:

SELECT IS_SRVROLEMEMBER('sysadmin','<LoginName>');

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:

EXEC sys.sp_helpsrvrolemember;

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 roles and logins.

  • +1 There is no reason to try and perform an action as a sysadmin, just let SQL Server tell you if it has the privilege or not. – Shawn Melton Jun 11 '13 at 14:39
4

You could use that account to create another login , and grant it sysadmin privileges.

  • 2
    This is not an action that only a sysadmin can perform. – Shawn Melton Jun 11 '13 at 14:29
  • I stand corrected. Can you let us know what other role can grant sysadmin priveleges in SQL 2000? – datagod Jun 12 '13 at 14:08
  • 2
    The securityadmin fixed server role is granted ALTER ANY LOGIN which means it can manage any login on the instance to include assigning permissions at the instance and database level. Microsoft has updated most of the documentation since SQL 2000 to bring attention to the fact that the securityadmin should be treated as a sysadmin (msdn.microsoft.com/en-us/library/ms175892(v=sql.105).aspx) – Shawn Melton Jun 12 '13 at 14:53
3

One way is to try executing something with xp_cmdshell such as ping. If you don't have this enabled you can enable it briefly for the test then disable it again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.