How can I ask the SQL server if I have permissions on something? (insert, update,etc)

Question

I'm trying to write a unit test for the database layer that validates the connection string's user has update/insert/read permissions. insert/rollback would create extra gaps in the identity column or launch triggers. for read permissions selecting against the table creates load/work on the sql database and updates when the table was last searched in the statistics. How do I programmatically ask sql server the current user's permissions on an object/table/view/stored procedure/etc.

I imagine it's in the system tables somewhere.

Answer 1

Try using the following stored proc

EXEC sp_table_privileges 
   @table_name = ' <table name> '

It should give you want you are looking for

Answer 2

You can ask for each permission individually using HAS_PERMS_BY_NAME:

SELECT HAS_PERMS_BY_NAME('<table>', 'OBJECT', 'SELECT');
SELECT HAS_PERMS_BY_NAME('<table>', 'OBJECT', 'UPDATE');
...

Or you can ask for all your permissions using fn_my_permissions:

SELECT * FROM sys.fn_my_permissions('<table>', 'object');