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