How to change SQL Server login default database through SQL Script
I am moving a SQL Server database from one drive to another by detaching and then reattaching. I detached the database, moved the mdf and ldf files, and then went to attach it and was presented with this dialog:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.ADDITIONAL INFORMATION:
Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)
This is because my login had the default database set to the database that I just detached. This causes all sorts of errors with SQL Server Management Studio but none of them are particularly helpful, they pretty much just keep telling you “access denied” but not why or what to do.
This is the dialog you would get if you try to click on the properties for your login:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476Failed to connect to server. (Microsoft.SqlServer.ConnectionInfo)
Cannot open user default database. Login failed.
Login failed for user 'login'. (Microsoft SQL Server, Error: 4064)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476
And then even if you close and open SQL Server Management Studio you will get this dialog:
TITLE: Microsoft SQL Server Management Studio
Failed to connect to server. (Microsoft.SqlServer.ConnectionInfo)ADDITIONAL INFORMATION:
Cannot open user default database. Login failed.
Login failed for user 'login'. (Microsoft SQL Server, Error: 4064)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476
What you can do to fix this is to change your login’s default database through SQL Script:
ALTER LOGIN [DOMAIN\login]
WITH DEFAULT_DATABASE = master
This will set the default database to your master database and then you will be able to log in and continue from there.