Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Preventing from "create database" with data and log file on C: drive. Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 4:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:39 AM
Points: 66, Visits: 186
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
Posted Tuesday, September 25, 2012 5:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 2,694, Visits: 3,750
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
Posted Tuesday, September 25, 2012 6:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:39 AM
Points: 66, Visits: 186
Jason,

Thanks for your response.

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

Thanks!
Post #1364378
Posted Tuesday, September 25, 2012 6:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 6,131, Visits: 7,171
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
Posted Wednesday, September 26, 2012 9:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:39 AM
Points: 66, Visits: 186
Thanks Craig..Let me try that.
Post #1364772
Posted Wednesday, September 26, 2012 9:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,877, Visits: 31,792
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 »

Add to briefcase

Permissions Expand / Collapse