Clone permissions between SQL instances with Minion Enterprise

Articles, Minion Enterprise, Tech / By

Right now, I’m restoring a production database – DB1 – to a development instance for a client. The client has kept a version of DB1 in dev for quite some time, with its own ever-evolving set of user permissions.

The client would like to retain those permissions, but of course, when I restore DB1 from production, that all gets wiped out! With Minion Enterprise, though, I can simply script out the permissions from the DB1 database on dev, and apply those permissions after the restore!

Clone Permissions in SQL Server

Minion Enterprise provides you the ability to script and clone permissions in a variety of ways. Today we want to script out all permission for DB1 on the dev server, “Dev1”.

To do this, we’ll first run the Clone.LoginAllDBsAll  procedure, collecting the results in a temporary table.

	DECLARE @InstanceID INT;
	SELECT @InstanceID = dbo.InstanceID('Dev1');
	
	
	CREATE TABLE #LoginScripts (
		PermissionCollectionTime [datetime] NULL,
		[InstanceID] [bigint] NULL,
		[StmtOrder] [int] NULL,
		[StmtName] [varchar](200) NULL,
		[StmtLevel] [varchar](200) NULL,
		[StateDesc] [varchar](50) NULL,
		[ObjectName] [varchar](500) NULL,
		[Stmt] [nvarchar](max) NULL,
		[NegativeStmt] [nvarchar](max) NULL,
		[LoginToClone] [varchar](256) NULL,
		[NewLogin] [varchar](256) NULL,
		[CopyPword] [bit] NULL,
		[LoginType] [varchar](50) NULL,
		[Pword] [nvarchar](2000) NULL,
		[UserName] [varchar](500) NULL
	);

	INSERT INTO #LoginScripts
	EXEC Clone.LoginAllDBsAll
	   @InstanceID = @InstanceID,
	   @LoginToClone = 'All',
	   @NewLogin = NULL,
	   @CopyPword = 1,
	   @Push = 0;

 

Now, we just filter for the database we want:

SELECT StateDesc, ObjectName, Stmt
	FROM #LoginScripts
	WHERE 1=1
		AND StmtLevel = 'DB1'
	ORDER BY stmtOrder;

This query pulls the following information:

  • Statement description – whether the statement is a CREATE, GRANT, Add (role member), etc.
  • Object name – The target of the statement: the user being created, or the role being added, or the object to which we’re granting permissions.
  • Statement – The actual T-SQL code to perform the action.

From here, we can grab the entire Stmt column and save it off for use after the restore.

That is, quite literally, all there is to it.

Further cloning considerations

If there are differences in the logins between the two servers, we can pull those scripts as well, using StmtLevel = ‘Server’.

And, if you are using SQL logins in your environment, it’s likely that a restore like this will cause some orphaned users on the target server. Here’s a primer on orphaned users, and how to find and fix them.

Finally, you don’t have to clone ALL permissions for a server. You can clone one login, a list of logins, just the Windows logins, and so on. See “Use Cases” in the Clone Users Module article for more.

Get cloning in your environment

One of Minion Enterprise’s primary goals is to make SQL Security both transparent, and effortless.  Get your trial today, and check out the video “Minion Enterprise: User Scripting and Cloning” for more information!