Post reply

Preventing from "create database" with data and log file on C: drive.

  • September 25, 2012 at 4:14 pm

    #393252

    Hello everyone,

    Thanks for looking in to my question.

    I have a SQL Server 2008R2 dev environment. I have set the default data and log files path to some other drives other than C: drive.

    But when users create databases on the instance, they create it on the C: drive, which is causing the C: drive to run out of space.

    Any idea how can I prevent this..?

    I tried policy based management, but "on change prevent" is not available for datafile.

    Help please...

    Thanks!

    Siva.

  • September 25, 2012 at 5:25 pm

    #1542148

    Easiest way. Don't give them permissions to create databases.

    **I know that sounds like a smart-a$$ answer, but it's the best answer IMHO.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • September 25, 2012 at 6:01 pm

    #1542159

    Jason,

    Thanks for your response.

    I tried many times and have failed to convince the management...:-(

    Thanks!

  • September 25, 2012 at 6:06 pm

    #1542161

    Siva Ramasamy (9/25/2012)

    But when users create databases on the instance, they create it on the C: drive, which is causing the C: drive to run out of space.

    Any idea how can I prevent this..?

    Look into server event triggers. I believe you might be able to capture the attempt and disrupt it if they're going to C:, but I'm not sure and haven't done a lot of research there.

    - 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

  • September 26, 2012 at 9:11 am

    #1542482

    Thanks Craig..Let me try that.

  • September 26, 2012 at 9:26 am

    #1542497

    i think in addition to a DDL trigger, you want to change the default paths as well;

    a simple CREATE DATABASE SANDBOX' command doesn't specify the path, so unless you changed it, it's going to get created in the default directory, probably something like C:\Programfiles\Microsoft SQL Server\MSSQL.1\MSSQL or something.

    if you right click on a server in SSMS and select "facets", you can change the default paths for backups and the default directory for new databases.

    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!

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

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