How to determine EXEC permission to an extended stored procedure?

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

  • Next message: Leo Quezada: "Re: INSERT an UPDATE permission denied on object" 
    Date: Mon, 16 May 2005 10:13:07 -0700

    The following proc indicates whether you have EXEC permission to a proc -
    however it fails for extended procs. I'd be grateful for a fix!
    A good test is @SPNM = 'xp_sprintf'. Note that the proc is getting a valid
    object id for the extended procs.

    PROCEDURE procHasExecutePermission
    ( @SPNM sysname,
       @HAS bit OUTPUT
    ) AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @OID int
    SET @OID = OBJECT_ID(@SPNM)
    IF @OID IS NULL
       IF SUBSTRING(@SPNM, 1, 3) = 'sp_' OR SUBSTRING(@SPNM, 1, 3) = 'xp_'
          SET @OID = OBJECT_ID('master..' + @SPNM)
    IF @OID IS NULL
       SET @HAS = 0
    ELSE
       IF PERMISSIONS(@OID) & 0x20 = 0x20
          SET @HAS = 1
       ELSE
          SET @HAS = 0
    END

    Thanks in advance for your help,
    Hal Heinrich VP Technology
    Aralan Solutions Inc.

  • Next message: Leo Quezada: "Re: INSERT an UPDATE permission denied on object"

    Relevant Pages

    • Re: Bcp and temp tables
      ... Here is the proc: ... bcp "SET FMTONLY OFF EXEC pubs.dbo.Kristoffer" queryout ... without SET NOCOUNT ON with the same results. ... > Tibor Karaszi, SQL Server MVP ...
      (microsoft.public.sqlserver.programming)
    • Re: Simple Stored Procedure funktioniert mal und funktioniert mal nich
      ... Deine PROC gibt keinen RETURN-Value zurück sondern ... > Ein Return-Value in einer SP ist immer vom Datentyp int ... > Eine Return-Variable sollte in einer SP immer deklariert sein. ... > SET NOCOUNT OFF ...
      (microsoft.public.de.sqlserver)
    • Re: Simple Stored Procedure funktioniert mal und funktioniert mal nich
      ... Ein Return-Value in einer SP ist immer vom Datentyp int ... Eine Return-Variable sollte in einer SP immer deklariert sein. ... CREATE PROC dbo.myProc ... SET NOCOUNT OFF ...
      (microsoft.public.de.sqlserver)
    • Re: Stop DTS execution
      ... Include SET NOCOUNT ON as the first statement in your proc: ... CREATE PROC StorProc ... >>SQL Server MVP ...
      (microsoft.public.sqlserver.programming)
    • Re: Help - stored procedure not returning a recordset
      ... > Remember to SET NOCOUNT OFF at the end of the proc too ... The setting only lasts for the scope of the proc (or the proc that ... I always SET NOCOUNT ON and out of n stored procedures I have, ...
      (microsoft.public.inetserver.asp.db)