0

I have a sproc that scripts out permissions and in the process, I add a check to see if the object exists before dumping the results into a table. Then, I have another re-assign permissions sproc that grabs row by row by using Cursor from the permission table and executes one by one. The performance with using a Cursor or without a cursor (run it directly from a query window in SSMS) are the same. What is the quickest way to run these 42,000+ rows, with each having an IF EXISTS condition check?

Below are some of the permissions that gets executed:

IF EXISTS ( SELECT USER_NAME(grantee_principal_id) 
            FROM sys.database_permissions 
            WHERE class = 1 
                and USER_NAME(grantee_principal_id) = 'TEST' 
                and USER_NAME(grantee_principal_id) not in ('public')) 
AND EXISTS (SELECT 1 
            FROM sys.database_permissions 
            WHERE OBJECT_SCHEMA_NAME(major_id) = 'dbo' 
            AND OBJECT_NAME(major_id) = 'testtable') 

BEGIN 

Print 'Assigning permission GRANT UPDATE on dbo.testtable to [TEST]' GRANT UPDATE on dbo.[testtable] to [TEST] END;


IF EXISTS ( SELECT USER_NAME(grantee_principal_id) 
            FROM sys.database_permissions 
            WHERE class = 1 
                and USER_NAME(grantee_principal_id) = 'ReadOnly'  
                and USER_NAME(grantee_principal_id) not in ('public')) 
AND EXISTS (SELECT 1 
            FROM sys.database_permissions 
            WHERE OBJECT_SCHEMA_NAME(major_id) = 'dbo' 
            AND OBJECT_NAME(major_id) = 'testtable') 

BEGIN Print 'Assigning permission GRANT SELECT on dbo.testtable to [ReadOnly]' GRANT SELECT on dbo.[testtable] to [ReadOnly] END;
  • 1
    Hmmm.. Why don't you just execute the GRANT UPDATE in the first sproc, instead of having to PRINT the statements into a table and then execute it from another sproc? – Danilo Braga Jul 9 '18 at 16:13
  • The first sproc doesn't print, it generates statements that need to be executed to a given database. Part of the statement prints the permission being re-assigned is so that i can see the permissions being assigned in SQL step output, and also dumps it into a text file and then it gets emailed. What i'm doing here is, script out permission from a database, restore this database from a fresh copy, then re-apply permissions. The IF exists condition makes sure that the objects that are missing don't get re-assigned, therefore no failure in sql step. – Laurent Jul 11 '18 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.