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:
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]
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]