RE: How can I tell if a user has EXEC permission for a stored proc

From: Hal Heinrich (HalHeinrich_at_discussions.microsoft.com)
Date: 05/13/05

  • Next message: Sophie Guo [MSFT]: "Re: SQL2KSP4 sp_addalias and db_accessadmin" 
    Date: Thu, 12 May 2005 16:07:54 -0700

    Hi Mark,

    I tried a few things:
    First off, if I run:
    SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('xp_loginconfig'))
    I get zero back. Also
    SELECT COUNT(*) FROM dbo.sysobjects WHERE (id = OBJECT_ID('xp_loginconfig'))
    returns zero. So this looks like a dead end.

    Now if I run:
    SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('myproc'))
    I get one back. So far so good. Next I run:
    SELECT dbo.sysusers.name FROM dbo.sysprotects INNER JOIN
    dbo.sysusers ON dbo.sysprotects.uid = dbo.sysusers.uid
    WHERE (dbo.sysprotects.id = OBJECT_ID('myproc'))
    which returns a single name, but not 'Jones' - who does have execute
    permission.
    So this also looks like a dead end.

    Any thoughts or suggestions?

    Thanks in advance,
    Hal

    "Mark Allison" wrote:

    > Hal
    >
    > Have a look at the sysprotects table - all the info you need is in there. If
    > you need help with the query, post back.
    >
    > --
    > Mark Allison, SQL Server MVP
    > http://www.markallison.co.uk
    >
    > Looking for a SQL Server replication book?
    > http://www.nwsu.com/0974973602m.html
    >
    >
    >
    >
    > "Hal Heinrich" wrote:
    >
    > > Given a user-name and a stored procedure name, can someone provide me with
    > > the TSQL code to set a boolean indicating whether that user has EXEC
    > > permission to that proc?
    > >
    > > The specific proc I'm interested in is xp_loginconfig.
    > >
    > > Thanks in advance for your help,
    > > Hal Heinrich
    > > VP Technology
    > > Aralan Solutions Inc.

  • Next message: Sophie Guo [MSFT]: "Re: SQL2KSP4 sp_addalias and db_accessadmin"

    Relevant Pages