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

I have a Windows Service that requires execute permissions on a SQL Server 2005 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 开发者_JAVA技巧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?

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

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

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

The first part of this answer shows how you can check rights in T-SQL, the second part gives an example how to use this in the Entity Framework (note that there are differences between the EF versions - the example given is EF 4, but can be changed easily to a newer version):

First part (SQL):

I am using the following T-SQL script to check permissions. It first checks if you have any rights, then it checks execute permissions for the SPs, finally select permissions for the tables. See this link to get more information.

-- 1. Do I have any permissions?
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY') DoIHaveAnyRights;

-- 2. create list of schemas
declare @SchemaList table (schema_id int, name nvarchar(max));

PRINT 'Schemas regarded:'

insert into @SchemaList
select distinct schema_id, name FROM sys.schemas
where name in ('dbo') -- enter the schemas you like to check comma-separated

SELECT s.name, s.schema_id FROM sys.schemas s
join @SchemaList sl on s.schema_id=sl.schema_id

-- 3. check execute permissions
PRINT 'EXECUTE permissions on stored procedures:'
SELECT 
    HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
        ,'OBJECT', 'EXECUTE') AS [have execute]
        ,'['+s.name +'].['+ t.name+']' as [object]
        --, * 
    FROM sys.procedures t
    join @SchemaList s on t.schema_id=s.schema_id
order by s.name, t.name;

-- 4. check select permissions
PRINT 'SELECT permissions on tables:'
SELECT 
    HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(t.schema_id))+'.' + QUOTENAME(t.name)
        ,'OBJECT','SELECT') AS [have select]
        ,'['+s.name +'].['+ t.name+']' as [object]
        --, * 
    FROM sys.tables t
    join @SchemaList s on t.schema_id=s.schema_id
order by s.name, t.name;

With the Northwind database for example, this gives you the following result:

Note that you can configure the schemas regarded in step 2. If you don't need a limited set of schemas checked, you can just comment out the where clause in the insert into @SchemaList statement to get all schemas.

Second part (Entity Framework):

In this section, I'd like to show you, how you can get the results into Entity Framework. Let's suppose you want to check if you have any rights before using any of the tables in your LINQ queries. Take a look at this example (for simplicity, I've done it in LinqPad, please add System.Data.Entity.dll and its namespaces via F4 before you run it):

void Main()
{
    var dc=this;
    var sql="SELECT TOP 1 "
              + "HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY') DoIHaveAnyRights;";
    var result=dc.ExecuteStoreQuery<Rights>(sql);
    if (result1.DoIHaveAnyRights==1)
    {
        Console.WriteLine("OK"); // ok
    }
    else
    {
        // no rights: Show error etc.
        Console.WriteLine("No rights"); // ok
    }
}

public class Rights
{
    public Int32 DoIHaveAnyRights { get; set; }
}

Likewise you can use the query from the first part of my answer, e.g.:

var sql="select top 1 case when cnt.NoRightsCount=0 then 1 else 0 end "
+"as DoIHaveAnyRights "
+"from (SELECT count(1) NoRightsCount FROM sys.procedures t "
+"where HAS_PERMS_BY_NAME("
+"QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)"
+",'OBJECT', 'EXECUTE')<>1) cnt";

This query will check if there are any stored procedures in your database for which you have no rights to execute - in this case the return value would be result1.DoIHaveAnyRights!=1.

I think you get the idea, play around with the possibilities: Keep in mind that EF requires access to all the database tables, stored procedures etc. you're mapping to - you could use the code above to check before you access them. Unfortunately, there is currently no easier way to do this.

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.