EM접속시 특정 사용자의 DB만 보이게 하는 방법 > db

Enterprise Manager 접속시 특정 사용자의 DB만 보이게 하는 방법이 있습니다.

쿼리분석기에서 sa 사용자로 로그인한 후 아래의 스크립트를 수행합니다.

---------------------------- 아래 ------------------------------------
use master
go

alter 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 db? */
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

/** OK, we can access this db, need to go to the specified database to get 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 if what kind of access we have to the specified databases */
/* If we are not in master, then we are selecting single database, we want to correct role bit to save round trip
*/
if (lower(@mode) like N'db%') begin
/* Make sure we're either in master or only doing it to current db. */
declare @dbrole int
select @dbrole = 0x0000

if (db_id() <> 1)
select @qual = db_name()

/* If dbname contains ', double it for the cursor, since cursor statement is inside of '' */
declare @qual2 nvarchar(517)
SELECT @qual2 = REPLACE(@qual, N'''', N'''''')

/* Preprocessor won't replace within quotes so have to use str(). */
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 can't 'use' a database with a version below the minimum. */
/* SQL6.0 minimum is 406; SQL65 requires 408. SQL70 database version is 408 now, 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 inaccessible ones. */
declare @dbid smallint, @dbidstr nvarchar(12), @dbstat int, @dbname nvarchar(258), @dbver smallint
declare @dbbits int, @dbbitstr nvarchar(12)

/* !!! work around here, if name contains '[', LIKE operator can't find it, since LIKE operator it treating '[' as a
wild char */
/* !!! but @qual2 might be '%', then = 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, and if it's accessible, recursively call ourselves to add it. */
fetch hCdbs into @dbname, @dbid, @dbstat, @dbver
while (@@fetch_status >= 0) begin
/* Preprocessor won't replace within quotes so have to use str(). */
select @dbidstr = ltrim(str(convert(int, @dbid)))

/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't 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 db is accessible (not in load, recovery, offline, single-use with another user besides us,
etc.) */
/* 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're sa or suser_id() is db owner sid. */
exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @inaccessible + N')')
end
else begin
/* Find out 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
/* Yes, current user does have access to this database, we are not trying to get 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 info into temp table first to avoid deadlock in 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, then dbrole is dummy, need to get it later */
/* 2. Do not double the ' character(s) in database name */
/* 3. To speed up connection, accessperms column only indicate whether the login user can access the db, it
does not contain */
/* permission info, we will retrieve the permission info through sp_MSdbuserpriv when necessary */
/* !!! work around here, if name contains '[', LIKE operator can't find it, since LIKE operator it treating '[' as a
wild char */
/* !!! but @qual2 might be '%', then = 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 (select * from #TmpDbUserProfile where db_name(dbid) not in
('master', 'msdb', 'model')) t on t.dbid = o.dbid where o.name = @qual and accessperms = 1023 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 (select * from #TmpDbUserProfile where db_name(dbid) not in
('master', 'msdb', 'model')) t on t.dbid = o.dbid where o.name like @qual and accessperms = 1023 order by
o.name

DROP TABLE #TmpDbUserProfile
DROP TABLE #TmpOut
return 0
end


출처 : http://sqler.pe.kr/web_board/view_list.asp?id=675&read=580&pagec=1&gotopage=1&block=0&part=myboard7&tip=ok