·í³\¦h¸ê®Æ®w¦s¦b©ó SQL Server °õ¦æ­ÓÅé®É¡A¥i¯à·|¹J¨ì½wºC¦^À³±q SQL Server ¥ø·~ºÞ²z­û

/*******************************************************************************/
/* 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

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