One of my friends inquired that he wanted to create a user in sql server 2008 with minimum rights permissions and the user is allowed to select data by executing select statements from a database. I did some research and found the solution to assign minimum possible roles for a user to be able to take backup. To create such a user i have also created a script just to perform the task of creating a user for taking backup of database. The script creates minimum rights backup, restore role user/backup operator for SQL Server 2008 database. Using the T-SQL script minimal permissions are needed for backup and restore operations by the user created.

Using the T-SQL script following are the permissions/roles assigned at SQL Server instance and database level:

At SQL Server instance level following roles are to assigned to the user:

dbcreator
public

Using Selected Database following roles are to assigned to the user:

db_backupoperator
db_datareader
public

As a result user/operator created has minimum permissions for db access and is allowed to execute select statement for particular database.

To download the script use following link:

http://gallery.technet.microsoft.com/Script-for-user-operator-85608810