May 3, 2017 at 1:57 pm
I have an application that does batch processing, that is used once a week. I previously had a standalone backup program in C# that I would run, that would backup the database. The credentials used for the connection were for sa. Now I have embedded a backup routine into the application itself, that the user uses. For this one the credentials for the connection are based on their windows credentials.
Here is the embedded backup routine:
string str1 = @"BACKUP DATABASE econtrack TO DISK = @backupname WITH FORMAT,MEDIANAME = 'Z_SQLServerBackups',NAME = 'Full Backup of tracking system';";
SqlCommand cmd2 = new SqlCommand(str1, GlobalVars.g_oconnection);
cmd2.Parameters.AddWithValue("@backupname", c_backupname);
cmd2.ExecuteNonQuery();
This is under SQL 2012.
Last week I had to restore the database due to a crash on our SAN. After I did the restore the user got an error when opening the app / connecting to the database. Unfortunately I was not there that day and didn't see screen shots and don't know the exact sequence of events. I believe that my coworker restored the database when logged into ssms with their own windows credentials. At some point we ended up going to a point in time vm restore that was prior to the crash, so I didn't have to deal with the connection issues. But this made me realize, I think, that there are flaws in my backup and restore process.
My recollection is that in the past, for another application I supported which was on sql 2000 (still never upgraded, lol) that whenever a restore was done, we had to execute: exec sp_changedbowner vamlogin (this was an accountmate system)
My understanding was that the credentials used to do the restore were the ones that governed the database ownership, i.e. that if you restored with sa, you needed to change the owner back to vamlogin, because the owner got changed to sa.
My questions on this are:
1) Do the credentials used to execute the restore govern the database owner /change the database owner - if so, is this for all versions of sql?
2) Do the credentials used to execute the backup matter?
3) What are the best practices for this? Now that I have an embedded backup routine, run by the user, under what credentials should the backup run? Does that matter, or is the restore user what matters?
May 4, 2017 at 8:28 am
itguy2015 - Wednesday, May 3, 2017 1:57 PMMy questions on this are:
1) Do the credentials used to execute the restore govern the database owner /change the database owner - if so, is this for all versions of sql?
2) Do the credentials used to execute the backup matter?
3) What are the best practices for this? Now that I have an embedded backup routine, run by the user, under what credentials should the backup run? Does that matter, or is the restore user what matters?
1. Yes. There are quite a few good reasons not maintaining the original owner but one of the biggest is that account may not even exist on the server where the restore is done and the assumption is that whoever is doing the restore has the permissions to change the database owner if needed.
2. No
3. It's the restore that matters. The owner of the database itself can be changed and the practices around who owns it vary. Some use loginless users, some sa.
Sue
May 4, 2017 at 8:59 am
Sue_H - Thursday, May 4, 2017 8:28 AMitguy2015 - Wednesday, May 3, 2017 1:57 PMMy questions on this are:
1) Do the credentials used to execute the restore govern the database owner /change the database owner - if so, is this for all versions of sql?
2) Do the credentials used to execute the backup matter?
3) What are the best practices for this? Now that I have an embedded backup routine, run by the user, under what credentials should the backup run? Does that matter, or is the restore user what matters?1. Yes. There are quite a few good reasons not maintaining the original owner but one of the biggest is that account may not even exist on the server where the restore is done and the assumption is that whoever is doing the restore has the permissions to change the database owner if needed.
2. No
3. It's the restore that matters. The owner of the database itself can be changed and the practices around who owns it vary. Some use loginless users, some sa.Sue
Thanks Sue. This is very helpful to know / confirm. I think based on this, it will be best to change the owner back to sa, and always do the restores under sa, so ownership remains with sa.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply