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

• Microsoft SQL Server version 7.0

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.

Error 21770:[SQL-DMO]The name <stored_proc_name> was not found in the specified collection.

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 version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

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.

Error 21770:[SQL-DMO]The name 'TestProc' was not found in the specified collection.

Additional query words:

Keywords : kbbug7.00 kbSQLServ700bug kbSQLServ700sp1fix
Version : winnt:7.0
Platform : winnt
Issue type : kbbug