What are the challenges you face when working across database platforms? Take the survey
Options

Dropped and Recreated Table using wrong permissions

tking8924tking8924 Posts: 2 Bronze 1
edited July 6, 2015 3:30PM in SQL Compare 11
I am trying to migrate table changes from one database to another. The changes add a column in the middle of the table which causes SQL Compare to generate a drop/create/rename script for the table instead of an alter. Each database has a different user that has select/insert/delete/update permissions on the table. I have selected the ignore permissions option with the expectation that, instead of having the user from the source database granted permissions, the permissions for the user in the destination database will be restored after the table is recreated. This however is not the case. Regardless of whether I select Ignore permissions or not, grants are generated for the user in the source database only. I'm using SQL Compare 11.2.1.82 Pro (same issue in the corresponding SDK as well). Am I doing something wrong? Is this expected behavior or is this a bug?

I have added DDL below to create a sample environment to highlight the issue I'm having trying to migrate the table changes as well as the output I receive from the deployment wizard. In the output I would expect the grants to be for DB1_Login not DB2_Login.

DDL:
/***** Create Databases *****/
CREATE DATABASE [DB1]
GO
CREATE DATABASE [DB2]
GO

/***** Create Test SQL IDs ******/
USE [master]
GO

CREATE LOGIN [DB1_Login] WITH PASSWORD=N'test', DEFAULT_DATABASE=[DB1], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN [DB2_Login] WITH PASSWORD=N'test', DEFAULT_DATABASE=[DB2], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

/***** Create Table_1 and DB1_Login User on DB1 *****/
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
	[int1] [INT] NOT NULL,
	[int2] [INT] NOT NULL
) ON [PRIMARY]
GO

CREATE USER [DB1_Login] FOR LOGIN [DB1_Login] WITH DEFAULT_SCHEMA=[dbo]
GO

GRANT SELECT, INSERT, DELETE, UPDATE ON dbo.Table_1 TO DB1_Login
GO

/***** Create Table_1 and DB2_Login User on DB2 *****/
USE [DB2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
	[int1] [INT] NOT NULL,
	[int3] [INT] NOT NULL,
	[int2] [INT] NOT NULL
) ON [PRIMARY]
GO

CREATE USER [DB2_Login] FOR LOGIN [DB2_Login] WITH DEFAULT_SCHEMA=[dbo]
GO

GRANT SELECT, INSERT, DELETE, UPDATE ON dbo.Table_1 TO DB2_Login
GO


/***** Cleanup Script *****/

/*

USE master
DROP DATABASE DB1
GO
DROP DATABASE DB2
GO

DROP LOGIN DB1_Login
GO
DROP LOGIN DB2_Login
GO

*/


SQL Compare 11 Deployment Script:
/*
Run this script on:

        (local).DB1    -  This database will be modified

to synchronize it with:

        (local).DB2

You are recommended to back up your database before running this script

Script created by SQL Compare version 11.2.1 from Red Gate Software Ltd at 7/6/2015 2:28:31 PM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Rebuilding [dbo].[Table_1]'
GO
CREATE TABLE [dbo].[RG_Recovery_1_Table_1]
(
[int1] [int] NOT NULL,
[int3] [int] NOT NULL,
[int2] [int] NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
INSERT INTO [dbo].[RG_Recovery_1_Table_1]([int1], [int2]) SELECT [int1], [int2] FROM [dbo].[Table_1]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DROP TABLE [dbo].[Table_1]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_rename N'[dbo].[RG_Recovery_1_Table_1]', N'Table_1', N'OBJECT'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering permissions on [dbo].[Table_1]'
GO
GRANT SELECT ON  [dbo].[Table_1] TO [DB2_Login]
GRANT INSERT ON  [dbo].[Table_1] TO [DB2_Login]
GRANT DELETE ON  [dbo].[Table_1] TO [DB2_Login]
GRANT UPDATE ON  [dbo].[Table_1] TO [DB2_Login]
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO
Sign In or Register to comment.