BUG: BOL Incorrectly Documents Permissions to System Stored Procedures

ID: Q241636

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 55837, 56428 (SQLBUG_70)

SYMPTOMS

There following system stored procedures check to verify that you are logged in as the system administrator (SA) rather than a member of the System Administrators (sysadmin) role as Books Online (BOL) states:

sp_resetstatus
sp_certify_removable
sp_add_file_recover_suspect_db
sp_cycle_errorlog
sp_create_removable

If you are a member of the sysadmin role and you attempt to run one of these stored procedures, you will receive the following error message:

Server: Msg 15003, Level 16, State 1, Line 0
Only members of the sysadmin role can execute this stored procedure.

CAUSE

These procedures are incorrectly checking to see if SUID <> 1. If SUID is <1>, then the execution of the stored procedure is aborted.

WORKAROUND

To work around this problem, log in as SA rather than as a member of the sysadmin role.

The exception to this rule is sp_cycle_errorlog. This stored procedure is basically executing DBCC ERRORLOG and members of the sysadmin role are able to execute that command directly. This is an undocumented DBCC command but the syntax is very simple. Members of the sysadmin role should run:


dbcc errorlog

and it will return the following message indicating that the errorlog has been cycled:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words:

Keywords : kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug