-- HOW-TO MOVE ALL THE PRODUCTION'S DATABASE PERMISSIONS ON A DEV DB
-- and ensure that each time you change the DDL in your db the same
-- permissions will apply
-- The script for getting the permissions is from here
USE [DB_DEV]
GO
/****** Object: Table [dbo].[Permissions] Script Date: 02/22/2010 12:13:30 ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Permissions]') AND type in (N'U'))
DROP TABLE [dbo].[Permissions]
GO
USE [DB_DEV]
GO
/****** Object: Table [dbo].[Permissions] Script Date: 02/22/2010 12:13:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Permissions](
[PermissionsId] [int] IDENTITY(1,1) NOT NULL,
[principle_name] [nvarchar](20) NULL,
[principle_type_desc] [nvarchar](20) NULL,
[object_name] [nvarchar](100) NULL,
[permission_name] [nvarchar](100) NULL,
[permission_state_desc] [nvarchar](100) NULL,
CONSTRAINT [PK_Permissions] PRIMARY KEY CLUSTERED
(
[PermissionsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Now simply copy the permissions from the production's
-- fullback database to the new Permissions table
use DB_FB
GO
INSERT INTO [DB_TEST].[dbo].[Permissions]
([principle_name]
,[principle_type_desc]
,[object_name]
,[permission_name]
,[permission_state_desc])
select dp.NAME AS principal_name,
dp.type_desc AS principal_type_desc,
o.NAME AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class_desc = 'DATABASE' AND name = N'trig_ApplyPermissions')
DISABLE TRIGGER [trig_ApplyPermissions] ON DATABASE
GO
USE [DB_DEV]
GO
/****** Object: DdlTrigger [trig_ApplyPermissions] Script Date: 02/22/2010 11:35:36 ******/
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class_desc = 'DATABASE' AND name = N'trig_ApplyPermissions')DROP TRIGGER [trig_ApplyPermissions] ON DATABASE
GO
USE [DB_DEV]
GO
/****** Object: DdlTrigger [trig_ApplyPermissions] Script Date: 02/22/2010 11:35:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [trig_ApplyPermissions]
on database
for create_procedure, alter_procedure,
create_table, alter_table,
create_function, alter_function
as
set nocount on
declare @data xml
set @data = EVENTDATA()
declare @objName nvarchar(200)
set @objname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
declare @sqlCode nvarchar(max)
set @sqlCode = ''
--Grab all the functions for the current DB
SELECT
IDENTITY(INT,1,1) AS PermissionsId,
[principle_name]
,[principle_type_desc]
,[object_name]
,[permission_name]
,[permission_state_desc]
INTO #ObjList
FROM [Permissions]
where [object_name] = @objName
DECLARE
@Loopid INT,
@MaxId INT,
@principle_name nvarchar(100) ,
@principle_type_desc nvarchar(100) ,
@object_name nvarchar(100) ,
@permission_name nvarchar(100),
@permission_state_desc nvarchar(100)
--Grab start and end values for the loop
SELECT @Loopid = 1,
@MaxId = MAX(PermissionsId)
FROM #ObjList
--This is where the loop starts
WHILE @Loopid <= @MaxId BEGIN
--grab the function name and type
SELECT
@principle_name = [principle_name] ,
@principle_type_desc = [principle_type_desc] ,
@object_name = [object_name] ,
@permission_name = [permission_name] ,
@permission_state_desc = [permission_state_desc]
FROM #ObjList
WHERE PermissionsId = @Loopid
SELECT @sqlCode =
@permission_state_desc + ' ' + @permission_name +
' ON ' + @object_name+
' TO ' + @principle_name + ' ' +
' as DBO ;' FROM [Permissions]
EXEC sp_executesql @sqlCode
--increment counter
SET @Loopid = @Loopid + 1
END --eof while
--clean up
DROP TABLE #ObjList
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [trig_ApplyPermissions] ON DATABASE
GO
ENABLE TRIGGER [trig_ApplyPermissions] ON DATABASE
GO
Posts
No comments:
Post a Comment
- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!