domain accounts, service accounts, logins....

Hi, under what context sql server agent service account and sql server service domain accounts work. What are the minimum permissions they should have to work normally. If a backup and restore job is created accross server what needs to be considered like the owner of the job should have permission on the other server share or the sql agent service or the sql server service.

one more thing If I have a sql Authentication login created can the person get into the sql instance from an other instance or should he should be a user also in the system user in any group. ( In the context of domain accounts).

more ▼

asked Sep 16, 2010 at 10:57 PM in Default

avatar image

51 5 6 8

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first


if you are installing sql 2005 or newer, the installer sets the domain account up with the permissions that are required to actually run SQL Server. It is a best practice to split the SQL Server service account and SQL Server Agent accounts, so each only has the rights that it needs (create two domain accounts and specify each one at install time).

As your backups will generally be jobs in the SQL Server, they would be run by the SQL Agent service account by default (you can specifiy a different user is you want). The user you choose would need permissions to connect to SQL Server, they would also be ideally made a member of the database role db_backupoperator. This role allows a member to make a backup of a database and membership is assigned per database.

The user doing the backup would also need access to a backup location, be that local to the server or a network share on a fileserver.

It can make sense to make a special SQL Server backup user in your AD for this purpose alone. The user would be made a member of the db_backupoperator role and supplied with access rights to the backup location and nothing else. The backup jobs on that server would be run by that user, with the knowledge that the backup user can only do backups.

One further tip to ensure that the backup user you create is always setup for new databases would be to add the backup user to the model database and assign it to the db_backupoperator role. That way, when a new database is created the backup user will be assigned the correct rights automatically.

Finally, SQL Authentication cannot be assigned to an AD user or group to my knowledge. You can connect to a SQL server using SQL Authenication if it is active, regardless from where you start the connection. You just need to setup the connection parameters the right way.

more ▼

answered Sep 16, 2010 at 11:53 PM

avatar image

26.2k 18 38 48

Thanks for the information William.

Sep 17, 2010 at 10:03 PM munna

If the information helped, please mark it as correct so that other people searching for the same information know that.

Sep 20, 2010 at 12:16 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 16, 2010 at 10:57 PM

Seen: 2421 times

Last Updated: Sep 16, 2010 at 11:28 PM

Copyright 2018 Redgate Software. Privacy Policy