SQL Server Performance Forum – Threads Archive

Creating a role with all permission for db

I am wondering how can I create a role with all the permissions defined in the following predefined db roles.
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter

---

db_owner already has all permissions in the DB. WBR, Vlad A. Scherbinin

---

Does this means db_owner has the permissions defined in these roles db_accessadmin,db_securityadmin,db_ddladmin
db_backupoperator,db_datareader,db_datawriter.
More over I am wondering how can I see the permissions defined in the above role.
sp_helprole dont give much information.

---

Hi,<br /><br />sp_helprole shows the information about roles in current database<br />sp_helprolemember db_owner returns sid and membername <br />and <br />sp_helprotect to view the permission for the role<br /><br /><br /><br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]<br />Regards<br /><br />hsGoswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami<br />

---

Take a look at
BOL: Roles WBR, Vlad A. Scherbinin

---

BTW is there any information why you want to give such a super power. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I need this power packed role in my "automated role and privilege creation script".

---

Then you can use sp_addsrvrolemember to accomplish the task by assigning a login to these roles. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

The roles he’s talking about are database roles, not server roles – sp_addrolemember is sufficient. Vlad is correct in the first post, db_owner has all permissions to the database. Aaronsandy, You should be applying permissions on a least privilege basis. Define precisely what access your process needs and grant access accordingly.
Using ‘god-level’ simply obscures the issue and avoids understanding what is going on.

---

So the concluding remark is db_owner has power of all other fixed db roles.including
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter So does it also include db_denydatareader and db_denydatawriter..According to my knowledge its no..
If you can throw light on this ..I will appreciate

---

Roles are assigned permissions. db_owner is assigned all permissions on all objects in a database. Other roles are either expressly granted or expressly denied permissions.
Read Books online and you’ll understand the fundamentals that preclude this kind of question.

---

lol – no offence, but I like your advanced member status. I take it that it’s based on number of posts.

---

Yes, this forum software solely assigns a status based on the number of posts. Most forum packages use this way, AFAIK. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Promotion as db_datareader to db_datawriter [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>

---

]]>