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?
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?
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
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.
No one has followed this question yet.