You may experience a slow response from SQL Server Enterprise Manager when many databases exist in an instance of SQL Server (889696)
The information in this article applies to:
- Microsoft SQL Server 2000 Personal Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Enterprise Edition
SYMPTOMSWhen many
databases exist in a default instance
or
in a named instance of Microsoft
SQL Server, you may experience
a slow response from SQL Server Enterprise Manager,
specifically when you drill into the Databases folder. If the Databases folder
contains more than a thousand databases, you could experience delays that are more
than five minutes. CAUSESQL Server Enterprise Manager calls the sp_MSdbuseraccess stored procedure to determine the accessibility of each database.
The sp_MSdbuseraccess stored procedure then runs against every database in SQL
Server to determine the user's permission level in that database. For x number of databases, the sp_MSdbuseraccess stored
procedure will run x + 1 times. These multiple calls can take several minutes to
finish. A busy system can make the calls take longer to finish.RESOLUTIONThis article includes a modified version of the sp_MSdbuseraccess stored procedure.
The
modified version of
the sp_MSdbuseraccess stored procedure can resolve the
problem by running the sp_MSdbuseraccess stored procedure in only those databases where
the user has permissions, instead
of running
the sp_MSdbuseraccess stored procedure in all the
databases. The more databases that
the user has access to, the less performance gain the
user achieves by using the
modified
version of the sp_MSdbuseraccess stored procedure. The
following information applies to the modified version of the sp_MSdbuseraccess stored procedure: - The user can only see the
databases that
they have access to in
SQL Server Enterprise Manager. A user has access to a
database when
one of the following conditions is true:
- The user has been granted access to a
database by using
the sp_grantdbaccess
stored procedure.
- The database contains a guest account.
- The
response time for SQL Server Enterprise Manager might not increase for users
who have access to many databases.
Modification Type: | Major | Last Reviewed: | 1/27/2005 |
---|
Keywords: | kbprb kbtshoot KB889696 kbAudDeveloper |
---|
|