Creating logins is not a rocket science in SQL Server. When creating a user, you might have seen an option where you can select default database.
So here you are assigning SampleDB to the user sql_user1
By assigning this, when use is logged in, he will be taken to SampleDB so that he he doesn’t have to change the database.
But what if this database is dropped later or permission for the user for this database is revoked.
So you have problems of logging to the SQL Server and you can change the default database to got away with this error.
In the login page, you can change the connection properties to connect to any database.
So what is the best database you should select as default database.
I will go for the tempDB for few reasons.
- All users has access to tempdb, so that logins will not failed.
- If default database is master, there is a change that mistakenly you will create objects like tables etc in that and most of the time you won’t be dropping them. In case, those objects are created in Tempdb, they will be dropped when the SQL Server restarts again!
Linkedin Group: SQL Server 2008
ReplyDeleteDiscussion: Default Database for Login
I like to create all logins with a default database of tempdb. This way if someone logs in and compiles an object without changing database contexts and does not clean up after themselves the object will be dropped the next time the service is restarted.
Posted by Orlando Colamatteo
Linked Group: PASS Professional Association for SQL Server
ReplyDeleteDiscussion: Default Database for Login
I am not seeing any text regarding this issue. But if the problem is the default database no longer exists, you get an error message when logging in and you cannot login to the server. This is incredibly short sighted on the part of the MS developers. So now you are no longer able to login to the server because the database you defaulted to is not available???
Besides logging in as SA (not having the password), are there any options available???
Posted by Victor Kushdilian
Linked Group: PASS Professional Association for SQL Server
ReplyDeleteDiscussion: Default Database for Login
So, if the user's default database has been dropped or deleted, "Cannot open user default database" seems like a fairly valid message. The resolution, as you describe, would be to set their database to a different one. Alternatively, drop the user altogether if they had no longer any valid need to connect to a given server.
Albeit, i haven't worked with many solutions that depended on the user's default database, mostly have seen the database specified on connection strings.
Posted by Chris Anderson
Linked Group: PASS Professional Association for SQL Server
ReplyDeleteI would suggest that best practice is a mix between what Ralph and Chris have mentioned. A user should have a default database selected and what that is depends upon the level of access that the user will have on the instance. If that user ONLY nees access to a single database (either directly or through an application) as a usere, then that database should be their default. In the event that Chris describes of that database being dropped then as a matter of course all logins that ONLY have access to that database should also be dropped. If however the user has a need for greater access - for example as a developer or DBA - then I would agree that master becomes a better option as their starting point since they may well be creating new databases etc.
As Chris has mentioned, if the user is using an application then the applications connection string should always include a specific database and this will over-ride any default database setting that the user's login has.
In my experience the clean up of logins that no longer need to be on the instance is a frequently overlooked practice ... in fact I have to admit to seeing very few clients with well defined security configurations. So many rely on the built in roles such as db_datareader - and I personally really wish Microsoft would remove them and make database designers actually consider security before deploying a new database.
Posted by Martin Cairney
Linkedin Group: PASS Professional Association for SQL Server
ReplyDeleteI would have to say that I don't agree with the idea of using TempDB as a default login database. The argument that the user might create objects that would be destroyed on server restart is valid in its simple context. But on long life systems (which seldom see restarts as often as implied by the argument) this clutter competes for space with processes that really need it. Sizing also becomes a bigger issue as you are now not just sizing for space you need to do normal work, but for space you didn't expect because of debris left behind by careless users.
I agree that if a user only has one reason to connect to the server, they should be directly tied to just that database. If that database has no reason to exist on that server any more, then neither do those specific users.
There is also (in my world) a difference between a 'user' and an 'account' in the system. A user implies a person. I have very few of those in my environment. An account however is most likely the way an application connects to its hosted database. That is the major type of 'user' of my databases. One can easily argue the exact same rules apply to these accounts. If the database the application depends on is relocated, or completely removed, the application no longer has a valid reason to be logging into my server. The counter argument on this is if the application depends on several databases for different reasons. Picking the default database the application logs into may be a little harder if all databases have equal standing to the application. (my scenario in my environment).
At that point, assuming the application is sufficiently locked down, you could argue towards the use of 'master' as the default database. As always, assuring that the account used does not have any rights greater then absolutely necessary to assure its operation.
As is always said: "It depends". Each environment's needs will change the rules of what 'default' really means.
Posted by Bruce Sherwood