Understanding SQL Server Fixed Server Roles – Part 1

3 diskadmin

Members belonging to the diskadmin fixed server role can manage the disk files on the server. In order to understand the diskadmin fixed server role let us create a server login named diskadmin_login. In order to create the server login:

create login diskadmin_login with password='P@ssw0rd'

Once the login is created, then we need to map it to the Roles database:

Create user diskadmin_login for login diskadmin_login

To assign the login named diskadmin_login fixed sql server role named diskadmin, you will need to execute the below T-SQL query against the Roles database.

Exec sp_addsrvrolemember 'diskadmin_login', diskadmin

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

exec sp_addrolemember  'db_owner', diskadmin_login'

Consider a scenario where the user with the diskadmin fixed server role attempts to create a database named Student.

Connect to SSMS using diskadmin_login credentials:

User Name: diskadmin_login
Password: P@ssw0rd

From the above screen capture we can see that the user having diskadmin fixed server roles cannot create a user defined database.

Consider another case where the user having diskadmin fixed server role tries to create a server level login named example_login, please refer the screen capture below:

Thus we can see that the user having diskadmin fixed server role cannot create a server level login.

Consider another case where the user having diskadmin fixed server role tries to delete a backup file named Roles.bak present at the location C:\Backup. In order to achieve this task, we will use xp_cmdshell feature:

From this screen capture, we can see that the user having diskadmin fixed server roles can delete a backup file present on the server.

Processadmin Role

Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server. In order to understand the processadmin fixed server role we will create a server login named processadmin_login :

Create login processadmin_login with password='P@ssw0rd'

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

create user processadmin_login for login processadmin_login

To assign the login named processadmin_login fixed SQL Server role named processadmin, execute the below T-SQL query against the Roles database.

Exec sp_addsrvrolemember 'processadmin_login','processadmin'

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

exec sp_addrolemember 'db_owner', processadmin_login '

Consider a case where the user having processadmin fixed server role tries to create a linked server. Connect to SSMS using processadmin login credentials:

User Name: processadmin_login
Password: P@ssw0rd

From the above screen capture, we can see that members belonging to processadmin fixed server role cannot create a linked server.

Consider another case where the member having processadmin fixed server role tries to delete a backup file named Roles_backup.bak at the path C:\Backup. In order to achieve this task, we will use the xp_cmdshell feature:

As seen from the above screen capture, we can conclude that members having the processadmin fixed server role cannot delete a backup file present on the server using xp_cmdshell.

Consider another case wherein the member having processadmin fixed server role tries to KILL a running process on the server, please refer the screen capture below:

From the above screen capture, we can conclude that members having processadmin fixed server role can KILL the running processes on the server.

In the Part 2 I will discuss the remaining four fixed server roles.

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 |