SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Custom Database Role
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmano1980
Starting Member

3 Posts

Posted - 03/05/2014 :  18:26:26  Show Profile  Reply with Quote
Hi All,

I have a created custom Database Role called Managers. This Role is member of db_owner role. I would like to remove the DROP Database and Restore Database privilege to this custom Role.
How do I do this?

Man

tkizer
Almighty SQL Goddess

USA
36833 Posts

Posted - 03/05/2014 :  20:12:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
Wouldn't you need to remove db_owner and then add the permissions you want? I don't think you can revoke drop/restore from db_owner.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

rmano1980
Starting Member

3 Posts

Posted - 03/05/2014 :  21:11:02  Show Profile  Reply with Quote
thanks Tara.
I was thinking of adding db_owner to the Custom Role. Then run a DENY DROP Database Command to the custom role. But I believe there is no DENY DROP database command ? Is that correct ?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36833 Posts

Posted - 03/05/2014 :  22:18:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
That was my point. I don't think you can do that. You probably can't use db_owner.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 03/06/2014 :  13:19:53  Show Profile  Reply with Quote
Grant the following for your custom role:

db_datareader
db_datawriter
db_ddladmin

Also grant the following:

GRANT EXECUTE ON schema::dbo TO {your custom role};

And also grant execute on any other schemas in the database.

This will give them pretty much everything except the ability to drop/restore databases. I would recommend that you test this out to make sure...
Go to Top of Page

rmano1980
Starting Member

3 Posts

Posted - 03/06/2014 :  23:31:01  Show Profile  Reply with Quote
thanking all of you for the insight.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000