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

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:
  1. 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
  2. Open SQL Query Analyzer, and then paste the code into a blank Query Analyzer window.
  3. 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:
  1. 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.
  2. Open the Sqldmo.sql file in SQL Query Analyzer.
  3. 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
  4. Search for the string that starts with "create proc sp_MSdbuseraccess."
  5. Run the batch that starts with "create proc sp_MSdbuseraccess'" in SQL Query Analyzer to re-create the original procedure.

Modification Type:MajorLast Reviewed:1/27/2005
Keywords:kbprb kbtshoot KB889696 kbAudDeveloper