1

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

1
  • Write a PoSh script that generates a list of logins, and passes that to Copy-DbaLogin from dbatools? It has Login as a parameter & takes lists. Nov 16, 2017 at 17:38

2 Answers 2

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.