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