question

tempdba avatar image
tempdba asked

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?

permissions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Madhivanan avatar image
Madhivanan answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rajib Bahar avatar image
Rajib Bahar answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AjarnMark avatar image
AjarnMark answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.