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!