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

  • 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. – Gareth Lyons Nov 16 '17 at 17:38
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
-1

While this may not be the answer you want, I use sp_helprevlogin because it gives me the ability to build that script and then clean up logins.

Migrations are a perfect time to run through that list of logins and users and get rid of the "badness" Are there logins for people who no longer work there? Are there logins for items that no longer need access to the database (think role change or application retirement) Any other old logins and users that are not used any more? Using sp_helprevlogin creates that script and allows you to do that audit. Then you can comment out the logins/users you determine are not needed and only bring over what is good. This makes your and your team's live better. This also makes your auditors happy...which definitely makes your life better. :-)

  • @ Thanks for the answer. But this might not be what i bam looking for.. I am looking a way to script out only those logins created at server level which have their users tagged to X databases – BeginnerDBA Nov 16 '17 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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