SQL SERVER BACKUP Database Permission

When speaking about Permission term in SQL SERVER , It has two parts:

  1. Server Permissions
  2. Database Permissions

And from another point of view, the SQL SERVER ROLE also has two parts:

  1. Server-Level Roles
  2. Database-Level Roles

In fact the Security Model of SQL SERVER is a hierarchical model. Therefore,

  • If a SQL SERVER Login has sysadmin Sever-Level role, can perform any activity on server like backup any databases.
  • If a SQL SERVER Login does not have sysadmin Sever-Level role, you have to map thisSQL Login to the database you want to use. This mapping automatically create a newDatabase User with the same name of the SQL Login.
  • Now you have two choices:
  • GRANT BACKUP permission to that Database User.
  • Add db_backupoperator Database-Level Role to that Database User.

For sample script see this link:

Backup rights

Another useful links:

Principals

Permissions Hierarchy

I wrote this post based on one MSDN Forum question. For more discussion see this motivated link :

Database Admin

Advertisements

BULK INSERT multiple Files in SQL SERVER Express

This post comes from this MSDN Forum question.

Because of use SQL SERVER Express, we cannot use Integration Services(SSIS).

Therefore, we  have two choices:

  1. BULK INSERT
  2. OPENROWSET(BULK…)

In each statement you can insert only one column with above choices. Erland Sommarskog was provided a solution to insert multiple files in this link:

Bulk Insert Multiple Files (TSQL)

For more information you can also see this great article:

Bulk Inserts via TSQL in SQL Server