May 10, 2012

sp_MSdbuserpriv (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_MSdbuserpriv(nvarchar @mode)

MetaData:

   
create proc sys.sp_MSdbuserpriv
@mode nvarchar(10) = N'perm'
as

-- Order of privilege evaluation is: user granted/revoked, then group granted/revoked, then public granted/revoked --









set nocount on
declare @bits int, @status int, @prot int, @perms int
declare @dbrole int, @dbrolestr nvarchar(12)

-- If 'srv', we're selecting the server (master db) user profile - currently, just create db priv. --
if (lower(@mode) like N'serv%')
begin
select @bits = 0x0000
if (user_id() = 1 or is_srvrolemember(N'sysadmin') = 1 or is_member(N'db_owner') = 1)
begin
-- sa has everything --
select @bits = 0x0007
end
else begin
if ((PERMISSIONS() & 1) > 0)
SELECT @bits = @bits | 0x0002
if ((PERMISSIONS(OBJECT_ID(N'sp_addextendedproc')) & 32) > 0)
SELECT @bits = @bits | 0x0004
end
select @bits
return 0
end

-- If 'perm', we're selecting the current database priv and role membership for the login user. --
if (lower(@mode) like N'role%' or lower(@mode) like N'ver%' or lower(@mode) like N'perm%')
begin
if (user_id() = 1 or is_srvrolemember(N'sysadmin') = 1 or is_member(N'db_owner') = 1)
begin
-- sa/Dbo has everything. --
select @bits = 0x03ff
end
else begin
-- Not dbo so get individual privileges --
select @bits = 0x0000, @perms = PERMISSIONS(), @status = status from dbo.sysusers where uid = user_id()

if ((@perms & 2) > 0)
SELECT @bits = @bits | 0x0002
if ((@perms & 8) > 0)
SELECT @bits = @bits | 0x0004
if ((@perms & 4) > 0)
SELECT @bits = @bits | 0x0008
if ((@perms & 64) > 0)
SELECT @bits = @bits | 0x0010
if ((@perms & 32) > 0)
SELECT @bits = @bits | 0x0020
if ((@perms & 128) > 0)
SELECT @bits = @bits | 0x0040
if ((@perms & 16) > 0)
SELECT @bits = @bits | 0x0080
if ((@perms & 256) > 0)
SELECT @bits = @bits | 0x0100
if ((@perms & 512) > 0)
SELECT @bits = @bits | 0x0200
end

-- Get both Server and Database Role information --
select @dbrole = 0x0000
-- Server Roles --
select @dbrole = (case when (is_srvrolemember(N'dbcreator') = 1) then @dbrole | 0x0001 else @dbrole end),
@dbrole = (case when (is_srvrolemember(N'diskadmin') = 1) then @dbrole | 0x0002 else @dbrole end),
@dbrole = (case when (is_srvrolemember(N'processadmin') = 1) then @dbrole | 0x0004 else @dbrole end),
@dbrole = (case when (is_srvrolemember(N'securityadmin') = 1) then @dbrole | 0x0008 else @dbrole end),
@dbrole = (case when (is_srvrolemember(N'serveradmin') = 1) then @dbrole | 0x0010 else @dbrole end),
@dbrole = (case when (is_srvrolemember(N'setupadmin') = 1) then @dbrole | 0x0020 else @dbrole end),
@dbrole = (case when (is_srvrolemember(N'sysadmin') = 1) then @dbrole | 0x0040 else @dbrole end),
@dbrole = (case when (is_srvrolemember(N'bulkadmin') = 1) then @dbrole | 0x10000 else @dbrole end),
-- Database Roles --
@dbrole = (case when (is_member(N'db_accessadmin') = 1) then @dbrole | 0x0080 else @dbrole end),
@dbrole = (case when (is_member(N'db_datareader') = 1) then @dbrole | 0x0100 else @dbrole end),
@dbrole = (case when (is_member(N'db_ddladmin') = 1) then @dbrole | 0x0200 else @dbrole end),
@dbrole = (case when (is_member(N'db_denydatareader') = 1) then @dbrole | 0x0400 else @dbrole end),
@dbrole = (case when (is_member(N'db_denydatawriter') = 1) then @dbrole | 0x0800 else @dbrole end),
@dbrole = (case when (is_member(N'db_backupoperator') = 1) then @dbrole | 0x1000 else @dbrole end),
@dbrole = (case when (is_member(N'db_owner') = 1) then @dbrole | 0x2000 else @dbrole end),
@dbrole = (case when (is_member(N'db_securityadmin') = 1) then @dbrole | 0x4000 else @dbrole end),
@dbrole = (case when (is_member(N'db_datawriter') = 1) then @dbrole | 0x8000 else @dbrole end)

if (lower(@mode) like N'ver%')
begin
-- 7.0
select @@version, N'login_id' = convert(int, suser_sid()), N'pagesize' = v.low, N'highbit' = v2.low, N'highbyte' = v3.low,
N'casesens' = (case when (N'A' != N'a') then 1 else 0 end), @@spid, @@servername, is_srvrolemember(N'sysadmin'), @dbrole
from master..spt_values v,master..spt_values v2,master..spt_values v3 where v.number=1 and v.type=N'E' and v2.number=2
and v2.type=N'E' and v3.number=3 and v3.type=N'E'
--
select @@version, N'login_id' = convert(int, suser_sid()), N'pagesize' = v.low, N'highbit' = v2.low, N'highbyte' = v3.low,
N'casesens' = (case when (N'A' != N'a') then 1 else 0 end), @@spid, convert(sysname, serverproperty(N'servername')),
is_srvrolemember(N'sysadmin'), @dbrole,
N'InstanceName' = convert(sysname, serverproperty(N'instancename')),
N'PID' = convert(int, serverproperty(N'processid'))
from master..spt_values v,master..spt_values v2,master..spt_values v3 where v.number=1 and v.type=N'E' and v2.number=2
and v2.type=N'E' and v3.number=3 and v3.type=N'E'
end
else if (lower(@mode) like N'role%')
begin
select @dbrole
end
else if (lower(@mode) like N'perm%')
begin
select @bits
end
return 0
end

No comments:

Post a Comment

Total Pageviews