Dropped and Recreated Table using wrong permissions
tking8924
Posts: 2 Bronze 1
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:
SQL Compare 11 Deployment Script:
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