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.
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:
- Creates a table called PermissionMigration
- Reads all databases from the sys.databases – it excludes all system databases.
- Calls the GetDatabaseRequest
- Parses the result from the procedure and inserts the permissions for the database to the PermissionMigration table
- 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
Open the file on the migrated server and run it.
All your database permissions should be there.
Serendipituos Code 