Permissions Lost After Renaming Table

  • We have a process that uses the following method to move data quickly:

    TableA = 600million records

    TableB = 0 records

    To "move" data from TableA to TableB

    Rename TableA --> TableA_HOLD

    Rename TableB --> TableA

    Rename TableA_HOLD --> TableB

    The problem with this is that after every rename, permission to the TableB is lost so we have to execute a statement to grant permission back to TableB after the process is complete.

    My question is why is this necessary? Is the permission tied to the table in such a way that it can't use the name only?

    Thanks,

    Letron

  • Letron Brantley (5/28/2015)


    We have a process that uses the following method to move data quickly:

    TableA = 600million records

    TableB = 0 records

    To "move" data from TableA to TableB

    Rename TableA --> TableA_HOLD

    Rename TableB --> TableA

    Rename TableA_HOLD --> TableB

    The problem with this is that after every rename, permission to the TableB is lost so we have to execute a statement to grant permission back to TableB after the process is complete.

    My question is why is this necessary? Is the permission tied to the table in such a way that it can't use the name only?

    Thanks,

    Letron

    I can't directly answer the your question without doing some research but I have a couple of questions, since it appears you are using SQL Server 2012, about the two tables.

    One, do they have identical table structures: indexes, constraints, etc.

    Two, do they reside in the same file qroup.

    Three, do they have the same permissions.

  • Thanks Lynn,

    "Yes" to all three of your questions.

    Letron

  • I'm renaming with a user-defined stored proc that has execute as permission to sp_rename.

    EXEC dbo.usp_RenameTable 'TableA', 'TableA_HOLD';

    EXEC dbo.usp_RenameTable 'TableB', 'TableA';

    EXEC dbo.usp_RenameTable 'TableA_HOLD', 'TableB';

  • Letron Brantley (5/28/2015)


    Thanks Lynn,

    "Yes" to all three of your questions.

    Letron

    Letron Brantley (5/28/2015)


    We have a process that uses the following method to move data quickly:

    TableA = 600million records

    TableB = 0 records

    To "move" data from TableA to TableB

    Rename TableA --> TableA_HOLD

    Rename TableB --> TableA

    Rename TableA_HOLD --> TableB

    The problem with this is that after every rename, permission to the TableB is lost so we have to execute a statement to grant permission back to TableB after the process is complete.

    My question is why is this necessary? Is the permission tied to the table in such a way that it can't use the name only?

    Thanks,

    Letron

    Based on the above it appears that you are moving data from TableA (600 million rows of data) to TableB (0 rows of data). Have you tried the following to move the data?

    A L T E R TABLE dbo.TableA SWITCH TO dbo.TableB; -- Remove the spaces in the word alter

  • Yes I've thought about ALTER SWITCH but if I'm not mistaken, one of the requirements for ALTER SWITCH is that the target table has to be empty.

  • Letron Brantley (5/28/2015)


    Yes I've thought about ALTER SWITCH but if I'm not mistaken, one of the requirements for ALTER SWITCH is that the target table has to be empty.

    Based on what you posted, TableB is empty. Your original post says 0 rows.

  • Yeah I did say that ... :Whistling:

    Actually the real table is not. That's why we are doing the rename. Also both tables are partitioned with the same scheme and function.

    I'm just curious as to why the permissions would get lost when doing a rename. It doesn't make sense to me right now.

    Letron

  • Letron Brantley (5/28/2015)


    Yeah I did say that ... :Whistling:

    Actually the real table is not. That's why we are doing the rename. Also both tables are partitioned with the same scheme and function.

    I'm just curious as to why the permissions would get lost when doing a rename. It doesn't make sense to me right now.

    Letron

    I'd have to do some testing and research to answer your original question. Hopefully someone else may have an answer.

  • I appreciate anything you can do Lynn. I'll do some testing on my side as well.

    Thanks again!

    Letron

  • Since I don't have 2012, I'm going to go back to the rename operation. Is the usp_RenameTable procedure using the sp_rename procedure? The user context under which the procedure is running has ALTER permission on the table, right? You aren't trying to switch schemas, use intermediary tables or anything else, are you?

  • Hey Ed,

    Yes the procedure is using the sp_rename proc. Also both tables are in the dbo schema.

    Letron

  • I'm sorry, but I'm out of ideas. I've even looked through the list of fixes for 2012 SP2 CU 1 through 6 and I'm just not seeing the problem. Without a 2012 environment to play in, I can't reproduce it.

  • if you drop or rename a table the granted permissions will be lost

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply