x

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 '10 at 10:57 PM in Default

munna gravatar image

munna
51 5 5 6

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

1 answer: sort voted first

Munna,

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 '10 at 11:53 PM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

Thanks for the information William.
Sep 17 '10 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 '10 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x158
x9

asked: Sep 16 '10 at 10:57 PM

Seen: 1802 times

Last Updated: Sep 16 '10 at 11:28 PM