From the below screen capture
we can conclude that as soon as the user named datareader having db_datareader
fixed database role tries to create a table named student in the
fixeddatabaseroles database, he has been denied from doing so.
Consider another case where the user having db_datareader fixed
database role tries to read the data from the table named sysobjects, please
refer the screen capture below:
Thus we can conclude that the user having db_datareader fixed
database role can only perform the read operations. Apart from that all other
operations will not be allowed to him.
The db_datawriter fixed database role
Members of the db_datawriter fixed database role can add,
delete, or change data in all user tables. In order to understand db_datawriter
fixed database role, let us create a server level login named datawriter having
public fixed server role and we will provide it access to the
fixeddatabaseroles database with just db_datawriter rights.
In order to create the server level login named datawriter,
execute the T-SQL script as shown in the screen capture below:
This creates a server level login named datawriter. 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.
In order to provide the above login access to the database fixeddatabaseroles
execute the below T-SQL script against the fixeddatabaseroles database.
CREATE USER datawriter for LOGIN datawriter
This creates a login named datawriter which has access to the
fixeddatabaseroles database.
In order to provide it db_datawriter fixed database role, execute
the below T-SQL script against the fixeddatabaseroles database.
Once
the above script gets executed, the user named datawriter having access to
fixeddatabaseroles database gets db_datawriter fixed database role, please
refer the screen capture below.
Now consider a case where the user named datawriter tries to
create a table named student in the fixeddatabaseroles database. In order to do
so, connect to the SQL Server Management Studio using datawriter login
credentials.
User Name: datawriter
Password: P@ssw0rd
From the below screen capture
we can conclude that as soon as the user named datawriter having db_datawriter
fixed database role tries to create a table named student in the
fixeddatabaseroles database, he has been denied from doing so.
For understanding purpose, I
connected to SSMS using sa credentials and created a table named student. Then
I again connected to SSMS using datawriter credentials and tried to perform a
SELECT operation on the student table and found that logins having datawriter
fixed server role are not allowed to perform SELECT operation on the user table
whereas if they try the same on system tables, they will be able to perform it,
please refer the screen capture below.
Login having datawriter fixed
database role tries to perform SELECT operation on the student table and was
DENIED from doing so.
Login having datawriter fixed
database role tries to perform SELECT operation on the system table named
SYSOBJECTS and SUCCEEDED.
Consider another case when the
login having datawriter fixed database role tries to INSERT the data into the
student table.
From the above screen capture,
we can see that the login having datawriter fixed database role can perform INSERT
operation on the user table.
Consider another case when it
tries to UPDATE the data in STUDENT table.
As seen from the above screen
capture, we can conclude that the login having db_datawriter fixed database
role can perform an UPDATE operation on the user table.
Consider another case where a
login having datawriter fixed database role tries to perform a delete operation
on the user table, please refer the screen capture below.
As seen from the above screen
capture we can conclude that the login having datawriter fixed database role
can DELETE data from the user table.
Consider another case where the
login having datawriter fixed database role tries to create a server level
login named test, please refer the screen capture below:
As seen from the above screen
capture we can conclude that login having datawriter fixed database role cannot
create a server level login.