sarangzigi.com

use master
go
--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