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.
]]>