May 10, 2012

sp_MSdbuseraccess (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdbuseraccess(nvarchar @mode
, nvarchar @qual)

MetaData:

   
create proc sys.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 = (CASE WHEN DATABASEPROPERTYEX(@qual, N'UserAccess') = N'SINGLE_USER' THEN 1 ELSE 0 END)
-- if ((@single <> 0) or ((@stat & SQLDMODBStat_Inaccessible) <> 0)) begin --
if ((@single <> 0) or
(DATABASEPROPERTYEX(@qual, 'Status') = 'SUSPECT') or
(DATABASEPROPERTYEX(@qual, 'Status') = 'OFFLINE') or
(DATABASEPROPERTYEX(@qual, 'Status') = 'RESTORING') or
(DATABASEPROPERTYEX(@qual, 'Status') = 'RECOVERING'))
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 = (CASE WHEN DATABASEPROPERTYEX(@dbname, N'UserAccess') = N'SINGLE_USER' THEN 1 ELSE 0 END)
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
(DATABASEPROPERTYEX(@dbname, 'Status') = 'SUSPECT') or
(DATABASEPROPERTYEX(@dbname, 'Status') = 'OFFLINE') or
(DATABASEPROPERTYEX(@dbname, 'Status') = 'RESTORING') or
(DATABASEPROPERTYEX(@dbname, 'Status') = 'RECOVERING'))
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, CONVERT(INT, DatabasePropertyEx(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, CONVERT(INT, DatabasePropertyEx(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 #TmpDbUserProfile t on t.dbid = o.dbid where o.name = @qual 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 order by o.name

DROP TABLE #TmpDbUserProfile
DROP TABLE #TmpOut
return 0
end

No comments:

Post a Comment

Total Pageviews