I'm developing an ETL, whose extract step must read data from a table.
There are times that my user's access to that table is revoked. So, to avoid the ETL failing, before reading the table I must verify if I have permission to do it. If not, I'll just jump the extract step and run the rest of the ETL.
What's the proper way of verifying if my user has read permission over a table?
Best Answer
To check for a specific effective permission on a securable, use
HAS_PERMS_BY_NAME
*.For example, to show if the current security context has
SELECT
permission on theProduction.Product
table in the AdventureWorks sample database:Related: To list effective permissions on a securable, use
sys.fn_my_permissions
:* The built-in function takes everything into account. SQL Server has to know how to correctly determine effective permissions. This function exposes that ability.