t-SQL Solution to backup and restore
- river1 - Wednesday, November 8, 2017 12:44 PM
You can't modify permissions on admin shares - they are accessible to members of the local administrators group on the server where the drive is local.
Sue
- Jeff Moden - Wednesday, November 8, 2017 1:02 PM
Fixed that one for you Jeff.Put down that pork chop. Ha!
Lest anyone think I'm being mean, Jeff & I are friends.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey - Wednesday, November 8, 2017 3:21 PM
ROFLMAO!!! I love your tenacity for being completely accurate. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Grant Fritchey - Wednesday, November 8, 2017 10:01 AMSorry I did not understood.
Let me add the job code and an image of the job so that I can better ilustrate what I am not understanding.
I have this job:
which as this code:
Declare @DBName VARCHAR(200)
Declare @Path VARCHAR(1000)set @DBName = 'dwhcore_Prod'
set @Path = (select dbo.ReturnFullBackupPath (replace (@DBName, '_Prod', '')))--Sets DB in Single user Mode
exec ('ALTER DATABASE ' + @DBNAme + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')--Database back to Multi User mode
exec ('ALTER DATABASE ' + @DBNAme + ' SET MULTI_USER')--Restore lastest full backup from PROD
exec ('restore database ' + @DBName + ' from disk = ''' + @Path + '''')As per image, this is a one step job and as NO user configurated by default as the run as user.
This means that the step (all t-sql inside the step) will run under the SQL Agent Service account? or will run under the account of the person that will execute the job ?
Because this is not a job that will be executed on a daily basis. This is a job that will be execute only manually (by clicking on the job and request it to run).
So question is, what is the security account that will be used to run the job? my security account or the SQL Server agent account?
My view (probably wrong) is that, the person how will click on the job to run it, only needs to have permissions to execute the job, then, what the job is doing (go to the shared folder and restoring) requires permissions on the SQL Server agent account.
In summary:
Person that runs the job: Permissions to execute the job, nothing more (does not even need permissions to restore.
SQL Server Agent account : Permissions to be able to restore and permissions to go to the share \\servername\G$\...Am i correct?
Thank you
-
As an example, I have just changed the Job ower from SA to another account I am now getting the error:
Message
Executed as user: a2\sa-a2-capf-dwh-d-rm. Linked servers cannot be used under impersonation without a mapping for the impersonated login. [SQLSTATE 42000] (Error 7437). The step failed.This means that this account a2\sa-a2-capf-dwh-d-rm needs to have permissions (login) on the PROD server to where this linked server that exists in dev points to, correct?
- river1 - Thursday, November 9, 2017 12:24 AM
Correct.
SQL Server Agent account : Permissions to be able to restore and permissions to go to the share \\servername\G$\...
No, that permission is needed for the SQL Server account. At least, it is for backups, and I imagine it's the same for restores. But, as already discussed, you can't change the permissions on the G$ share. You would have to have the SQL Server account put into the local admins group on the server servername. Ask those who are refusing to create you a share whether it's preferable to do that, or create a share with suitably restricted permissions.John
- John Mitchell-245523 - Thursday, November 9, 2017 2:18 AM
Thank you for your answer John, but if this restore is going to be executed by a SQL Server Job under SQL Server Agent, why do you say that its the SQL Server Service account that needs to have permissions insted of the SQL Server Agent Service account?
- river1 - Thursday, November 9, 2017 2:58 AM
Because it's the database engine that's doing the restore. The SQL Server Agent account is running a SQL command, not accessing the files directly. This surprised me when I learned it recently, but now all our backup locations have permissions for the SQL Server account only, and they all work properly. But once again, don't take my word for this. Set it up so that SQL Server and SQL Server Agent have access to the share, and make sure it works. Then remove access for SQL Server, and see whether it still works. Then swap over so that only SQL Server Agent has access, and see what happens then.
John
-
They have finally agreed in sharing. So now its:
\\Servername\Backups\dwhCore\dwhCore_FULL_201711050200.bak
Now, question is, who needs to have permissions in this share?The SQL Server Agent Service Account where the job that has the step is running?
The SQL Server Service Account?
Or the owner of the job?
Thank you,
- river1 - Thursday, November 9, 2017 3:35 AM
What John said. The SQL Server service account.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you.
So the SQL Server Service account (Not the SQL Agent account) need to have permissions to go to:
\\Servername\backups\....
And to have restore permisisons (which by defaut it already has.
As for the user that will execute the job, the only permissions it will need is to execute the job it self, correct?
The user does not need any restore nor shared location permissions, correct?
-
Actually, I have changed the owner of the job to : a2\sa-a2-capf-dwh-d-rm$
and now I get the following infromation when trying to run the Job:
Executed as user: a2\sa-a2-capf-dwh-d-rm. Linked servers cannot be used under impersonation without a mapping for the impersonated login. [SQLSTATE 42000] (Error 7437). The step failed.
So as summary I think that permissions need to be like this:
Restore operations are executed by the SQL Server Service account.
Based on this, in each of the environments to be refreshed, SQL Server Service account needs the following permissions:
- G:\Backup of PROD DWH Server
- G:\Backup of the DWH Server to be refreshed
As for the account owning the Job: a2\sa-a2-capf-dwh-d-rm (Dev example)
- Granted permissions in PROD in msdb (backupset, backupmediafamily) using the LinkedServer
The user that triggers the job needs permission to execute the Job 05 – DWH RestoreFromProd (no additional permissions needed for the user)
Can you please validate?
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply