database roles and default view permissions

Posted on 2011-02-17
Last Modified: 2012-05-11
is there documentation of what view permissions (in SSMS) each database role gets by default?

for example, datawriter- what does he get to see in SSMS?

Question by:anushahanna
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 41

Accepted Solution

Sharath earned 167 total points
ID: 34917844
Run these SPs and see the result.

exec sp_dbfixedrolepermission
exec sp_helpdbfixedrole

Open in new window

LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 333 total points
ID: 34918382
Fixed database roles are defined at the database level and exist in each database. You cannot add, delete or modify fixed database roles. You can only add users as a member of a fixed database role.

There are nine fixed database roles:


The members of db_owner database role can perform any activity in the database.

The members of db_accessadmin database role can add or remove Windows NT groups, users or SQL Server users in the database.

The members of db_datareader database role can see any data from all user tables in the database.

The members of db_datawriter database role can add, change, or delete data from all user tables in the database.

The members of db_ddladmin database role can make any data definition language commands in the database.

The members of db_securityadmin database role can manage statement and object permissions in the database.

The members of db_backupoperator database role can back up the database.

The members of db_denydatareader database role can deny permission to select data in the database.

The members of db_denydatawriter database role can deny permission to change data in the database.

Note. To add a security account as a member of an existing SQL Server database role in the current database, you can use the sp_addrolemember system stored procedure.


Author Comment

ID: 34918770
OK- So would the following be accurate:

db_owner/db_ddladmin  can see all objects in SSMS
db_accessadmin/db_securityadmin  can see logins and users only
db_datareader/db_datawriter/db_denydatareader/db_denydatawriter  will see all user tables
db_backupoperator can see only databases
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 333 total points
ID: 34918806
yep that pretty mutch soms it.

Author Comment

ID: 34919436

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question