x

How to stop user object creation in master database

HI,
I have many logins to the sql server. They create objects in master database. I want to stop them from creating any user objects in master database. What to do?

more ▼

asked Nov 13 '09 at 06:08 AM in Default

tempdba gravatar image

tempdba
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
more ▼

answered Nov 13 '09 at 07:34 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left

Don't give your users WRITE /CREATE permission on the Master Database.

Sounds to me that they have SA access which is really bad - they only need, and should only have, "god" access to the database(s) they need to work on.

more ▼

answered Nov 13 '09 at 11:44 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

Madhivanan's link has an answer that is

DENY CREATE DATABASE, CREATE TABLE            
TO Mary, John, [Corporate\BobJ]            

Or you can 1) create a role for the users, 2) add all users to that role, 3) Under database properties go to permissions section and Deny permission via the UI

more ▼

answered Nov 16 '09 at 03:44 PM

Rajib Bahar gravatar image

Rajib Bahar
238 3 3 7

Thanks - but it is Madhivanan's link :) (I edited his post just to make his URL work on this site, hence my name is listed too)
Nov 16 '09 at 04:54 PM Kristen ♦
Thanks for the clarification Kristen. I meant to credit Madhivanan.
Nov 18 '09 at 12:30 PM Rajib Bahar
(comments are locked)
10|1200 characters needed characters left

Question: Why do these logins even have permissions to the master database other than what they get by default from the public role? If it is your trusted DBAs or high-permission developers that are causing the problem, see my suggestion below.

Suggestion: Many times folks forget to change the query window to point to the specific database they mean to be accessing, and it stays on the default. And many times, the default database was left as master on their Login record. I would suggest changing their default database to something like TempDB or a custom database created just for this purpose where if they forget to change their database pointer, it causes no harm for them to create objects there and you can easily remove the objects because you know nothing is supposed to persist in that database, or with TempDB, the next time the service is stopped and restarted, it will get rebuilt and the old junk disappears.

more ▼

answered Nov 18 '09 at 04:05 PM

AjarnMark gravatar image

AjarnMark
100 3 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x83

asked: Nov 13 '09 at 06:08 AM

Seen: 1466 times

Last Updated: Nov 13 '09 at 11:44 AM