Remove DBCC Permissions

Question

Post reply

Remove DBCC Permissions

mitchismoney

Mr or Mrs. 500

Points: 533
More actions
June 16, 2010 at 11:03 am

#220071

Is there any way to prevent members of the db_owner role from running the DBCC ShrinkFile command?
We have users that have begun to rely on this to free disk space instead of fixing their process. With our setup this actually doesn't do them any good as the logs are on a different drive and the .mdf drive is the one suffering from loss of space.
So ultimately I want to prevent them from being able to run the command.
Thanks!
_______________________________________________________________
Its not how far you go, its how go you far...:crazy:

Viewing 5 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

DBCC SHRINKFILE is granted to the roles sysadmin and db_owner

to remove permissions from a user, you'd most likely take away the db_owner role from them.

there is no other way to remove that permission.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

mitchismoney (6/16/2010)
Is there any way to prevent members of the db_owner role from running the DBCC ShrinkFile command?

No. Db_owner has full control over the database and can do anything to the database (including drop it).

Why do the users have db_owner permissions in the first place? That's far higher than anyone other than the DBA should have.

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

mitchismoney Mr or Mrs. 500 Points: 533 More actions · Answer 3

Thanks for the replies, that's kind of what I was thinking.

The database group was a relatively new area for my company and as such was set up with an "open" administration policy and no DBA, therefore database developers were given db_owner permissions to create/configure their environments as they needed.

I am now the first DBA in my company, so I am going through permissions, architecture....etc to determine what needs to be changed/re-configured in the environments. So one of the first things is changing the "it's the way it's always been" mentality and getting our database teams and environments in a better state.

Thanks again.

Darren

_______________________________________________________________
Its not how far you go, its how go you far...:crazy:

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Good luck.

I have no problems with devs having db_owner in the development environment, but they shouldn't have elevated permissions in production.

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