How to stop developers from creating objects in master database

Question

Post reply

How to stop developers from creating objects in master database

dreamthebest

Ten Centuries

Points: 1324
More actions
November 13, 2009 at 2:10 am

#213965

User, Developers generally create objects in master database which is not good as it makes the log file full. So, how to stop them from creating objects in master database. Something, like trigger or something else will work except running stored procedure to change their database??

Viewing 14 posts - 1 through 13 (of 13 total)

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Grant them only the EXEC permissions on the Stored Procedures

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

NickDBA Hall of Fame Points: 3792 More actions · Answer 2

I don't think you can create DDL triggers on system databases. So possibly the best way is to do it through permissions as above.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

I've found that the real problem is that the default database for most logins is the MASTER database and the developers/user forget about that when they login. Change it to a "sandbox" database or even TempDB and 99% of those problems will likely go away.

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

dreamthebest Ten Centuries Points: 1324 More actions · Answer 4

Isn't there any way to create DDL triggers in system databases.

The idea of changing the default database is good but there are lots of logins and it will take time to check them. what about any other idea? How about any other way..do some1 have any idea...

GSquared SSC Guru Points: 260824 More actions · Answer 5

Deny them the rights to create objects in master. That's simple enough, unless they've got sa rights (which they shouldn't).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

george sibbald SSC Guru Points: 104210 More actions · Answer 6

dreamthebest (11/13/2009)
Isn't there any way to create DDL triggers in system databases.
The idea of changing the default database is good but there are lots of logins and it will take time to check them. what about any other idea? How about any other way..do some1 have any idea...

If you want to know quickly all the default databases run this

set quoted_identifier off

select 'exec sp_defaultdb ',+"'" +loginname +"'," +dbname from syslogins

you could then edit that very quickly and run it back in. be careful you only do it for users though, not 'sa' for example.

I know, I know, its 2000 code, buts its all I got and it will work.:-)

---------------------------------------------------------------------

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

dreamthebest (11/13/2009)
Isn't there any way to create DDL triggers in system databases.
The idea of changing the default database is good but there are lots of logins and it will take time to check them. what about any other idea? How about any other way..do some1 have any idea...

You can create a DDL trigger at the system level. In my environment there should be very few DDL changes except during deployments. MY experience is that even if you enable basically everything that can be watched (CREATE TRIGGER triggername ON ALL SERVER FOR DDL_EVENTS) doesn't generate an noticeable hit on performance. The only thing it doesn't pickup (thankfully) is operations that affect temporary objects in tempdb, but if you create a persisted table (CREATE TABLE tempdb.dbo.TableName) it does pick that up..

I think Jeff had the right idea, change defaut DB to tempdb, and that will solve 99% of the problems..

CEWII

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 8

tell them its a $20 fine per object created in master database.. should see that drop off fairly quickly.. or.. you have a lot of spare beer money which should make the whole thing a lot more palatable. 🙂

otherwise, i'd tend to go with limiting the permissions within the master database.. if they shouldn't be creating objects there, then they shouldn't have permissions there.. changing the default to tempdb is a good process too

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

dreamthebest Ten Centuries Points: 1324 More actions · Answer 9