SQL Server database migration

1. index migration: -- export all database Index scripts ---- only generate Index scripts without statistical information ---------------- declare @ tbnamesysname, @ typechar (1) set @ tbname; -- table name, empty return all table indexes set @ type1; -- whether to display clustered index, 1 show clustered index, 2 not show clustered Index

1. migrate Index: -- export all Index scripts of the database ---- only generate Index scripts without statistical information ---------------- declare @ tbname sysname, @ type char (1) set @ tbname = ''; -- table name. If it is null, all table indexes are returned. set @ type = '1'; -- whether or not clustered indexes are displayed. 1 indicates clustered indexes, and 2 indicates no clustered indexes.

1.MigrationIndex:

-- ExportDatabaseAll Index scripts
---- Generate only the index script without statistical information ------------------
Declare @ tbname sysname, @ type char (1)
Set @ tbname = ''; -- table name. If it is null, all table indexes are returned.
Set @ type = '1'; -- whether to display clustered indexes; 1: clustered indexes; 2: clustered indexes.
With t (
Select rank () over (order by B. name, a. name, c. name) as id, c. index_id,
B. name as schema_name, a. name as table_name, c. fill_factor, c. is_padded,
C. name as ix_name, c. type, e. name as column_name, d. index_column_id, c. is_primary_key,
D. is_included_column, f. name as filegroup_name, c. is_unique, c. ignore_dup_key,
D. is_descending_key as is_descending_key, c. allow_row_locks, c. allow_page_locks
From sys. tables as
Inner join sys. schemas as B on a. schema_id = B. schema_id and a. is_ms_shipped = 0
Inner join sys. indexes as c on a. object_id = c. object_id
Inner join sys. index_columns as d on d. object_id = c. object_id and d. index_id = c. index_id
Inner join sys. columns as e on e. object_id = d. object_id and e. column_id = d. column_id
Inner join sys. data_spaces as f on f. data_space_id = c. data_space_id
Where a. object_id like '%' + isnull (ltrim (object_id (@ tbname), '') + '%'
And c. is_hypothetical = 0 and is_disabled = 0 and c. type >=@ type
)

Select k1.table _ name, k1.ix _ name, k1.sqlscript from
(Select distinct a. schema_name, a. table_name, a. ix_name,
Case a. type when 1 then 'clustered' when 2 then 'nonclustered' else' end as index_type,
Case a. is_primary_key when 0 then 'no' else 'yes' end as is_primary_key,
M. ix_index_column_name, isnull (m. ix_index_include_column_name, '') as ix_index_include_column_name,
A. filegroup_name, replace ('create' + case when is_unique = 1 then 'unique 'else' end
+ Case when a. type = 1 then 'clustered' else 'nonclustered' end + 'index'
+ A. ix_name + 'on' + a. schema_name + '.' + a. table_name + '(' + m. ix_index_column_name + ')' +
Case when m. ix_index_include_column_name is null then ''else' include ('+ m. ix_index_include_column_name +') 'end
+ Case when fill_factor> 0 or ignore_dup_key = 1 or is_padded = 1 or allow_row_locks = 0 or allow_page_locks = 0 then' with ('else' end
+ Case when fill_factor> 0 then', fillfactor = '+ rtrim (fill_factor) else ''end
+ Case when is_padded = 1 then ', pad_index = on 'else' 'end
+ Case when ignore_dup_key = 1 then ', ignore_dup_key = on 'else' end
+ Case when allow_row_locks = 0 then ', allow_row_locks = off 'else' end
+ Case when allow_page_locks = 0 then ', allow_page_locks = off 'else' end
+ Case when fill_factor> 0 or ignore_dup_key = 1 or is_padded = 1 or allow_row_locks = 0 or allow_page_locks = 0 then ') 'else' 'end,' (,', '(')
+ 'With (online = on)' as sqlscript
From t as
Outer apply
(
Select ix_index_column_name = stuff (replace (
(
Select case when B. is_descending_key = 1 then column_name + 'desc' else column_name end as column_name
From t as B where a. id = B. id and is_included_column = 0 order by index_column_id for xml auto
),'', ''), 1, 1 ,'')
, Ix_index_include_column_name = stuff (replace (
(
Select column_name from t as B where a. id = B. id and is_included_column = 1
Order by index_column_id for xml auto
),' ', ''), 1, 1 ,'')
) M
) K1
Order by k1.table _ name, k1.ix _ name

2. Export users:

USE master
GO
IF OBJECT_ID ('SP _ hexadecimal ') IS NOT NULL
Drop procedure sp_hexadecimal
GO
Create procedure sp_hexadecimal
@ Binvalue varbinary (256 ),
@ Hexvalue varchar (514) OUTPUT
AS
DECLARE @ charvalue varchar (514)
DECLARE @ I int
DECLARE @ length int
DECLARE @ hexstring char (16)
SELECT @ charvalue = '0x'
SELECT @ I = 1
SELECT @ length = DATALENGTH (@ binvalue)
SELECT @ hexstring = '0123456789abcdef'
WHILE (@ I <= @ length)
BEGIN
DECLARE @ tempint int
DECLARE @ firstint int
DECLARE @ secondint int
SELECT @ tempint = CONVERT (int, SUBSTRING (@ binvalue, @ I, 1 ))
SELECT @ firstint = FLOOR (@ tempint/16)
SELECT @ secondint = @ tempint-(@ firstint * 16)
SELECT @ charvalue = @ charvalue +
SUBSTRING (@ hexstring, @ firstint + 1, 1) +
SUBSTRING (@ hexstring, @ secondint + 1, 1)
SELECT @ I = @ I + 1
END

SELECT @ hexvalue = @ charvalue
GO

IF OBJECT_ID ('SP _ help_revlogin ') IS NOT NULL
Drop procedure sp_help_revlogin
GO
Create procedure sp_help_revlogin @ login_name sysname = NULL
DECLARE @ name sysname
DECLARE @ type varchar (1)
DECLARE @ hasaccess int
DECLARE @ denylogin int
DECLARE @ is_disabled int
DECLARE @ PWD_varbinary varbinary (256)
DECLARE @ PWD_string varchar (514)
DECLARE @ SID_varbinary varbinary (85)
DECLARE @ SID_string varchar (514)
DECLARE @ tmpstr varchar (1024)
DECLARE @ is_policy_checked varchar (3)
DECLARE @ is_expiration_checked varchar (3)

DECLARE @ defaultdb sysname

IF (@ login_name is null)
DECLARE login_curs CURSOR

SELECT p. sid, p. name, p. type, p. is_disabled, p. default_database_name, l. hasaccess, l. denylogin FROM
Sys. server_principals p left join sys. syslogins l
ON (l. name = p. name) WHERE p. type IN ('s', 'G', 'U') AND p. name <> 'sa'
ELSE
DECLARE login_curs CURSOR

SELECT p. sid, p. name, p. type, p. is_disabled, p. default_database_name, l. hasaccess, l. denylogin FROM
Sys. server_principals p left join sys. syslogins l
ON (l. name = p. name) WHERE p. type IN ('s', 'G', 'U') AND p. name = @ login_name
OPEN login_curs

Fetch next from login_curs INTO @ SID_varbinary, @ name, @ type, @ is_disabled, @ defaultdb, @ hasaccess, @ denylogin
IF (@ fetch_status =-1)
BEGIN
PRINT 'no login (s) found .'
CLOSE login_curs
DEALLOCATE login_curs
RETURN-1
END
SET @ tmpstr =''
PRINT @ tmpstr
PRINT''
WHILE (@ fetch_status <>-1)
BEGIN
IF (@ fetch_status <>-2)
BEGIN
PRINT''
SET @ tmpstr = '-- Login:' + @ name
PRINT @ tmpstr
IF (@ type IN ('G', 'U '))
BEGIN -- NT authenticated account/group

SET @ tmpstr = 'create login' + QUOTENAME (@ name) + 'from windows with DEFAULT_DATABASE = [' + @ defaultdb + ']'
END
Else begin -- SQL Server authentication
-- Obtain password and sid
SET @ PWD_varbinary = CAST (LOGINPROPERTY (@ name, 'passwordhash ') AS varbinary (256 ))
EXEC sp_hexadecimal @ PWD_varbinary, @ PWD_string OUT
EXEC sp_hexadecimal @ SID_varbinary, @ SID_string OUT

-- Obtain password policy state
SELECT @ is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON 'when 0 THEN' off' else null end from sys. SQL _logins WHERE name = @ name
SELECT @ is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON 'when 0 then' off' else null end from sys. SQL _logins WHERE name = @ name

SET @ tmpstr = 'create login' + QUOTENAME (@ name) + 'with password =' + @ PWD_string + 'hashed, SID = '+ @ SID_string + ', DEFAULT_DATABASE = ['+ @ defaultdb +']'

IF (@ is_policy_checked is not null)
BEGIN
SET @ tmpstr = @ tmpstr + ', CHECK_POLICY =' + @ is_policy_checked
END
IF (@ is_expiration_checked is not null)
BEGIN
SET @ tmpstr = @ tmpstr + ', CHECK_EXPIRATION =' + @ is_expiration_checked
END
END
IF (@ denylogin = 1)
BEGIN -- login is denied access
SET @ tmpstr = @ tmpstr + '; deny connect SQL TO' + QUOTENAME (@ name)
END
Else if (@ hasaccess = 0)
BEGIN -- login exists but does not have access
SET @ tmpstr = @ tmpstr + '; revoke connect SQL TO' + QUOTENAME (@ name)
END
IF (@ is_disabled = 1)
BEGIN -- login is disabled
SET @ tmpstr = @ tmpstr + '; alter login' + QUOTENAME (@ name) + 'disable'
END
PRINT @ tmpstr
END

Fetch next from login_curs INTO @ SID_varbinary, @ name, @ type, @ is_disabled, @ defaultdb, @ hasaccess, @ denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
This code will be in the "master"DatabaseCreate two stored procedures. The two stored procedures are named "sp_hexadecimal" and "sp_help_revlogin" respectively.

Run sp_help_revlogin: exec sp_help_revlogin.

3. Grant the object permission to the user:

--DatabaseObject authorization in
Select 'Grant '+ (case when type = 'u'then' select, insert, update, delete'
When type = 'v'then' select, insert, update, delete'
Else 'exec 'end)
+ 'On ['+ name +'] to appconn'
From sys. objects where is_ms_shipped = 0 and type in ('P', 'U', 'fn ', 'V ')

4. query the permissions of a specific user and export the authorization script:

-- Export user permissions of the original database
Select object_name (a. id) as objectname,
User_name (a. uid) as usename,
Case B. issqlrole when 1 then 'group'
Else 'user'
End as Role,
Case a. protecttype when 205 then 'Grant'
When 204 then 'Grant'
When 206 then 'deny'
Else 'revoke'
End as ProtectType,
Case a. [action] when 26 then 'references'
When 178 then 'create function'
When 193 then 'select'
When 195 then 'insert'
When 196 then 'delete'
When 197 then 'update'
When 198 then 'create table'
When 203 then 'create database'
When 207 then 'create view'
When 222 then 'create PROCEDURE'
When 224 then 'execute'
When 228 then 'backup database'
When 233 then 'create default'
When 235 then 'backup Log'
When 236 then 'create rule'
Else '0' end as [Action],
User_name (a. grantor) as Grantor
From sysprotects a inner join sysusers B on a. uid = B. uid
Where exists (select 1 from sysobjects
Where [name] = object_name (a. id) and xtype <>'s ')
And (exists (select 1 from sysmembers
Where groupuid = a. uid and memberuid = user_id (@ username ))
Or a. uid = user_id (@ username ))
Order by object_name (a. id)

-- Generate an authorization statement
Select protecttype + ''+ action + 'on' + objectname + 'to' + usename
From (
Select object_name (a. id) as objectname,
User_name (a. uid) as usename,
Case B. issqlrole when 1 then 'group'
Else 'user'
End as Role,
Case a. protecttype when 205 then 'Grant'
When 204 then 'Grant'
When 206 then 'deny'
Else 'revoke'
End as ProtectType,
Case a. [action] when 26 then 'references'
When 178 then 'create function'
When 193 then 'select'
When 195 then 'insert'
When 196 then 'delete'
When 197 then 'update'
When 198 then 'create table'
When 203 then 'create database'
When 207 then 'create view'
When 222 then 'create PROCEDURE'
When 224 then 'execute'
When 228 then 'backup database'
When 233 then 'create default'
When 235 then 'backup Log'
When 236 then 'create rule'
Else '0' end as [Action],
User_name (a. grantor) as Grantor
From sysprotects a inner join sysusers B on a. uid = B. uid
Where exists (select 1 from sysobjects
Where [name] = object_name (a. id) and xtype <>'s ')
And (exists (select 1 from sysmembers
Where groupuid = a. uid and memberuid = user_id (@ username ))
Or a. uid = user_id (@ username ))
-- Order by object_name (a. id)
)

Flying over the sea