real value of default db

Posted on 2014-08-20
Medium Priority
Last Modified: 2014-09-08
when you create a default db, what is the real value of carefully choosing a specific db?

it could be any system db or user db? how would you decide which is the best for that login?

is keeping system database as default always safe? what may be cons of not keeping system db as default?
Question by:25112
LVL 21

Assisted Solution

by:Randy Poole
Randy Poole earned 1000 total points
ID: 40274456
If your talking about the default db for a new user, you would normally set it for the database the user was created for.  Administrators would normally be set to the master system db.  If the user was created for a specific database, there really is no reason for them to have access to the system databases
LVL 23

Accepted Solution

nemws1 earned 1000 total points
ID: 40274466
The only thing assigning a default database does is that it allows the user to skip issuing a "USE <database>" command when they connect.  Which, IMO is lazy anyways.  It's useful if you have per-application users (which you do, don't you?  If not, you should) which only ever connect to one database.  When they connect to the server, they'll automatically connect to the default database, even if they don't specify connecting to that database when they establish the connection.

Otherwise, it means nothing.  You can set 'master' as the default database for all users without any security worries.  If the user doesn't have permission to do anything in 'master', they won't be able to issue an SQL commands to that database.

I think you think it means more than it does.  Assigning a default database to a user does NOT give them any permissions to that database - you still need to do that separately.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question