December 11, 2002 at 6:07 am
Trying to get to a point here now where we will be able to seperate all our online apps to their own servers. In the past on some I have handled thru a dummy db with a single login for some apps which have user specific DBs behind. But the one thing I have been playing with is seperate apps themselves where there is no user individualized DB to deal with. The thing I am trying is using an account specific to each app and set with the default db. I am finding in testing it allows me to create a single connection string then I just supply the appropriate username and password for the app. Looks clean and may make it to production at some point. Nice article.
December 11, 2002 at 6:55 am
Good argument Andy, but I'd go further and say that really (and we probably all don't do this) what we need to do is NOT connect to production databases using accounts which have privileges that allow us to do damage without thinking. If we did, then which one we connected to wouldn't matter so much.
Problem is, it's that old compromise between being able to get the job done quickly and potentially damaging data by using an account which can do (almost) anything. All too easy to run a DML command which you intended for a dev or test DB against the live DB if you're not paying attention. A good argument for using non-sa accounts to access production DBs, unless absolutely necessary.
Master is at first sight not an obvious default as it contains some hyper important data which you don't want to corrupt, but the chances of accidentally updating/deleting a table in master which has the same name as an application table is pretty slim (unless you have weird developers). TempDB is a good candidate, but how about using Northwind, it doesn't matter much if you damage that? However, as noted previously, if your sysadmin a/c's are set to non-master it can be a problem in a DR situation, just when you don't need the hassle.
Whichever you choose, what I would strongly argue is that whilst it's not too important which particular db is default, it is probably a good idea never to default to a production database if there are dev/test/prod DBs on the same server. All too easy to run that update/truncate/drop table against an object with an identical name on live and test / live and dev, without engaging the brain. And yes, developers should be specifying the db/catalog they want to use in their connection strings - make 'em type! 🙂
Edited by - jonreade on 12/11/2002 07:28:03 AM
Jon
December 11, 2002 at 9:24 am
Can I add a related question?
If access is granted through Windows NT Security and I appear in a couple of different groups, with differing Default Databases... is there any rule that determines which database that I start off in?
I would generally avoid using Master as the default database due to the amount of times people start up Query Analyser, compile a Proc, then realise that they're in the wrong place - it gets messy, not to mention dangerous!
December 11, 2002 at 9:33 am
Interesting question...dont know the answer.
As far as master, no one except SA's should be creating procs in master. In my world anyway.
Andy
December 11, 2002 at 12:26 pm
Have tended to avoid this because of security, but perhaps that is unfounded.
Good points brought up in the article and above and the more I think about it, the more I like having all logins default to a "standard" db, especially for dr situations. Still not sure about master, but tempdb or model might be good choices.
Thsi is for user accounts. for privledged accounts, like dbo, etc., I think master might be ok. They should have more responsibility and be able to handle it.
Steve Jones
December 12, 2002 at 1:49 pm
I always have set my logins to something other than master to protect master from users. What rights should be taken away from public to protect master, if master is used as the default?
Rosalind Philips
January 8, 2003 at 1:19 am
Does anyone have any thoughts on what rights should be taken away from the public role in master?
January 8, 2003 at 6:08 am
We simply never grant any rights to the public role for any object in any database in the first place. We set up sensible roles for different levels of users and admins for each db and they are the only 'roles/users' that have any rights.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
January 8, 2003 at 6:56 am
This is a very nice topic and I would definitely say I got a very good approach on administering sql logins. I am going to apply this immediately on our environments.
I agree with BobAtDBS regarding the rights for the public role. This is basically a system database role and therefore not a good idea to change its default rights (I don't even know if it is possible).
I also want to give emphasis on the concept that we as much as possible, restrict the access on master database to just the admini users and some special users perhaps. Depends on the application requirements. Therefore, I agree that default database should not be set to master.
cheers!
January 9, 2003 at 9:09 am
My logins all default to tempdb but after reading the comments I am changing default of sa to master. Thanks for the 'everyday work' topic applying to most readers.
January 9, 2003 at 10:59 am
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply