DB OWNER Role member and DROP DATABASE

Question

DB OWNER Role member and DROP DATABASE

Jp

Default port

Points: 1419
More actions
January 9, 2008 at 7:17 pm

#177913

Hi, In sql2005, member of db owner role can drop the database.How can we prevent this? I cannot remove users from db owner role.
I know this is the change in sql 2005 from sql2000.
Any Help?
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's not a new thing in SQL 2005. From SQL 2000 BoL:

DROP DATABASE permissions default to the database owner, members of the sysadmin and dbcreator fixed server roles, and are not transferable

You could perhaps write a DDL trigger that rolls back any drop database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jp Default port Points: 1419 More actions · Answer 2

Thanks Gail.

Is it possible to achieve this functionality in SQL2000?I have SQL2000 production dbs and I want to put similar controls like DB_Owners should not be able to DROP DATABASE.

Thanks.

--JP

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 3

I think the best you could do is monitor for it and take corrective action (make sure your backups are current!).

Begs the question though - if you are concerned with these people accidentally :crazy: (or deliberately :angry: ) dropping the database, should they be in the DBO role? What other stuff are they doing that requires it? How often are they required to do that stuff? Can a process be put in place that requires them to notify the DBA whenever they need to perform such tasks, thereby allowing you to restrict their access?

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare