Advertisements

Archive

Posts Tagged ‘Attach/De-Attach Database’

Actual value was -1. (Microsoft.SqlServer.GridControl) Error when attaching database.

April 23, 2010 2 comments

When attaching a database in SQL Server 2008, a user might get the following error.

 TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:

Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)

——————————
BUTTONS:

OK
——————————

The default solution that is listed on the sites [1] & [2] is use a user a user with sysadmin.  It does resolve the issue; but why?

Digging into it a bit, I found when attaching a database SQL Server executes few extended stored procedures.  These stored procedures can only be executed by sysadmin role, therefore when someone with just dbcreator permissions try to attach the database it fails. The actual error is this …

Msg 22001, Level 16, State 1, Line 0
xp_regread() returned error 5, ‘Access is denied.’

So I decided to dive into books online to confirm I am not getting forgetful, Books Online state for sp_attach_db “sysadmin and dbcreator fixed server roles can execute this procedure’.So I decided to execute following command:

EXEC sp_attach_db    @dbname = 'DBName',
                     @filename1='D:\MSSQL10.MSSQLServer\MSSQL\Data\DBName.mdf',
                     @filename2='E:\MSSQL10.MSSQLServer\MSSQL\LOG\DBName_log.ldf'

It attached successfully, so it seems like an issue in GUI only.

I have tried to look for KB article to see if there is a fix for this; also on Microsoft connect with no luck.  So I have a Microsoft Connect article here.

References
[1] How to change SQL Server login default database through SQL Script. Jeff Widmer’s Blog. Link.
[2] Unable to attach database using Management Studio. MSDN Forums Online. Link.

Advertisements
%d bloggers like this: