Post reply

Privileges (stored procs)

  • June 17, 2005 at 9:21 am

    #66620

    I believe from BOL I can generate simple script to see the Table Privileges using Information_Schema.  Does anyone know how to do this with Stored Procedures? 

    Thanks in advance. 

    I wasn't born stupid - I had to study.

  • June 17, 2005 at 9:25 am

    #566469

    Put the simple script in a stored procedure?

  • June 17, 2005 at 9:32 am

    #566477

    Or does he mean with the system tables??

  • June 17, 2005 at 9:32 am

    #566476

     

    I stated that very poorly.  I meant, does anyone know how to find the privileges for Stored Procedures....  sorry. 

    I wasn't born stupid - I had to study.

  • June 17, 2005 at 9:42 am

    #566485

    Here's a little view I put up for an ADP releasing software (releases are made often due to many hotfixes).

    It select the procs and functions that do not have deny or grant permissions and it creates those statements for you . (then I can manually grant permission if needed).

    CREATE VIEW dbo.vwPermissionsMissing

    AS

    SELECT TOP 100 PERCENT USER_NAME(so.uid) AS Owner, so.name AS StoredProcedure, so.XType, so.refdate AS CreationDate, 'GRANT ' + CASE WHEN so.XType in ('IF', 'TF') THEN 'SELECT ON [' ELSE 'EXECUTE ON [' END + USER_NAME(so.uid) + '].[' + so.name + '] TO [Domain Users]' as GrantStatement

    FROM dbo.sysobjects so LEFT OUTER JOIN

    (SELECT id

    FROM dbo.sysprotects

    WHERE uid = USER_ID('Domain Users') AND action in (193,224)) sp ON so.id = sp.id

    WHERE (so.xtype IN ('P', 'FN', 'IF', 'TF')) AND (so.status >= 0) AND (sp.id IS NULL)

    ORDER BY so.name

  • June 17, 2005 at 1:12 pm

    #566544

    Thank you, Remi!  Must admit, it took me a bit of time to recognize how to use with and without 'Domain Users'.  It is very good for specifying my own Roles or Groups as well...

    I wasn't born stupid - I had to study.

  • June 17, 2005 at 5:12 pm

    #566579

    Sorry... I thaught you knew how to grant permissions with tsql .

  • June 20, 2005 at 7:20 am

    #566709

    Here's an alternate way to list the names of procedures that the current user has execute permissions on..

    select o.name

    from   sysobjects o

    where  o.type = 'p'

    and    permissions(o.id) & 32 = 32

    ..see BOL and PERMISSIONS() function for more details

    =;o)

    /Kenneth

  • June 20, 2005 at 8:05 am

    #566744

    If I am reading BOL correctly, then I would want to use the following: 

    SELECT o.name

    FROM sysobjects o

    WHERE o.type = 'P'

      AND permissions(o.id) & 32 <> 32

    to find those which do not have EXECUTE permission.  The examples near the bottom, (A, B, and C) indicate flow control (IF statements) which lead me to this conclusion. 

     

    While playing around with this, I tried

    SELECT o.name, permissions(o.id)

    FROM sysobjects o

    WHERE o.type = 'P'

    What I do not understand is, I get a return for pretty much every stored procedure with a permissions function value of: 2097215.  I do not find this number in BOL, so I am stumped about that return.   

    Thanks for your input, (and thanks Remi - I do know how to GRANT permissions...) 

    I wasn't born stupid - I had to study.

  • June 20, 2005 at 9:13 am

    #566766

    I'm not going to pretend that I know everything there is about bit-magic, but.. 2097215 is what you get when the current user has execute perms (32) AND also can GRANT execute (also 32). It's sort of the 'high bit' 32 added...

    65536 * 32 = 2097215 if that makes anything clearer

    If you read BOL again about PERMISSIONS, you see that they mention both the actual permissions and permission to grant are stored in the same place. 

    /Kenneth

     

  • June 20, 2005 at 9:55 am

    #566778

    Thanks.  That makes sense. 

    I wasn't born stupid - I had to study.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply