So we followed an best practice by Andreas de Ruiter (https://blogs.msdn.microsoft.com/andreasderuiter/2015/04/20/transforming-data-in-a-data-warehouse-through-sql-views)

The Idea behind it is, that you write SQL Views which are easy to maintain and deploy. You use stored procedures to use these views and create tables from it. He suggests dropping or truncating the tables to maintain consistency over the data. I think it's the easiest approach without writing any complex update logic.

On these tables we have the need to set specific user permissions. By dropping the table we would lose them. So for us the question is, how could we drop the table and recreate the existing permissions on the newly created table?

  • 3
    Use truncate instead of drop? – Colin 't Hart Jun 14 '17 at 11:10
  • We thought of that too, unfortunately if we change the View we have to alter the table layout manually – Peter Jun 14 '17 at 13:32
  • 2
    Yes, there's no way around that. Which DBMS are you on? Materialized Views might also be an option. – Colin 't Hart Jun 14 '17 at 13:50
  • We are using MSSQL so materalized views only exist in oracle afaik – Peter Jun 14 '17 at 13:53
  • Another approach -- especially if you're not yet automating the entire process: 1. Truncate 2. Rename to _old 3. Create new 4. Copy permissions from _old 5. Drop _old – Colin 't Hart Jun 14 '17 at 17:11

Export the table permission before dropping:

    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as + 
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
FROM sys.database_permissions AS dp
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('public','guest')
--  AND o.name IN ('My_Procedure')      -- Uncomment to filter to specific object(s)
--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.