November 13, 2013 at 9:18 pm
Comments posted to this topic are about the item What is your default database set to?
November 14, 2013 at 2:17 am
It is definitely good to know the implications of choosing a default database.
A few years back I worked with the Remedy help desk system. I do not know if it has been changed for this issue, but back then the default database for the Remedy login had to be the Remedy database. The account also needed to be the db owner account as shown in database properties, or be set to use a default schema of dbo. This is because Remedy would create tables as required, and often used the syntax "CREATE TABLE xxx". The read statements typically took the form "SELECT FROM dbo.xxx" or "SELECT FROM remedydb.dbo.xxx", so you needed to make sure the defaults that applied to the table creates would work with the explicit names used for the select statements.
You are unlikely to have a one-size-fits-all solution to allocating the default database for a user, but you definitely need to know any special needs the user account has, and the implications around the database you choose.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 14, 2013 at 3:03 am
Happened to me just the other day! Took me a while to figure it out 🙂
November 14, 2013 at 4:25 am
I learnt it the hard way... while i cleaned up few databases (after migration), users complaint of not being able to connect... though error message was quite obvious and hence i changed the default db of logins to fix the issue
Cheers
November 14, 2013 at 7:23 am
We use a lot of applications with dedicated user and roles.
In that particular case, since the account should only have access to that specific DB, we do default to the specific DB as default.
For other "management" accounts, we default to master. We identified this issue a long time ago - the hard way like many - and without really thinking about it, we adopted this method. If your DB is down, the application that requires it will be down, which is really easy to spot 🙂
Thanks for the good explanation.
Olivier
November 14, 2013 at 7:54 am
I assign all of my SQL Server logins to the tempdb as the default database. NEVER master. This way no harm can be done by an application that just connects to the server without specifying a database name as part of the connection string. Ideally, I'd like to see an option of "none".
November 14, 2013 at 8:35 am
Mauve (11/14/2013)
I assign all of my SQL Server logins to the tempdb as the default database. NEVER master. This way no harm can be done by an application that just connects to the server without specifying a database name as part of the connection string. Ideally, I'd like to see an option of "none".
All users except sysadmins get tempdb as a default - anything they add there gets cleaned up next patch/reboot cycle, regardless.
Sysadmins get master, in case tempdb isn't available for some reason (tempdb volume failure, and so on).
November 14, 2013 at 9:19 am
Mauve (11/14/2013)
I assign all of my SQL Server logins to the tempdb as the default database. NEVER master. This way no harm can be done by an application that just connects to the server without specifying a database name as part of the connection string. Ideally, I'd like to see an option of "none".
I second this, I really wish that we could set this as a default so when someone creates a new user it would be already entered.
November 14, 2013 at 10:08 am
I like the idea of defaulting to tempdb. I've always used master, agree with not using any real db as a default.
November 14, 2013 at 12:54 pm
Did you try with sqlcmd?
November 14, 2013 at 12:58 pm
No I didn't try with sqlcmd. Honestly , I didn't think of that.
November 14, 2013 at 1:19 pm
I've always have any login with sys admin rights default to master, but any login with only database level rights defaults to their database.
I do not want any login with without server level rights to have any rights to anything other than their database. If there is a problem with their database, I don't want them connecting anyway.
November 14, 2013 at 2:39 pm
We follow the same as several comments, we set the sysadmins to master and everyone else to tempdb on all servers in all environments from Dev to Live.
We reasoned that tempdb should be available and if it wasnt you probably dont want users connecting and they probably would be having issues if they could
Also that tempdb doesnt get dropped, moved, renamed or restored in the normal course of business.
Sytems get moved from shared instances to dedicated servers.
Applications sometimes update the name of their database on upgrades.
On development and test servers it can be even worse with databases changing often, version numbers on Database names during regression testing, databases getting dropped as no longer used that are the developers default db . I have seen these and more and they were the cause of our choosing to use tempdb when we analysed the cause of the issues and realised we could take action to avoid it.
https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]
November 14, 2013 at 3:00 pm
Mauve (11/14/2013)
I assign all of my SQL Server logins to the tempdb as the default database. NEVER master. This way no harm can be done by an application that just connects to the server without specifying a database name as part of the connection string. Ideally, I'd like to see an option of "none".
Create a "none" database, populate it with table "FYI" having a single field "Notice" containing a single row with the value 'You should not be here.'
that's so funny.
November 14, 2013 at 3:49 pm
Tempdb, or some other safe, non-critical DB. Master is neither of those.
Have too many times connected to a server to run a bunch of DDL scripts, or compile a bucket of programmables, and then part-way through ... Blast - now I have to clean up master...
Yeah, that might speak to my work habits more than anything, but we're all human, right?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply