How do I determine if I have execute permissions on a DB programatically?

Question

I have a Windows Service that requires execute permissions on a DB. On start up I check to see if I can connect to the DB and stop the service if I can't. I also want to do a check to see if I can execute a stored procedure using that connection. Is there a way to do that without actually attempting to execute a sproc and looking at the exception if one occurs?

Answer 1

If this is for SQL Server, see this question.

Answer 2

You would have to have rights to access the DataDictionary for your database and run a query against it to determine which permissions the account you log in with has. This would vary from database to database.

Answer 3

you could run a query like this:

SELECT
    o.NAME,COALESCE(p.state_desc,'?permission_command?')+' '+COALESCE(p.permission_name,'?permission_name?')+' ON ['+SCHEMA_NAME(o.schema_id)+'].['+COALESCE(o.Name,'?object_name?')+'] TO ['+COALESCE(dp.Name,'?principal_name?')+']' COLLATE SQL_Latin1_General_CP1_CI_AS AS GrantCommand
    FROM sys.all_objects                          o
        INNER JOIN sys.database_permissions       p ON o.OBJECT_ID=p.major_id
        LEFT OUTER JOIN sys.database_principals  dp ON p.grantee_principal_id = dp.principal_id
    where p.state_desc='GRANT' AND p.permission_name='EXECUTE'
        AND o.NAME='YourProcedureName'
        AND dp.Name='YourSecurityName'

...and remove the fancy formatting of the grant command, it is there only for reference

these are nice too...

SELECT * FROM fn_my_permissions('YourTable', 'OBJECT') 
SELECT * FROM fn_my_permissions('YourProcedure', 'OBJECT') 
SELECT * FROM fn_my_permissions (NULL, 'DATABASE')
SELECT * FROM fn_my_permissions(NULL, 'SERVER')

To see what permissions someone else has you can do this:

EXECUTE AS user = 'loginToTest'
GO
PRINT 'SELECT permissions on tables:'
SELECT
    HAS_PERMS_BY_NAME(    QUOTENAME(SCHEMA_NAME(schema_id))+'.' + QUOTENAME(name)
                          ,'OBJECT','SELECT'
                     ) AS have_select
        , * 
    FROM sys.tables;

PRINT 'EXECUTE permissions on stored procedures:'
SELECT
    HAS_PERMS_BY_NAME(    QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
        ,'OBJECT', 'EXECUTE') AS have_execute
        , * 
    FROM sys.procedures;
GO
REVERT;
GO

Answer 4

SQL 2005 and on you can check any permission with HAS_PERM_BY_NAME:

SELECT HAS_PERMS_BY_NAME('sp_foo', 'OBJECT', 'EXECUTE');