Fixing the Orphaned SYSDBA SQL User

Ok, I see in my logs constantly google searches for “fixing the sysdba user“ that lead to this blog. This is a common thing I find in my logs, but strangely enough, I’ve never posted anything about fixing the sysdba user. So, drumroll…

 

sp_change_users_login 'Update_One', 'sysdba', 'sysdba'

 

Ta-da! For people who work with SalesLogix often, this is something well known. However this isn’t something just related to SalesLogix. But first, for those who might not know, a little background. When you make a backup of a SQL database and move it to another server. The database has stored the users who have been granted access to the database, such as the SalesLogix SYSDBA user. When you restore the database on the new server, the database SYSDBA user needs to be matched up with the server level SYSDBA user since the ID’s of the SYSDBA user between servers would be different. As I mentioned, it’s nothing specific to SalesLogix – any database that uses mixed security with SQL accounts would need the orphaned SQL users matched with the server after a restore on a new server. What the proc above is doing is telling it to match the server level SYSDBA login with the database level SYSDBA login. You can also do this as follows to just tell it to automatically match up a user named SYSDBA on both sides.

 

sp_change_users_login 'auto_fix', 'sysdba'

 

Either way works the same and accomplishes the same result. Anyway, at long last, that is it. We can all sleep better now.

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

1 Comment

  1. Hey Brad, AFAIK they are identical. The "Update_One" method allows you to explicitly specify a database user with a server user (hence the need to enter ‘sysdba’, ‘sysdba’ twice – you’re indicating the name of the database user with the name of the server user).

    Using "auto_fix" assumes that the user name is the same on both the server and the database (which works fine for SLX).

    -Ryan

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!