Sean Wallbridge
A long time ago in a galaxy far, far away I was an Oracle DBA. And then I did a lot of work with SQL Server as I worked with a group that did a lot of Great Plains installs. Then, sadly, I didn’t do much work with SQL and I got super rusty, to say the least.
In the last little while I have been working with one of our large customers to migrate their ancient Great Plains install out of physical servers and into VM’s on top of Hyper-V. The process has been painful as they are running on ancient 2003 boxen and their SQL server was installed on their 2003 DC. Long story short, you can’t P2V a 2003 DC. So, we ended up building a Server 2008 VM and loaded it with 64 bit SQL 2005 and restored the GP databases to the new SQL instance using SQL backups created on the old server. All was good testing GP with the SA user but things fell down horribly when testing as regular GP users as they had zero access to anything.
I finally asked my colleague (and boss) Sean to have a look and he pointed out that restoring databases from backup does NOT restore user logins and passwords in SQL. So we went digging and found this KB from Microsoft:
https://support.microsoft.com/en-us/kb/918992
I was a little freaked out at the idea of running the script described in the post but I screwed up my courage and ran it, as described, on the original SQL box. It created a stored procedure and when I ran that procedure on the original box it produced output that I could cut/paste to the new box and with a little clean up it resulted in a series of SQL commands that I could run on the new box to recreate the user logins and passwords. Yay!
A little thing, I suppose, but super helpful when you need it!