asked
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).
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.
