Microsoft KB Archive/889696

= You may experience a slow response from SQL Server Enterprise Manager when many databases exist in an instance of SQL Server =

Article ID: 889696

Article Last Modified on 11/2/2007

-

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

-



SYMPTOMS
When 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.



CAUSE
SQL 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.



RESOLUTION
This 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.



MORE INFORMATION
To use the modified sp_MSdbuseraccess stored procedure code, follow these steps:   Copy the following code: /*******************************************************************************/ /* exec sp_MSdbuseraccess 'perm', 'dbname' -- selecting priv bit from specified database                      */ /* exec sp_MSdbuseraccess 'db', 'dbname'  -- select databases, must change the database if dbname is specified */ /* exec sp_MSdbuseraccess 'init', 'dbname' -- noop                                                      */ /*******************************************************************************/ print N'' print N'Dropping sp_MSdbuseraccess' print N'' go if exists (select * from master.dbo.sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdbuseraccess') drop procedure sp_MSdbuseraccess go

print N'' print N'Creating sp_MSdbuseraccess' print N'' go create proc sp_MSdbuseraccess @mode nvarchar(10) = N'perm', @qual nvarchar(128) = N'%' as set deadlock_priority low create table #TmpDbUserProfile (     dbid        int NOT NULL PRIMARY KEY,      accessperms int NOT NULL      )

create table #TmpOut (     name        nvarchar(132) NOT NULL,      version     smallint,      crdate      datetime,      owner       nvarchar(132),      dbid        smallint NOT NULL,      status      int,      category    int,      status2     int,      fulltext    int,      )

set nocount on

declare @accessbit int if (lower(@mode) like N'perm%') begin /* verify */ declare @id int, @stat int, @inval int select @id = dbid, @stat = status from master.dbo.sysdatabases where name = @qual if (@id is null) begin RAISERROR (15001, -1, -1, @qual) return 1 end

/* Can we access this database? */     declare @single int select @single = DATABASEPROPERTY( @qual, N'issingleuser' ) /*     if ((@single <> 0) or ((@stat & SQLDMODBStat_Inaccessible) <> 0)) begin  */ if ((@single <> 0) or        (DATABASEPROPERTY(@qual, N'isdetached') <> 0) or         (DATABASEPROPERTY(@qual, N'isshutdown') <> 0) or         (DATABASEPROPERTY(@qual, N'issuspect') <> 0) or         (DATABASEPROPERTY(@qual, N'isoffline') <> 0) or         (DATABASEPROPERTY(@qual, N'isinload') <> 0) or         (DATABASEPROPERTY(@qual, N'isinrecovery') <> 0) or         (DATABASEPROPERTY(@qual, N'isnotrecovered') <> 0)) begin select @inval = 0x80000000 select @inval return 0 end select @accessbit = has_dbaccess(@qual) if ( @accessbit <> 1) begin select @inval = 0x40000000 select @inval return 0 end

/** We can access this database, and we must locate the specified database to get the priv bit **/ declare @dbTempname nvarchar(258) declare @tempindex int SELECT @dbTempname = REPLACE(@qual, N']', N']]') exec (N'[' + @dbTempname + N']' + N'..sp_MSdbuserpriv ') return 0 end

/* If 'db', we want to know what kind of access we have to the specified databases */ /* If we are not in the master database, we are selecting a single database, and we want to correct role bit to save round trip */ if (lower(@mode) like N'db%') begin /* Make sure that you are in either the master database or the current database, so that you do not affect other databases. */     declare @dbrole int select @dbrole = 0x0000

if (db_id <> 1) select @qual = db_name

/* If dbname contains a single quotation mark ('), double the single quotation mark for the cursor because the cursor statement is inside two single quotation marks (''). */     declare @qual2 nvarchar(517) SELECT @qual2 = REPLACE(@qual, N', N''')

/* The preprocessor will not replace information within quotation marks, and we have to use the str function. */     declare @invalidlogin nvarchar(12) select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11)) declare @inaccessible nvarchar(12) select @inaccessible = ltrim(str(convert(int, 0x80000000), 11))

/* We cannot 'use' a database that has a version that is less than the minimum version. */     /* The SQL Server 6.0 version minimum is 406; the SQL Server 6.5 version minimum is 408. The SQL Server 7.0 version is 408; however,  it might change later. */     declare @mindbver smallint if (@@microsoftversion >= 0x07000000) select @mindbver = 408 else select @mindbver = 406

/* Select all matching databases -- we want an entry even for the inaccessible databases. */     declare @dbid smallint, @dbidstr nvarchar(12), @dbstat int, @dbname nvarchar(258), @dbver smallint declare @dbbits int, @dbbitstr nvarchar(12)

/* !!! If the database name contains a left bracket ([), the LIKE operator cannot find the name because the LIKE operator treats a left bracket as a wildcard character. */     /* !!! If the @qual2 variable is a percent sign (%), the equal (=) operator does not work. */     declare @temp int select @tempindex = charindex(N'[', @qual2) if (@tempindex <> 0) exec(N'declare hCdbs cursor global for select name, dbid, status, version from master.dbo.sysdatabases where name = N + @qual2 + N') else exec(N'declare hCdbs cursor global for select name, dbid, status, version from master.dbo.sysdatabases where name like N + @qual2 + N')

open hCdbs

/* Loop for each database; if the database can be accessed, recursively call ourselves to add the database. */     fetch hCdbs into @dbname, @dbid, @dbstat, @dbver while (@@fetch_status >= 0) begin /* The preprocessor will not replace information within quotation marks, and we have to use the str function. */        select @dbidstr = ltrim(str(convert(int, @dbid)))

