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
Solved

database roles and default view permissions

Posted on 2011-02-17
5
329 Views
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?

thanks
0
Comment
Question by:anushahanna
  • 2
  • 2
5 Comments
 
LVL 40

Accepted Solution

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

exec sp_dbfixedrolepermission
exec sp_helpdbfixedrole

Open in new window

0
 
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:


db_owner
db_accessadmin
db_datareader
db_datawriter
db_ddladmin
db_securityadmin
db_backupoperator
db_denydatareader
db_denydatawriter

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.

0
 
LVL 6

Author Comment

by:anushahanna
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
0
 
LVL 15

Assisted Solution

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

Author Comment

by:anushahanna
ID: 34919436
thanks!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

809 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