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, 2009 at 06:08 AM in Default

avatar image

tempdba
1 1 1 1

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

4 answers: sort voted first
more ▼

answered Nov 13, 2009 at 07:34 AM

avatar image

Madhivanan
1.1k 2 5 9

(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, 2009 at 11:44 AM

avatar image

Kristen ♦
2.2k 7 11 14

(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, 2009 at 03:44 PM

avatar image

Rajib Bahar
238 3 6 11

(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, 2009 at 04:05 PM

avatar image

AjarnMark
100 3 5 8

(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.

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:

x110

asked: Nov 13, 2009 at 06:08 AM

Seen: 2200 times

Last Updated: Nov 13, 2009 at 11:44 AM

Copyright 2016 Redgate Software. Privacy Policy