제목처럼요.
ms-sql EM관리자에서 로그인하면 사용권한 없는 디비도 보이는데요.....
안보이게 할수 있는 방법이 없나요^^
EM(Enterprise Manager)잖아여~~~
관리자를 위한 투~울인데...
관리자 아닌 사람한테 EM 주지 마세여~~~
^^;;
login & user 관리로 db 접근 못하게 하면 되지 않나여~
그럼..
물론 안보이게도 할 수 있습니다.
긴 쿼리문으로 조정을 하시면 가능한데요, 지금의 단계는 아닌듯하여
쿨럭~
보이긴 한다고 해도 조정할 수 없게 되는게 접근데이터베이스에 제한을 두면
다른 건 건딜수 없습니다. ^^
여리님
그 쿼리좀 저좀 알려 주실수 있습니까?
그부분을 알아야 하는 이유가 좀 있어서요 ^^*
혹시 시간되시면 이곳에 써 주셔두 되고 메일로 보내 주셔도 됩니다.
ds0124@dream-stone.net 입니다.
그럼 좋은 하루 되십시오
^^ 그 쿼리문은 아래와 같습니다.
use mastergo--sa 사용자로 로그인 후 스크립트 수행 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 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
declare @single int select @single = DATABASEPROPERTY( @qual, N'issingleuser' ) 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
declare @dbTempname nvarchar(258) declare @tempindex int SELECT @dbTempname = REPLACE(@qual, N']', N']]') exec (N'[' + @dbTempname + N']' + N'..sp_MSdbuserpriv ') return 0 end
if (lower(@mode) like N'db%') begin declare @dbrole int select @dbrole = 0x0000
if (db_id() <> 1) select @qual = db_name()
declare @qual2 nvarchar(517) SELECT @qual2 = REPLACE(@qual, N'''', N'''''')
declare @invalidlogin nvarchar(12) select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11)) declare @inaccessible nvarchar(12) select @inaccessible = ltrim(str(convert(int, 0x80000000), 11))
declare @mindbver smallint if (@@microsoftversion >= 0x07000000) select @mindbver = 408 else select @mindbver = 406
declare @dbid smallint, @dbidstr nvarchar(12), @dbstat int, @dbname nvarchar(258), @dbver smallint declare @dbbits int, @dbbitstr nvarchar(12)
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
fetch hCdbs into @dbname, @dbid, @dbstat, @dbver while (@@fetch_status >= 0) begin
select @dbidstr = ltrim(str(convert(int, @dbid)))
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)
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
exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @inaccessible + N')') end else begin
select @accessbit = has_dbaccess(@dbname) if ( @accessbit <> 1) begin exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @invalidlogin + N')') end else begin
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
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
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