SQL 2005: Check permissions on a securable

Three functions to check permissions in SQL 2005: Has_pers_by_name, PERMISSIONS, and fn_my_permissions. PERMISSIONS, which
exists in SQL 2000, is not suggested to use becuase it will be removed in future release, use fn_my_permissions instead.
SQL 2005 has a new function Has_pers_by_name to check whether the current user has permissions on any securables:
Has_perms_by_name (
                                      securable ,
                                      securable_class ,
                                      permission 
                   [ , sub-securable ]
                   [ , sub-securable_class ]
                                      ) return int
fn_my_permissions ( securable , ‘securable_class’) return a record set
PERMISSIONS ( [ objectid [ , ‘column’ ] ] ) return int
 
Arguments
securable
Is the name of the securable. If the securable is the server itself, this value should be set to NULL. securable is a scalar
expression of type sysname. There is no default.
securable_class
Is the name of the class of securable against which the permission is tested. securable_class is a scalar expression of type
nvarchar(60).
permission
A nonnull scalar expression of type sysname thatrepresents the permission name to be checked. There is no default. The
permission name ANY is a wildcard.
sub-securable
An optional scalar expression of type sysname that represents the name of the securable subentity against which the
permission is tested. The default is NULL.
sub-securable_class
An optional scalar expression of type nvarchar(60) that represent the class of securable subentity against which the
permission is tested. The default is NULL.
These functions cannot check permissions on a linked server.
The following query will return a list of built-in securable classes:
   SELECT DISTINCT class_desc FROM sys.fn_builtin_permissions(default)
 
USE AdventureWorks
GO
E.g. 1: Can I create procedures and tables in schema dbo
SELECT has_perms_by_name(db_name(), ‘DATABASE’, ‘CREATE PROCEDURE’)
    & has_perms_by_name(‘dbo’, ‘SCHEMA’, ‘ALTER’) AS _can_create_procs,
    has_perms_by_name(db_name(), ‘DATABASE’, ‘CREATE TABLE’) &
    has_perms_by_name(‘dbo’, ‘SCHEMA’, ‘ALTER’) AS _can_create_tables;
E.g. 2: Do I have any permissions in the current database?
SELECT has_perms_by_name(db_name(), ‘DATABASE’, ‘ANY’)
However sometimes the three function gives different result, e.g.:
SELECT has_perms_by_name(name, ‘OBJECT’, ‘SELECT’) AS have_select,
PERMISSIONS(object_id)&1,
* FROM sys.tables;
 
SELECT * FROM fn_my_permissions(‘AdventureWorks.HumanResources.Employee’, ‘OBJECT’)
    ORDER BY subentity_name, permission_name ; 
Use fn_my_permissions seems is a better choice.
 
Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

2 Responses to SQL 2005: Check permissions on a securable

  1. JIG says:

    SP_HELPROTECT COMES IN HANDY TO CHECK PERMISSIONS ON ANY OBJECT
    http://msdn.microsoft.com/en-us/library/ms190310.aspx

    • phe1129 says:

      JIG,
      sp_helprotect does not return information about securables that were introduced in SQL Server 2005. Use sys.database_permissions and fn_builtin_permissions instead.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s