Solved

database roles and default view permissions

Posted on 2011-02-17
5
324 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
create an aggregate function 9 34
MS SQL + Insert Into Table - If Doesnt Exist 9 35
Deal with apostrophe in stored procedures 8 42
VB.NET 2008 - SQL Timeout 9 24
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

773 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