Solved

sp_MSdbuserpriv

Posted on 2010-08-20
4
1,193 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:anushahanna
  • 2
4 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 167 total points
ID: 33490259
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 333 total points
ID: 33491852
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 333 total points
ID: 33491870
Oh, and that above link to technet also shows on the left hand side the entry / reference for : is_srvrolemember()

0
 
LVL 6

Author Comment

by:anushahanna
ID: 33588007
Thanks indeed for the helpful clarification.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now