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

This article has been archived. It is offered "as is" and will no longer be updated.
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''goif 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_MSdbuseraccessgoprint N''print N'Creating sp_MSdbuseraccess'print N''gocreate 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   endgo/* End sp_MSdbuseraccess */exec sp_MS_marksystemobject sp_MSdbuseraccessgogrant execute on sp_MSdbuseraccess to publicgo
  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.
Properties

Article ID: 889696 - Last Review: 12/09/2015 01:55:27 - Revision: 1.3

Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition

  • kbnosurvey kbarchive kbprb kbtshoot KB889696
Feedback