HAS_PERMS_BY_NAME

All users belong to SQLusers group. A login is created for the [Domain\SQLusers]. Suppose all the users have the same permissions on database objects. However, you find the user VANSTUDENTS\Vancouver can View more tables and can delete records on tables which he should not. How can you find out?

• Impersonate VANSTUDENTS\vancouver login:

EXECUTE AS LOGIN ='vanstudents\vancouver'

• SELECT Name FROM sys.tables WHERE HAS_PERMS_BY_NAME(name,'object','DELETE')=1;
  

• SELECT Name FROM sys.tables WHERE HAS_PERMS_BY_NAME(name,'object','SELECT')=1;