Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer
    
+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

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.

share|improve this answer

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

share|improve this answer
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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