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




Array

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 |