Migrate SQL Server database with instance level logins

Question

Is there a way to migrate database along with all users and logins at the instance and database levels?

I am aware of contained databases and orphan users and solution for that.

I am using SQL Server 2012, 2014, and 2016. I came across another blog that Import Export functionality can be used, but yet have to try it.

Answer 1

Check out this:

sp_help_revlogin

This stored procedure, provided by Microsoft in the link below, will generate a T-SQL script that will copy the logins, their passwords and their corresponding SIDs (or security identification number, which is usually the culprit here). This is also a great utility when the database has numerous users with various security levels and passwords.

https://support.microsoft.com/en-us/kb/918992

This scripts out all Instance level logins. You can use this scripts and re create logins and map them to database in destination server.

Hope this helps.

Answer 2

There are some fantastic Powershell scripts located at dbatools.

You could use Copy-SqlDatabase to migrate your databases.

Then, you could take advantage of Copy-SqlLogin by generating the Powershell commands via T-SQL script for the logins you're interested in.

select 'Copy-SqlLogin -Source sqlserver -Destination sqlcluster -Logins ' + name + '' from sys.database_principals where type_desc='sql_user'

After migration, make sure you take care of orphan users. You could use something like this:

declare @cmd varchar(max)=''
select
 @Cmd=@cmd + 'ALTER USER ' + dp.name + ' WITH LOGIN = ' + dp.name + ';' + char(10)
from
 sys.database_principals dp left join
 master.sys.server_principals sp on
 sp.name=dp.name
where
 dp.type='s' and
 dp.default_schema_name<>dp.name and
 sp.name is not null
order by dp.name
--print @Cmd
exec (@cmd)