Transfer database permissions on all objects from one SQL server to another – MS SQL Server 2008 – 2014

The Problem

I needed to migrate all databases from a MS SQL server 2008 R2 to MS SQL Server 2014; while I can create and restore my databases, permission in objects live on the system databases, therefore I needed to transfer all my login and data objects from one server to another.

This cannot be done using MS SQL Server tools, you need to write a script to move all the permissions. But permissions and roles are stored individually on each database, if you have hundreds of databases, you would like to poke your eyes out.

So, reading all the permissions on all databases requires some SQL Injection.

The Solution

I searched the webs and found me a really nice post on the that explains why you can’t just migrate and gives you the solution to for one database, in this article I was able to grab the script that generates the list of permissions that need to be migrated for one database http://sqlity.net/en/2584/script-database-permissions/  scroll down to the “The Database Permissions Query” section

I took the script – Thank you sqlity.Net for the posting the valuable information that saved me hours. I adapted the script to run on for every database on my server.

Using MS SQL Management Studio, open the original server, and make sure you are running on the master database

Step 1 – Create the SQL Injection Stored Procedure

Create PROCEDURE [dbo].[GetDatabaseRequest](@dbName varchar(50), @ScriptToRun varchar(max) out) as

BEGIN 

SET NOCOUNT ON;

declare @sql as varchar(max);

set @sql = ‘SELECT ‘ + ”” + ‘Use [‘ + @dbName + ‘];”’ + ‘ + CASE WHEN P.state_desc = ‘ + ”’GRANT_WITH_GRANT_OPTION”’ + ‘ THEN ‘ + ”’GRANT”’ + ‘ ELSE P.state_desc END + ” ”’ +

‘ + P.permission_name + ‘ +

”’ ON ”’ + ‘ + CASE P.class_desc ‘ +

‘WHEN ‘ + ”’DATABASE”’ + ‘ THEN ‘ + ”’DATABASE::”’ + ‘+QUOTENAME(DB_NAME())’ +

‘WHEN ‘ + ”’SCHEMA”’ + ‘ THEN ‘ + ”’SCHEMA::”’ + ‘+QUOTENAME(S.name) ‘ +

‘WHEN ‘ + ”’OBJECT_OR_COLUMN”’ + ‘ THEN ‘ + ”’OBJECT::”’ + ‘+QUOTENAME(OS.name)+’ + ”’.”’ + ‘+QUOTENAME(O.name) + ‘ +

‘ CASE WHEN P.minor_id <> 0 THEN ‘ + ”'(”’ + ‘+QUOTENAME(C.name)+’ + ”’)”’ + ‘ELSE ” ” END ‘ +

‘WHEN ‘ + ”’DATABASE_PRINCIPAL”’ + ‘ THEN ‘ +

‘ CASE PR.type_desc

WHEN ‘ + ”’SQL_USER”’ + ‘ THEN ‘ + ”’USER”’ +

‘ WHEN ‘ + ”’DATABASE_ROLE”’ + ‘ THEN ‘ + ”’ROLE”’ +

‘ WHEN ‘ + ”’APPLICATION_ROLE”’ + ‘ THEN ‘ + ”’APPLICATION ROLE”’ +

‘ END +’ + ”’::”’ + ‘+QUOTENAME(PR.name)’ +

‘ WHEN ‘ + ”’ASSEMBLY”’ + ‘ THEN ‘ + ”’ASSEMBLY::”’ + ‘+QUOTENAME(A.name)’ +

‘ WHEN ‘ + ”’TYPE”’ + ‘ THEN ‘ + ”’TYPE::”’ + ‘+QUOTENAME(TS.name)+’ + ”’.”’ + ‘+QUOTENAME(T.name)

END COLLATE Latin1_General_100_BIN + ‘ +

”’ TO ”’ + ‘+QUOTENAME(DP.name) + ‘ +

‘CASE WHEN P.state_desc = ‘ + ”’GRANT_WITH_GRANT_OPTION”’ + ‘ THEN ‘ + ”’WITH GRANT OPTION”’ + ‘ ELSE ”” END ‘ +

‘ as PermissionScript

FROM ‘ + @dbName + ‘.sys.database_permissions AS P

LEFT JOIN ‘ + @dbName + ‘.sys.schemas AS S

ON P.major_id = S.schema_id

LEFT JOIN ‘ + @dbName + ‘.sys.all_objects AS O

JOIN ‘ + @dbName + ‘.sys.schemas AS OS

ON O.schema_id = OS.schema_id

ON P.major_id = O.object_id

LEFT JOIN ‘ + @dbName + ‘.sys.types AS T

