Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

database roles and default view permissions

Posted on 2011-02-17
5
Medium Priority
?
342 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
[X]
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
5 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 668 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 1332 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 1332 total points
ID: 34918806
yep that pretty mutch soms it.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34919436
thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

636 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