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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Preventing from "create database" with data...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Siva Ramasamy

Posted Tuesday, September 25, 2012 4:14 PM

Valued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:10 AM
Points: 60, Visits: 164

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.

Post #1364341

Jason Selburg

Posted Tuesday, September 25, 2012 5:25 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Post #1364366

Siva Ramasamy

Posted Tuesday, September 25, 2012 6:01 PM

Valued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:10 AM
Points: 60, Visits: 164

Jason,

Thanks for your response.

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

Thanks!

Post #1364378

Evil Kraig F

Posted Tuesday, September 25, 2012 6:06 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1364380

Siva Ramasamy

Posted Wednesday, September 26, 2012 9:11 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:10 AM
Points: 60, Visits: 164

Thanks Craig..Let me try that.

Post #1364772

Lowell

Posted Wednesday, September 26, 2012 9:26 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 11,648, Visits: 27,762

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1364788

« Prev Topic | Next Topic »

Permissions