Understanding SQL Server Fixed Server Roles – Part 1

When considering the security and protection of the database engine, one of the first considerations that comes to mind is SQL Server Fixed Server Roles. The Server Roles are named Fixed Server Roles as the user cannot alter these under any conditions. Assigning the appropriate roles to the user helps keep the SQL Server Environment safe.

There are 8 types of Fixed Server Roles in SQL Server 2008 R2 :

· Bulkadmin

· Dbcreator

· Diskadmin

· Processadmin

· Securityadmin

· Serveradmin

· Setupadmin

· Sysadmin

In order to understand the fixed server roles, we will create a database named Roles. To do this, please execute the below T-SQL query against the master database:

Create
Database Roles

Let us now understand each of the above roles in detail.

Bulkadmin Role

Members of bulkadmin fixed server roles can perform BULK operations. In order to understand the bulkadmin fixed server role let create a server login named bulkadmin_login, please execute the below T-SQL against the master database:

create login bulkadmin_login with password='P@ssw0rd'

This creates a server level login named bulkadmin_login.

Once the login is created, we need to map it to the database named Roles. To do so, you will need to execute the below T-SQL query against the database named Roles.

Create user bulkadmin_login for login bulkadmin_login

To assign the login named bulkadmin_login to the Fixed SQL Server Role bulkadmin, please execute the below T-SQL query against the Roles database.

Exec sp_addsrvrolemember 'bulkadmin_login','bulkadmin'

Once the user is created, then we will assign it db_owner fixed database role:

exec
sp_addrolemember 'db_owner','bulkadmin_login'

Let us now consider a case where the user having bulk admin fixed server role tries to create a new database named ABC.

Connect to the SQL Server Management Studio using bulk_admin login credentials.

User Name: bulkadmin_login

Password: P@ssw0rd

Try to create a database named ABC  by executing the below T-SQL against the master database.

Create Database ABC

Please consider the screen capture below:

As seen from the above screen capture, we can conclude that the user having bulk admin fixed server roles cannot create a user defined database.

Consider another case, wherein the user having the bulk admin fixed server role tries to create a server level login named test_login mapped to the Roles database, In order to do so, execute the below T-SQL query against the Roles database:

Create login test_login with password=’P@ssw0rd’

As seen from the above screen capture, the user with the bulk admin fixed server role is not permitted to create a server level login.

Consider another case where a user with the bulk admin fixed server role tries to perform a BULK INSERT operation. ie the student data present in the file named student.txt located at the location C:\Student\student.txt is to be imported in a table named student present in the Roles database.

The text file named student has 2 rows of data as follows:

1, satnam, singh
2, Robert, clark

Connect to the SSMS using the bulk admin login credentials and execute the below query against the Roles database:

BULK INSERT Roles.dbo.Student
FROM ‘C:\Student\student.txt’
WITH
(
FIELDTERMINATOR=’,’
)

From the above screen capture, we can conclude that the user having bulk admin fixed server roles can perform the BULK INSERT operations.

dbcreator Role

Members of the dbcreator fixed server role can create, alter, drop, and restore any database. In order to understand the dbcreator fixed server role let us create a server login named dbcreator_login, please execute the below T-SQL query against the master database:

create login dbcreator_login with password='P@ssw0rd'

Once the login is created, then we need to map it to the Roles database, therefore execute the below T-SQL query against the Roles database:

create user dbcreator_login for login dbcreator_login

To assign the login named dbcreator_login fixed sql server role named dbcreator, please execute the below T-SQL query against the database named Roles:

Exec sp_addsrvrolemember 'dbcreator_login', 'dbcreator'

Once the user is created, then we will assign it db_owner fixed database role. In order to do so, execute the below T-SQL against the Roles database.

exec sp_addrolemember 'db_owner','dbcreator_login'

Consider a case wherein the user having the dbcreator fixed server role attempts to perform a BULK INSERT operation.

Connect to the SSMS using the dbcreator_login login credentials.

User Name: dbcreator_login
Password: P@ssw0rd

Execute the below T-SQL query against the Roles database.

BULK INSERT Roles.dbo.Student
FROM 'C:\Student\Student.txt'
WITH
(
FIELDTERMINATOR=','
)

From the above screen capture, we can conclude that the user with the dbcreator fixed server role is not premitted to perform a BULK INSERT operation.

Consider another case where the user having dbcreator fixed server roles tries to create a server level login named example_login:

Create login example_login with password='P@ssw0rd'

From the above screen capture, we can conclude that the user having dbcreator fixed server role isn’t allowed to create a server level login.

Consider another case where the user having dbcreator fixed server role tries to backup the database to the location ‘c:\backup’:

BACKUP DATABASE Roles TO DISK='C:\Backup\Roles.bak'

From the above screen capture, we can see that the user with the dbcreator fixed server role is allowed to BACKUP the database.

Consider another case where the user having a dbcreator fixed server role tries to drop the database named Roles:

DROP DATABASE Roles

Thus we can see that the user with a dbcreator fixed server role can drop the database.

Let us consider another case where the user having dbcreator fixed server role tries to restore the database:

Thus we can see that a user with the dbcreator fixed server role can restore the database.

Continues …

Pages: 1 2




Related Articles :

8 Responses to “Understanding SQL Server Fixed Server Roles – Part 1”

  1. As a administrator, I always used to wonder how to secure MS-sql, from malicious edits by users, This article explains it all, and saves a loads of blame games, which later we audit and track, But as said prevention is always better than cure, since these roles come handy…

  2. Thank you.

  3. Very good article!!

  4. Good article. When is its part 2 coming :)?

  5. Thank you for sharing with us. Good stuff!

  6. I do not even know how I ended up here, but I thought this post was good. I don’t know who you are but definitely you’re going to a famous blogger if you aren’t already ;) Cheers!

  7. Good post,easy to understand with images and text. looking forward to the part II.

  8. Very useful information and easy way to understand the server role concepts.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |