Prevent DB creation

Question

Prevent DB creation

Sreejith!

SSCrazy

Points: 2401
More actions
August 31, 2016 at 2:57 am

#321028

Hello,
I would like to prevent users from creating databases on a specific disk(say D :),as there is not enough room for any new DB's & available space can be userd by existing DB's.Is there any way we can prevent this by creating a trigger or policy?
Thanks in adavance:-)
Cheers

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

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

ManicStar SSCoach Points: 17992 More actions · Answer 1

If you are on 2016, you can reset the default paths to different locations that would help a bit if your users are not in the habit of specifying the paths.

https://msdn.microsoft.com/en-us/library/dd206993.aspx

Eric M Russell SSC Guru Points: 125552 More actions · Answer 2

I've never used it, but there is this Enterprise feature called 'Policy Based Management' whereby you can setup rules that govern things like server and database settings or even object naming conventions. The following MSDN article describes how to encorce a rule that data files and log files must be on separate drives. This isn't exactly what you want, but it's an example of how PBM works in general. I believe you can do what you want with PBM.

https://msdn.microsoft.com/en-us/library/bb402876.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Sreejith! SSCrazy Points: 2401 More actions · Answer 3

Thanks for the replies..Unfortunately I could not find any Server Facet to restrict this.I think we can create a Server trigger(for create database) & check Drive info using EVENTDATA() function.Not sure thought

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

Sreejith! (8/31/2016)
Hello,
I would like to prevent users from creating databases on a specific disk(say D :),as there is not enough room for any new DB's & available space can be userd by existing DB's.Is there any way we can prevent this by creating a trigger or policy?
Thanks in adavance:-)
Cheers

The best way, IMHO, is to not allow users to create databases at all. 😉

--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)

Eric M Russell SSC Guru Points: 125552 More actions · Answer 5

Talking about users creating databases, I assume we're really just talking about the development envrironment here, and when it comes time to deploy to production, you can adust the file locations as needed. Even in the DEV environment, you can go in behind the developers and (taking the database offline temporarily) switch around the location of files. You can toss reccomendations and mandates over the fence, but ultimately a DBA has to play the role of a soft tyrant.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Sreejith! SSCrazy Points: 2401 More actions · Answer 6

Thanks again for suggestions.But in our SQL estate,we have some application which create their own databases & also I would like to restrict my co-DBA's also mistakenly creating database on the drive where we have space issues.A pop up saying " Not enough space on this D Drive,Please create DB on another disk:-)"

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 7

This should impede DBAs

CREATE TRIGGER [DontTouchMe]

ON ALL SERVER

FOR CREATE_DATABASE

AS

IF CHARINDEX('D:\', EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')) > 0

BEGIN

ROLLBACK

RAISERROR('Slap',16,1)

END

GO

DROP TRIGGER [DontTouchMe] ON ALL SERVER -- it does not prevent, because DBAs can do this too... But it will prevent dbcreators, as long as they are not sysadmins.

Sreejith! SSCrazy Points: 2401 More actions · Answer 8

Thanks a lot..Above trigger did the trick:-):-):-)