Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdbuserpriv

  No additional text.


Syntax

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

 
Last revision 2008RTM
See also

  sp_MSdbuseraccess (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash