How can I DENY create indexes to a DDL_Admin group

Question

How can I DENY create indexes to a DDL_Admin group

4x4krzy

Ten Centuries

Points: 1173
More actions
December 5, 2014 at 12:36 pm

#299218

I am trying to manage indexes on a dev environment that went into production. There are 250 or so indexes on 63 tables & 1313 columns and only 25 indexes are being used.
I would like to DENY create index to anyone that has DDL_Admin and have not found a way to do this yet.
DENY [CREATE INDEX] TO [Domain\DDL_Admin_User]
Does anyone have any suggestions? Maybe a trigger that blocks the creation of indexes? If a trigger is the way to go, what kind of impact could that have?
Thank you much

Viewing 8 posts - 1 through 7 (of 7 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

I know it's not the direct answer to your question, but there's an implication here that concerns me.

You want people able to create tables (DDL_Admin), modify tables, etc... but not create indexes? This sounds like a recipe for disaster. If these people can't be trusted not to create poor indexes (or not to cowboy in general), they shouldn't be DDL_Admin in Production.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 2

Maybe a trigger that blocks the creation of indexes? If a trigger is the way to go, what kind of impact could that have?

What will stop someone with sufficient permissions to disable the trigger?

Alex Suprun

4x4krzy Ten Centuries Points: 1173 More actions · Answer 3

They don't have DDL_Admin in prod, only dev.

The ETL developers push through any changes with a proxy account and to date, none of the indexes were checked.

They can do pretty much what they like in dev. There is more than one dev group and having duplicate indexes is starting to be an issue. Nothing gets checked before it gets pushed to prod.

I am getting a review process in place starting next week, but I'm trying to stop the crazy index building without thinking it through.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

I agree with the previous posts... deny the DDL_Amin priv.

Now, if that doesn't quite work out for you and they have to have the priv to do their job, then the best thing to do would be to teach them how, when, and why to create an index. Have a couple of Lunch'n'Learns on the subject. They'll love you for it... seriously.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael L John One Orange Chip Points: 27060 More actions · Answer 5

A DDL trigger will work.

Something like:

CREATE TRIGGER [DDL_NoIndexes] ON DATABASE

FOR create_index, alter_index

AS

ROLLBACK

PRINT 'Please don''t do that!'

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [DDL_NoIndexes] ON DATABASE

GO

I have the same issue. The developers do a good job with tables, but indexes are another story. When I came on board there were a number of tables that had 20-30 indexes on them.

All of the developers are now on the same page when it comes to indexes.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 6

4x4krzy (12/5/2014)
Nothing gets checked before it gets pushed to prod.

That's the root cause of your issues, that's what you have to fix. It's not technical issue and should not be resolved by triggers or permissions. Your software development process has to be fixed. How you develop, test and deploy to production has to change.

Alex Suprun

4x4krzy Ten Centuries Points: 1173 More actions · Answer 7

That's why I am getting a proccess in place to get objects pushed to prod.

I still do not want any of the devs creating indexes. I have seen on a 30 column table 29 indexes and no matter what you say or how you explain it some devs don't get it.

It is a matter of training the devs so they understand how & what indexes the indexes are, but that's a people issue. I am looking for a SQL solution for now.

Thank you Michael for the trigger code. I'll be using it along with some other code I wrote.