Article ID: 229003
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q229003
BUG #: 54536 (SQLBUG_70)
SYMPTOMS
You receive the following error message if you attempt to manage permissions for user-owned stored procedures in the SQL Enterprise Manager (SEM), when the server is registered using a login that is not the owner of that stored procedure.
This problem does not occur while managing permissions for user-owned tables or views.
WORKAROUND
Here are two workarounds:
- Register the server in SEM using the login that is the owner of the stored procedure.
-or-
- Manage permissions using the T-SQL GRANT and REVOKE commands from the Query Analyzer.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
232570INF: How to Obtain Service Pack 1 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
MORE INFORMATION
Steps to Reproduce the Problem
- Register the server in SEM using the system administrator (SA) login.
- Create a login "TestUser" and permit access to the Northwind database for this user in the role of db_owner or db_ddladmin.
- Edit the registration properties of the server and register the server in SEM using the login of "TestUser".
- Create a stored procedure named TestProc in the Northwind database from the SEM.
- Edit the registration properties of the server and register the server using a login that is not the owner of the stored procedure "TestProc", but is a database owner (DBO) of that database.
- In the SEM, expand the databases folder by clicking the PLUS SIGN (+).
- Next, expand the Northwind database by clicking the PLUS SIGN (+).
- Select Stored Procedures so that the names of all the stored procedures in the Northwind database display on the right-side of the SEM.
- Right-click the stored procedure "TestProc" and select Properties.
- Click the Permissions button in the Properties window.
The following error message is returned:
Keywords: kbbug kbfix kbsqlserv700sp1fix KB229003