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? Jun 14, 2017 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, 2017 at 13:32
  • 2
    Yes, there's no way around that. Which DBMS are you on? Materialized Views might also be an option. Jun 14, 2017 at 13:50
  • We are using MSSQL so materalized views only exist in oracle afaik
    – Peter
    Jun 14, 2017 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 Jun 14, 2017 at 17:11

1 Answer 1


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 and acknowledge you have read our privacy policy.

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