Explain about Has_perms_by_name function..

 Posted by Bharathi Cherukuri on 7/24/2012 | Category: Sql Server Interview questions | Views: 4323 | Points: 40

This function will let the user know whether he has the effective permission on a securable(Ex:Table).
This function cannot be used to check permissions on linked server.

If the user wants to know whether he has the permission for SELECT on the customer's table, he can use the below query:

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

It will return either 1(true) or 0(false).
To check all the tables in which you have select permission, below query is used:

select Has_perms_by_name
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
'OBJECT', 'SELECT') As have_select, name FROM sys.tables

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response