Permissions Lost After Renaming Table

Question

Permissions Lost After Renaming Table

Viewing 11 posts - 16 through 25 (of 25 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Perry Whittle (5/29/2015)
if you drop or rename a table the granted permissions will be lost

I thought I remember reading that sp_rename maintained the permissions. Upon re-reading the BOL page, it doesn't say that. Thanks, Perry. I'll file this one away.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

drop definitely doesn't and I'm 99.9% certain that rename doesn't either

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

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

OK, just tested and it seems it does retain the permission when renaming, sorry for the mistake

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

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Perry Whittle (5/29/2015)
OK, just tested and it seems it does retain the permission when renaming, sorry for the mistake

No problem, but thanks for the confirmation. I thought it did on 2008, but I don't have a 2012 server to test. :unsure:

I guess we're back to the OP's problem. Have you tried executing the sp_rename (as sysadmin or user with alter permissions on the table) without going through your procedure?

If it works and maintains the permissions, can you post the code for the procedure that does the rename?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Letron Brantley Ten Centuries Points: 1202 More actions · Answer 5

Thanks for working with me on this guys. Below is the code for the usp_RenameTable stored proc.

CREATE PROCEDURE [dbo].[usp_RenameTable]

-- Add the parameters for the stored procedure here

@t1 nvarchar(50) = '',

@t2 nvarchar(50) = ''

WITH EXECUTE AS 'proxy_acct'

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

DECLARE @sqlstmt nvarchar(200)

SET @sqlstmt = 'sp_rename ''' + @t1 + ''' , ''' + @t2 + ''''

EXECUTE sp_executesql @sqlstmt

--select @sqlstmt

END

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Letron Brantley (5/29/2015)
Thanks for working with me on this guys. Below is the code for the usp_RenameTable stored proc.
CREATE PROCEDURE [dbo].[usp_RenameTable]
-- Add the parameters for the stored procedure here
@t1 nvarchar(50) = '',
@t2 nvarchar(50) = ''
WITH EXECUTE AS 'proxy_acct'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @sqlstmt nvarchar(200)
SET @sqlstmt = 'sp_rename ''' + @t1 + ''' , ''' + @t2 + ''''
EXECUTE sp_executesql @sqlstmt
--select @sqlstmt
END

Okay, the procedure looks right and works on 2008. It does maintain permissions after the rename.

When you're calling the procedure, are you including schema in @t1 and @t2? I'm asking because the old name should be qualified, but the new name should be unqualified.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Did you try to use sp_rename (without using the procedure) to rename the table as someone with proper privs? If so, did it keep the permissions through the rename?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Letron Brantley Ten Centuries Points: 1202 More actions · Answer 8

Hmmm... No I'm not including the schema when passing the table name. Could that be the issue?

How did you determine whether the permissions persisted after the rename? Was there a dmv you used or did you just create a test account and try to query the table?

Letron

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Letron Brantley (5/29/2015)
Hmmm... No I'm not including the schema when passing the table name. Could that be the issue?
How did you determine whether the permissions persisted after the rename? Was there a dmv you used or did you just create a test account and try to query the table?
Letron

You should qualify the the existing table, but not the destination name. Example:

sp_rename 'dbo.TestTable', 'TestTable2';

As for checking the permissions, you can query sys.database_permissions:

SELECT *

FROM sys.database_permissions

WHERE major_id = OBJECT_ID('dbo.TestTable', 'u');

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Letron Brantley Ten Centuries Points: 1202 More actions · Answer 10

Thanks a bunch Ed. I'll give that a try. That makes sense to me.

Letron

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

Please post back if you figure this out. I'm interested in knowing if it's a problem in 2012 or not.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions