Video Screencast Help

SQL Agent Permissions

Created: 24 Aug 2013 | 4 comments

Hey,

Im having trouble with some of my SQL cluster backups.  Following the best practice tech article HOWTO21793, seen below, i am confused by the first practice the the third.  Please help me understand.  To me the first means that i need to add the windows user to the security of the database and assign the System Administrator privilege.  The third point seems to say do not add the windows user account to the database security.

The following best practices help you with security and database access with the SQL Agent:

1. Ensure that the Windows user account that you use to back up the SQL instances has System Administrator privileges.

2. Ensure that Backup Exec has rights to the following registry keys:

  • HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server
  • HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQL

3. Do not apply the credentials that you use to make SQL backup and restore selections to an actual SQL instance. The credentials that you use to make SQL backup and restore selections must be applied to the Windows computer where SQL is installed.

 

Operating Systems:

Comments 4 CommentsJump to latest comment

pkh's picture

The third step means that you add the BE account to Windows and not to the SQL instance

ajhstn's picture

Hi pkw,

the BE account is already added to windows, as it is a explicit local administrator on the SQL windows box, as well as a domain admin.

Can you clarify further what step 1 implies?

To give an account "sysadmin" privlidges, doesn't that mean it must be added as a user to the database and given that permission?

ajhstn

DNI_1's picture

Hi ajhstn,

 

Step 1 implies to use a windows account (Used to backup SQL instance) with System Admin privileges - Just incase if the account you use doesn't have sys admin privileges that could fail your backups due to denied access.

As mentioned in Step 3, you need NOT add this account to the SQL instance. Just Windows level access is fine.

 

 

 

Hope this helps!! Kindly, mark this as solution if it resolves your issue.

pkh's picture

This is correct.  For example, a domain admin will have sys admin privileges, but some other account may not.  You should also make sure that the Windows authentication is enabled for the SQL instance.