/* If the database is a single user database and there is an entry for it in sysprocesses that is not us, we cannot use it. */        declare @single_lockedout int select @single_lockedout = DATABASEPROPERTY( @dbname, N'issingleuser' ) if (@single_lockedout <> 0) select @single_lockedout = 0 where not exists (select * from master.dbo.sysprocesses p where dbid = @dbid and p.spid <> @@spid)

/* First, see if the database can be accessed (not in load, not in recovery, not offline, not in single-use with another user besides us, and so on.) */ /*        if ((@single_lockedout <> 0) or ((@dbstat & SQLDMODBStat_Inaccessible) <> 0) or (@dbver < @mindbver)) begin   */ if ((@single_lockedout <> 0) or           (@dbver < @mindbver) or            (DATABASEPROPERTY(@dbname, N'isdetached') <> 0) or            (DATABASEPROPERTY(@dbname, N'isshutdown') <> 0) or            (DATABASEPROPERTY(@dbname, N'issuspect') <> 0) or            (DATABASEPROPERTY(@dbname, N'isoffline') <> 0) or            (DATABASEPROPERTY(@dbname, N'isinload') <> 0) or            (DATABASEPROPERTY(@dbname, N'isinrecovery') <> 0) or            (DATABASEPROPERTY(@dbname, N'isnotrecovered') <> 0) ) begin /* Inaccessible, but we can set dbo if we are an sa or if the suser_id function is the database owner sid. */           exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @inaccessible + N')') end else begin /* Determine whether the current user has access to the database. */           select @accessbit = has_dbaccess(@dbname) if ( @accessbit <> 1) begin exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @invalidlogin + N')') end else begin /* The current user does have access to this database, and we are not trying to obtain priv at this point. */              select @dbbits = 0x03ff select @dbbitstr = ltrim(convert(nvarchar(12), @dbbits)) exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @dbbitstr + N')') end end

fetch hCdbs into @dbname, @dbid, @dbstat, @dbver end /* while FETCH_SUCCESS */ close hCdbs deallocate hCdbs

/* Select sysdatabases information in the temp table first to avoid a deadlock in the restore process. */     if (@tempindex <> 0) insert #TmpOut select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, o.status, o.category, o.status2, DatabaseProperty(o.name, N'isfulltextenabled') from master.dbo.sysdatabases o where o.name = @qual else insert #TmpOut select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, o.status, o.category, o.status2, DatabaseProperty(o.name, N'isfulltextenabled') from master.dbo.sysdatabases o where o.name like @qual

/* 1. If on all databases, dbrole is dummy, we must obtain it later. */     /* 2. Do not double the single quotation mark (') characters in the database name. */     /* 3. To speed up the connection, the accessperms column only indicates whether the user can access the database. The column does not contain */ /*   permission information. We will retrieve the permission information by using the sp_MSdbuserpriv stored procedure when we need that information. */     /* !!! If the name contains a left bracket ('[), the LIKE operator cannot find it because the LIKE operator treats a left bracket  as a wildcard character. */     /* !!! If the @qual2 variable is a percent sign (%), the equal (=) operator does not work. */     if (@tempindex <> 0) select o.name, o.version, o.crdate, o.owner, o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0, LogOnSepDev = 1, o.category, t.accessperms, @dbrole, o.fulltext, o.status2, collation = convert(sysname, databasepropertyex(o.name, N'collation')) from #TmpOut o left outer join #TmpDbUserProfile t on t.dbid = o.dbid where o.name = @qual and (t.accessperms & 0x40000000 = 0) order by o.name else select o.name, o.version, o.crdate, o.owner, o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0, LogOnSepDev = 1, o.category, t.accessperms, @dbrole, o.fulltext, o.status2, collation = convert(sysname, databasepropertyex(o.name, N'collation')) from #TmpOut o left outer join #TmpDbUserProfile t on t.dbid = o.dbid where o.name like @qual and (t.accessperms & 0x40000000 = 0) order by o.name

DROP TABLE #TmpDbUserProfile DROP TABLE #TmpOut return 0 end go /* End sp_MSdbuseraccess */

exec sp_MS_marksystemobject sp_MSdbuseraccess go grant execute on sp_MSdbuseraccess to public go  Open SQL Query Analyzer, and then paste the code into a blank Query Analyzer window. Use the EXECUTE command to run the modified sp_MSdbuseraccess stored procedure against the SQL Server databases.

Note This code will mark the modified sp_MSdbuseraccess stored procedure as a system object by using the sp_MS_marksystemobject stored procedure.

If you implement this code and then decide to return to the default behavior, follow these steps:  Locate the original CREATE PROCEDURE script for the sp_MSdbusreaccess stored procedure.

Note The script is typically located in the Sqldmo.sql file in the Program Files\Microsoft SQL Server\MSSQL\Install folder. Open the Sqldmo.sql file in SQL Query Analyzer.</li>  Locate the following batch in the script, and then run the batch to drop the current version of the sp_MSdbuseraccess stored procedure: if exists (select * from master.dbo.sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name =               N'sp_MSdbuseraccess') drop procedure sp_MSdbuseraccess go </li> Search for the string that starts with &quot;create proc sp_MSdbuseraccess.&quot;</li> Run the batch that starts with &quot;create proc sp_MSdbuseraccess’&quot; in SQL Query Analyzer to re-create the original procedure.</li></ol>

Keywords: kbprb kbtshoot KB889696

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.