May 28, 2015 at 1:10 pm
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
May 28, 2015 at 1:28 pm
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.
May 28, 2015 at 1:31 pm
Thanks Lynn,
"Yes" to all three of your questions.
Letron
May 28, 2015 at 1:34 pm
How are you renaming your tables?
May 28, 2015 at 1:38 pm
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';
May 28, 2015 at 1:41 pm
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
May 28, 2015 at 1:45 pm
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.
May 28, 2015 at 1:46 pm
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.
May 28, 2015 at 1:49 pm
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
May 28, 2015 at 1:51 pm
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.
May 28, 2015 at 1:53 pm
I appreciate anything you can do Lynn. I'll do some testing on my side as well.
Thanks again!
Letron
May 28, 2015 at 1:57 pm
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?
May 28, 2015 at 2:01 pm
Hey Ed,
Yes the procedure is using the sp_rename proc. Also both tables are in the dbo schema.
Letron
May 29, 2015 at 4:50 am
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.
May 29, 2015 at 5:49 am
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