Migrate logins tagged to specific databases users

Question

We are doing migration from SQL2008R2 to SQL 2014 for some of the databases hosted on 2008R2 to 2014 and remaining DB's to other servers.

I am aware of migrating the login using dbatoo or using sp_helprevlogin..

But using that it would move all the logins from 2008R2 instance to 2014.

Since we are only moving some databases we only need to move their respective logins or i should say only those logins that are present under users in the database geting migrated to 2014 instance.

How can we achieve this intelligently via script, because doing manually is taking lot of time as we have hundred of logins and about 40 databases

Answer 1

I have a procedure called sp_SrvPermissions that has a @DBName parameter specifically for that. You can do something like this:

EXEC sp_SrvPermissions @DBName = '<DBName>', @Output = 'CreateOnly'

This will give you not just the script to create the LOGIN associated with just that database but any server level roles they are a member of and any server specific permissions they might have as well.

Answer 2

I'm in a bit of a rush but this'll do part of what you need. This will create a CREATE LOGIN statement for all SQL logins, you'll need to join against sys.database_principals if you want to pull logins that are associated with particular databases. You'll also need to tweak it if you need to deal with Window logins.

select 'CREATE LOGIN ' + QUOTENAME(name) + 
' WITH PASSWORD = 0x' + CONVERT(VARCHAR(MAX), LOGINPROPERTY(name,'PasswordHash'), 2) + ' HASHED, ' + 
' SID = 0x' + CONVERT(VARCHAR(MAX), sid, 2)
FROM sys.server_principals