How to determine EXEC permission to an extended stored procedure?

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.It doesn't fail (only) for extended procs, it fails for the objects
that begin with 'sp_' or 'xp_', because you are getting the OBJECT_ID
for the object from the master database, but the PERMISSIONS function
accepts only object id-s for objects from the current database. In some
cases, it may look like it's working for some objects from master, but
that's only because the same object id is allocated in the current
database for another object.
If you need to check for objects that may be in master, I would use
another procedure like this:
USE master
GO
CREATE PROCEDURE procHasExecutePermission1
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
USE YourDatabase
GO
CREATE PROCEDURE procHasExecutePermission2
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
CREATE PROCEDURE procHasExecutePermission3
(@.SPNM sysname, @.HAS int OUTPUT) AS
IF LEFT(@.SPNM,3)='sp_'
EXEC master..procHasExecutePermission1 @.SPNM, @.HAS OUTPUT
IF @.HAS IS NOT NULL RETURN
EXEC procHasExecutePermission2 @.SPNM, @.HAS OUTPUT
I have used sysobjects to check the object type, so if you pass a table
name it will return NULL instead of 0.
You may want to improve these procedures using PARSENAME if you need to
allow procedure names prefixed with the database name. If the database
name may also be other than the current database or the master
database, then it gets complicated... there may be a solution by
calling the PERMISSIONS function from Dynamic SQL.
Razvan