sp_MSdbuseraccess 는 EM으로 접속할때 각각의 데이터베이스에 접속권한이 있는지 확인하는 프로시저인데 이 프로시저를
약간 수정하여 EM으로 접속했을때의 응답속도도 향상시킬수 있고 더불어 권한이 있는 데이터베이스만 보이도록 셋팅할 수 있다.
다음 프로시저를 복사하여 실행하기만 하면된다.
(기존 프로시저와 다른 부분은 빨간색으로 표시했다. 관심있는 분들은 분석해 보시기 바랍니다.)
/*******************************************************************************/
/* 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
/* 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
그럼 다시 되돌리고 싶을 경우는 어떻게 할까?
위의 쿼리를 실행하기 전에 master 데이터베이스에 있는 sp_MSdbuseraccess 프로시저를 백업받아놓으면 될까?
굳이 그럴 필요는 없다.
sp_MSdbuseraccess 프로시저는 "Program Files\Microsoft SQL Server\MSSQL\Install" 폴더안의 Sqldmo.sql 파일 안에
존재하고 있다.
Sqldmo.sql파일에서 "create proc sp_MSdbuseraccess" 를 찾아서 실행시켜주기만 하면 원복할 수 있다.
[참조] http://support.microsoft.com/default.aspx/kb/889696
