|
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?
(comments are locked)
|
|
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.
(comments are locked)
|
|
Madhivanan's link has an answer that is
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 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)
|
|
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.
(comments are locked)
|
|
(comments are locked)
|

