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 …