An hour ago my team was trying to figure out why few maintenance plans on one of the Production Instances is failing. The job was failing with the below error message.
The Execute method on the task returned error code 0x80131904 (Cannot open user default database. Login failed. Login failed for user '****\SQLAgt'.)
From the error message it is obvious that the owner of the job SQLAgt does not have access to its default database. Out of curiosity, I logged onto the instance and issued sp_helplogins command for that login (it is a group of which SQLAgt is a member).
Opened the login properties in SSMS.
The Default database is blank. For sure that database does not exist or is offline. A quick sp_helpdb confirmed that the database does not exist. Then I recollected that some time back team had implemented a Change Control to drop one of the databases on this instance. But while implementing it they did not take this consideration. As a result any logins which had this dropped database as the Default database will not be able to login to the instance. Executing this query will change the default database to master for that login.
Exec sp_defaultdb @loginame='DomainName\SQLAgt', @defdb='master'
I use the following scripts to look for any logins that have the to-be dropped database as the Default Database, before dropping the database.
SELECT name FROM syslogins WHERE dbname='DatabaseName'
SELECT name as [LoginName] FROM sys.server_principals WHERE is_disabled = 0 AND default_database_name='DatabaseName'
This situation could have been easily avoided, had the DBA checked for the logins that have this database as default, before dropping the database. Now one more Change Control, approvals and so on for no reason.