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.