Retaining Permissions when Altering a Table

Question

Retaining Permissions when Altering a Table

homebrew01

SSC Guru

Points: 55548
More actions
October 24, 2009 at 8:59 am

#135883

Once in a while, we add columns to the middle of a table. When using the GUI it copies to a temp table with the new column, drops the live table, renames the temp table to live table, and recreates the indexes.
However, if there were specific permissions on the table, they do not get recreated. How can I script them out ?
What's the best way to add a column in the middle of a table ?
CREATE TABLE dbo.Tmp_MyTable
(Col1 int NOT NULL,
New_Column nchar(10) NULL,
Col2 varchar(20) NULL,
Col3 varchar(50) NULL) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.MyTable)
EXEC('INSERT INTO dbo.Tmp_MyTable (Col1 , Col2 , Col3)
SELECT Col1 , Col2 , Col3 FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')
DROP TABLE dbo.MyTable
EXECUTE sp_rename N'dbo.Tmp_MyTable', 'MyTable', 'OBJECT'
CREATE NONCLUSTERED INDEX idx_Col1 ON dbo.MyTable
(Col1 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Viewing 3 posts - 1 through 2 (of 2 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

homebrew01 (10/24/2009)
However, if there were specific permissions on the table, they do not get recreated. How can I script them out ?

Not sure if there's a way from the GUI, but query sys.database_principals and sys.database_permissions and you should be able to generate code to recreate them

What's the best way to add a column in the middle of a table ?

Create new table, copy data over, drop old table.

Why do the columns have to be in a particular position? Why can't you add them at the end (which is a ALTER TABLE ADD ... command) and change the select statements to return the columns in the order required?

Like with rows, column 'order' is not supposed to have a meaning in a table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

GilaMonster (10/24/2009)

Why do the columns have to be in a particular position? Why can't you add them at the end (which is a ALTER TABLE ADD ... command) and change the select statements to return the columns in the order required?
Like with rows, column 'order' is not supposed to have a meaning in a table.

I'm lobbying to add columns to the end, but the old guard likes grouping common fields, and when a new column is added, they want it next to their "friends".