public void CheckPolicyTest_NoRoutines() { GrantExecutePolicy target = new GrantExecutePolicy(); #region script... string script = @"SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Person].[Address]') AND type in (N'U')) BEGIN CREATE TABLE [Person].[Address]( [AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [AddressLine1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [AddressLine2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [City] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [StateProvinceID] [int] NOT NULL, [PostalCode] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Address_rowguid] DEFAULT (newid()), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Address_ModifiedDate] DEFAULT (getdate()), CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED ( [AddressID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[Person].[Address_AuditTrig_DELETE]')) EXEC dbo.sp_executesql @statement = N' CREATE TRIGGER Address_AuditTrig_DELETE ON [Person].[Address] FOR DELETE AS BEGIN DECLARE @TrxId uniqueidentifier SET @TrxId = newid() DECLARE @count int SELECT @count = count(*) FROM deleted INSERT INTO [dbo].[AuditTransactionMaster] (TransId,TableName,ModifyType,RowsAffected) VALUES (@TrxId,''Person.Address'',''DELETE'',@count) INSERT INTO [Person].[Address_Audit] (TransId,AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,rowguid,ModifiedDate) SELECT @TrxId, AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,rowguid,ModifiedDate FROM deleted END ' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[Person].[Address_AuditTrig_INSERT]')) EXEC dbo.sp_executesql @statement = N' CREATE TRIGGER Address_AuditTrig_INSERT ON [Person].[Address] FOR INSERT AS BEGIN DECLARE @TrxId uniqueidentifier SET @TrxId = newid() DECLARE @count int SELECT @count = count(*) FROM inserted INSERT INTO [dbo].[AuditTransactionMaster] (TransId,TableName,ModifyType,RowsAffected) VALUES (@TrxId,''Person.Address'',''INSERT'',@count) INSERT INTO [Person].[Address_Audit] (TransId,AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,rowguid,ModifiedDate) SELECT @TrxId, AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,rowguid,ModifiedDate FROM inserted END ' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[Person].[Address_AuditTrig_UPDATE]')) EXEC dbo.sp_executesql @statement = N' CREATE TRIGGER Address_AuditTrig_UPDATE ON [Person].[Address] FOR UPDATE AS BEGIN DECLARE @TrxId uniqueidentifier SET @TrxId = newid() DECLARE @count int SELECT @count = count(*) FROM inserted INSERT INTO [dbo].[AuditTransactionMaster] (TransId,TableName,ModifyType,RowsAffected) VALUES (@TrxId,''Person.Address'',''UPDATE'',@count) INSERT INTO [Person].[Address_Audit] (TransId,AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,rowguid,ModifiedDate) SELECT @TrxId, AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,rowguid,ModifiedDate FROM inserted END ' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[Person].[uAddress]')) EXEC dbo.sp_executesql @statement = N' CREATE TRIGGER [Person].[uAddress] ON [Person].[Address] AFTER UPDATE NOT FOR REPLICATION AS BEGIN SET NOCOUNT ON; UPDATE [Person].[Address] SET [Person].[Address].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[AddressID] = [Person].[Address].[AddressID]; END; ' GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Person].[FK_Address_StateProvince_StateProvinceID]') AND parent_object_id = OBJECT_ID(N'[Person].[Address]')) ALTER TABLE [Person].[Address] WITH CHECK ADD CONSTRAINT [FK_Address_StateProvince_StateProvinceID] FOREIGN KEY([StateProvinceID]) REFERENCES [StateProvince] ([StateProvinceID]) GO ALTER TABLE [Person].[Address] CHECK CONSTRAINT [FK_Address_StateProvince_StateProvinceID] GO "; #endregion string message; string messageExpected = "No routines found"; bool expected = true; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }
public void CheckPolicyTest_NoSchemaNoBrackets() { GrantExecutePolicy target = new GrantExecutePolicy(); #region script... string script = @"/* Source Server: (local) Source Db: AdventureWorks Process Date: 12/11/2008 9:56:37 PM Object Scripted:uspUpdateEmployeePersonalInfo Object Type: Stored Procedure Scripted By: mmckechn Include Permissions: True Script as ALTER: True */ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[uspUpdateEmployeePersonalInfo]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [uspUpdateEmployeePersonalInfo] @EmployeeID [int], @NationalIDNumber [nvarchar](15), @BirthDate [datetime], @MaritalStatus [nchar](1), @Gender [nchar](1) WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON; BEGIN TRY UPDATE [Employee] SET [NationalIDNumber] = @NationalIDNumber ,[BirthDate] = @BirthDate ,[MaritalStatus] = @MaritalStatus ,[Gender] = @Gender WHERE [EmployeeID] = @EmployeeID; END TRY BEGIN CATCH EXECUTE [dbo].[uspLogError]; END CATCH; END; ' PRINT 'CREATED Stored Procedure: uspUpdateEmployeePersonalInfo' END ELSE BEGIN IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[uspUpdateEmployeePersonalInfo]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' ALTER PROCEDURE [uspUpdateEmployeePersonalInfo] @EmployeeID [int], @NationalIDNumber [nvarchar](15), @BirthDate [datetime], @MaritalStatus [nchar](1), @Gender [nchar](1) WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON; BEGIN TRY UPDATE [Employee] SET [NationalIDNumber] = @NationalIDNumber ,[BirthDate] = @BirthDate ,[MaritalStatus] = @MaritalStatus ,[Gender] = @Gender WHERE [EmployeeID] = @EmployeeID; END TRY BEGIN CATCH EXECUTE [dbo].[uspLogError]; END CATCH; END; ' PRINT 'ALTERED Stored Procedure: uspUpdateEmployeePersonalInfo' END END GO GRANT EXECUTE ON uspUpdateEmployeePersonalInfo TO [public] GO"; #endregion string message; string messageExpected = string.Empty; bool expected = true; bool actual; System.Collections.Generic.List <System.Text.RegularExpressions.Match> commentCollection = ScriptHandling.ScriptHandlingHelper.GetScriptCommentBlocks(script); actual = target.CheckPolicy(script, commentCollection, out message); Assert.AreEqual(messageExpected, message); Assert.AreEqual(expected, actual); }