sp_MSdbuserpriv

what is meaning of the following SP:
exec master.dbo.sp_MSdbuserpriv 'serv123456789012345678901234567890'

as long as the paramter starts with 'serv' and it does not care about the rest of the details in the string. what value does the above return for you and how do you interpret it?

thanks
LVL 6
anushahannaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cyberkiwiConnect With a Mentor Commented:
use master
exec sp_Helptext sp_MSdbuserpriv

defaults to 'perm' if @mode not supplied
checks for 'serv','role','ver'

Everything else after these text are ignored.

It returns 7 to me, because I am a sysadmin.
The possible returns for 'serv' are

7 - sysadmin or user in sysadmin role
2 - has create database permission
4 - has permission to sp_addextendedproc
6 - both 2 & 4
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well, it has been around for quite some time. One of those cute undocumented stored procedures. This one is for privileges - ie db permissions and roles basically. The actual code is below as extracted from SQL server... Not unsurprising is a newer function : fn_my_permissions()

As mentioned above, it has one parameter @mode and returns a status based on permissions() and is_srvrolemember()  (and a couple of combinations or server properties etc).

If you look for permissions function in Books Online you will see what the bitmaps represent : http://technet.microsoft.com/en-us/library/ms186915.aspx

The mode is one of 'perm%', 'role%', 'ver%', or 'serv%'. And call it like :  EXEC sp_MSdbuserpriv @mode = 'ver'



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

Open in new window

0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Oh, and that above link to technet also shows on the left hand side the entry / reference for : is_srvrolemember()

0
 
anushahannaAuthor Commented:
Thanks indeed for the helpful clarification.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.