The db_denydatawriter fixed database role.
Members of the db_denydatawriter fixed database role cannot
add, modify, or delete any data in the user tables within a database. In order
to understand db_denydatawriter fixed database role, let us create a server
level login named denydatawriter having public fixed server role and we will
provide it access to the fixeddatabaseroles database with just db_owner and
db_denydatawriter rights.
In order to create the server level login named denydatawriter,
execute the T-SQL script as shown in the screen capture below:
This creates a server level login named denydatawriter. In
order to determine whether the login has been created or not just expand the
Logins node present under the Security node in the SQL Server Management
Studio, please refer the screen capture below.
Now we need to map the above login to fixeddatabaseroles database.
In order to do it, execute the below T-SQL script against the
fixeddatabaseroles database.
After mapping the login to the database, we then need to assign
the login db_owner and db_denydatawriter fixed database roles, please refer the
screen capture below which contains the T-SQL scripts to achieve the goal.
Please refer the screen capture below which shows that the user
named denydatawriter present in the fixeddatabaseroles have db_owner and
db_denydatawriter fixed database roles.
Connect
to the SQL Server Management Studio using denydatawriter login credentials.
User Name: denydatawriter
Password: P@ssw0rd
Now consider a case where the user having denydatawriter fixed
database role tries to INSERT data in the user table named STUDENT, please
refer the screen capture below:
As
seen from the above screen capture we can conclude that the user having
denydatawriter fixed database role cannot add the data into the user table.
Consider
another case wherein a user having denydatawriter fixed database role tries to
UPDATE the data present in the user table named STUDENT, please refer the
screen capture below:
From
the above screen capture we can conclude that the user having denydatawriter
fixed database role cannot MODIFY the data in the user table.
Consider
another case wherein a user having denydatawriter fixed database role tries to
DELETE the data present in the user table named STUDENT, please refer the
screen capture below:
From
the above screen capture we can conclude that the user having denydatawriter
fixed database role cannot DELETE the data present in the user table.
]]>