RSS Feed

SQL Has_perms_by_name

Important points on has_perms_by_name

has_perms_by_name function evaluates the effective permission of the current user on a securable [an example of securable is table].

If a user wants to know whether he has SELECT permission on the Customers table, he can use the following query:

Select Has_perms_by_name ('Customers', 'Objects', 'SELECT')

and

Return value will be 1 (true) or 0 (false).

If you wanted to know whether another user had specific permission, you would have to be a sysadmin or have IMPERSONATE permission for the user in question. Provided one of those conditions are satisfied, you could find out whether "Tom" has SELECT permission by issuing the following:

EXECUTE AS USER 'Tom'
Go
Select Has_perms_by_name ('Customers', 'Objects', 'SELECT')
Go

[Source: Beginning SQL Server 2008 Administration, by Robert Walters, Grant Fritchey, Carmen Taglienti]

To check all the tables in which you have select permission:
    SELECT HAS_PERMS_BY_NAME
    (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
        'OBJECT', 'SELECT') AS have_select, name FROM sys.tables
The Has_perms_by_name function is accessible to the public role.

Has_perms_by_name function cannot be used to check permissions on linked server.

[Source: Microsoft® SQL Server® 2008 Administrator’s Pocket Consultant, by William R. Stanek]