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

MORE INFORMATION

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.

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.