FIX: Error 21770 When Trying to Manage Permissions For User-Owned Stored Procedures in SEM

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.

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.

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: For more information, contact your primary support provider.

Steps to Reproduce the Problem

1. Register the server in SEM using the system administrator (SA) login.
2. Create a login "TestUser" and permit access to the Northwind database for this user in the role of db_owner or db_ddladmin.
3. Edit the registration properties of the server and register the server in SEM using the login of "TestUser".
4. Create a stored procedure named TestProc in the Northwind database from the SEM.
5. 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.
6. In the SEM, expand the databases folder by clicking the PLUS SIGN (+).
7. Next, expand the Northwind database by clicking the PLUS SIGN (+).
8. Select Stored Procedures so that the names of all the stored procedures in the Northwind database display on the right-side of the SEM.
9. Right-click the stored procedure "TestProc" and select Properties.
10. Click the Permissions button in the Properties window.

The following error message is returned: