migrationsql server
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.
Logins or server logins exists at the SQL server level, and the database user is at the database level. They need not have the same name, but generally do. In an instance, you cannot have a database user function properly without a corresponding SQL server login.
Let us say you have an instance: instanceA with a sever login userA on your computer. You can map this server login userA to a database user: userA for a database DB_A. They are both named userA, but one is the server login and one is the database user. The database user: userA resides in DB_A. When you backup and restore DB_A from instanceA to another instanceB, the database user: userA is moved, but the server login: userA still remains on instanceA. To move the logins from instanceA to instanceB, you can use sp_help_revlogin (Click to check out KB page).
If you had previously created the SQL server login userA on instanceB, then SQL will try to map this login with database user: userA, when DB_A is restored from instanceA to instanceB. If this fails, you can use the sp_change_users_login (Click for BOL link) procedure to fix the broken mappings. Hope this clears things up.
For logins, this is better IMHO:
http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror
And for jobs, you don't need SSIS. Just highlight the Jobs node in Management Studio, click on Object Explorer Details (F7), in the right pane select all, right-click and script.
I'd do the logins first, in case any of the jobs involve any of the logins.
Best Answer
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.