JOIN ‘ + @dbName + ‘.sys.schemas AS TS

ON T.schema_id = TS.schema_id

ON P.major_id = T.user_type_id

LEFT JOIN ‘ + @dbName + ‘.sys.xml_schema_collections AS XSC

JOIN ‘ + @dbName + ‘.sys.schemas AS XSS

ON XSC.schema_id = XSS.schema_id

ON P.major_id = XSC.xml_collection_id

LEFT JOIN ‘ + @dbName + ‘.sys.columns AS C

ON O.object_id = C.object_id

AND P.minor_id = C.column_id

LEFT JOIN ‘ + @dbName + ‘.sys.database_principals AS PR

ON P.major_id = PR.principal_id

LEFT JOIN ‘ + @dbName + ‘.sys.assemblies AS A

ON P.major_id = A.assembly_id

LEFT JOIN ‘ + @dbName + ‘.sys.service_contracts AS SC

ON P.major_id = SC.service_contract_id

LEFT JOIN ‘ + @dbName + ‘.sys.service_message_types AS SMT

ON P.major_id = SMT.message_type_id

LEFT JOIN ‘ + @dbName + ‘.sys.remote_service_bindings AS RSB

ON P.major_id = RSB.remote_service_binding_id

LEFT JOIN ‘ + @dbName + ‘.sys.services AS SBS

ON P.major_id = SBS.service_id

LEFT JOIN ‘ + @dbName + ‘.sys.routes AS R

ON P.major_id = R.route_id

LEFT JOIN ‘ + @dbName + ‘.sys.fulltext_catalogs AS FC

ON P.major_id = FC.fulltext_catalog_id

LEFT JOIN ‘ + @dbName + ‘.sys.fulltext_stoplists AS FS

ON P.major_id = FS.stoplist_id

LEFT JOIN ‘ + @dbName + ‘.sys.asymmetric_keys AS AK

ON P.major_id = AK.asymmetric_key_id

LEFT JOIN ‘ + @dbName + ‘.sys.certificates AS CER

ON P.major_id = CER.certificate_id

LEFT JOIN ‘ + @dbName + ‘.sys.symmetric_keys AS SK

ON P.major_id = SK.symmetric_key_id

JOIN ‘ + @dbName + ‘.sys.database_principals AS DP

ON P.grantee_principal_id = DP.principal_id

JOIN ‘ + @dbName + ‘.sys.database_principals AS G

ON P.grantor_principal_id = G.principal_id

Where p.permission_name <> ‘ + ”’Connect”’

set @ScriptToRun = @sql;

END

This stored procedure will return the parsed SQL Statement that will list the permissions for every object on your database. If you print the result and run it, you will see all the permissions for that database.

PermissionResults

Step 2 – Read all the databases in SQL 2008 server and return all found permissions.

Make sure you are running on the master database

This script does the following:

  1.  Creates a table called PermissionMigration
  2. Reads all databases from the sys.databases – it excludes all system databases.
  3. Calls the GetDatabaseRequest
  4. Parses the result from the procedure and inserts the permissions for the database to the PermissionMigration table
  5. Returns all the records from the PermissionMigration table

You may be thinking why didn’t you create a temp table?  I needed the permissions to be readily accessible, in case I need to run them again, then I just run a query on the table.

IF OBJECT_ID(‘dbo.PermissionMigration’, ‘U’) IS NOT NULL

DROP TABLE dbo.PermissionMigration;

cREATE TABLE [dbo].[PermissionMigration](

[Id] [int] IDENTITY(1,1) NOT NULL,

[ScriptToRun] [varchar](max) NOT NULL,

CONSTRAINT [PK_PermissionMigration] PRIMARY KEY CLUSTERED

(

[Id] 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

declare @dbName varchar(50);

declare @result varchar(max);

DECLARE db_cursor CURSOR for

SELECT name

FROM sys.databases

Where not name in (‘master’, ‘model’,‘msdb’,‘tempdb’)

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

execute GetDatabaseRequest @dbname , @result out

set @result = ‘Insert into PermissionMigration ([ScriptToRun]) Select * from (‘ + @result + ‘) as x where not PermissionScript is null’

execute (@result);

fetch next from db_cursor into @dbname

end

close db_cursor

deallocate db_cursor

Select ScriptToRun from PermissionMigration

Step 3 – Save the results to a file>/h4>

The script will return the Transact-SQL statement to add permissions for all objects on all databases.

Save the results to a text file – change the file extension to .sql

list

Open the file on the migrated server and run it.

All your database permissions should be there.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s