One Database Fails

Grant Fritchey

I'm running 3.8 pro (although I had the same error in the previous version). When I try to connect to one database, and so far only this one database, I get an error stating that SQL Prompt can't process the schema. Here's the detailed error:


RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException ---> System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
at k.v(SqlConnection )
at k.a(j )
at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt(ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects)
at cy.b(IAuthenticationTokenProvider )
--- End of inner exception stack trace ---
at cy.b(IAuthenticationTokenProvider )
at cy.a(IAuthenticationTokenProvider )
at cy.GetMetaData(IAuthenticationTokenProvider provider)
at M.b(G , IAuthenticationTokenProvider , Boolean , EventHandler`1 , EventHandler`1 , EventHandler`1 , EventHandler`1 )

chris.buckingham

There is a possibility is that you have insufficient access rights on the database. On SQL Server 2000 you need dbo access, on SQL Server 2005 and 2008, you don’t but you do need some additional permissions.

For anyone out there working with SQL Server on a large or mission critical system it's quite likely that, unless you're the DBA, you're not going to have dbo access to the databases you're working with, particularly if you're working directly with the production servers. Unsurprisingly then we've had a number of complaints from people finding themselves in exactly this situation who want to be able to use SQL Prompt 3, but can't because it generally doesn't work unless you're dbo.

Fortunately my colleague Jon this morning let me know that a solution is at hand for SQL Server 2005 at least, although you're still going to have to be nice to your DBA to make this work (I'd suggest that beer often helps). What you need to do is ask him or her to grant you the VIEW DEFINITION permission on the database of interest. What this means is that you'll be able to see all the meta-data for any object defined in the database, but you won't have access to the objects themselves, so for example you won't be able to read any data from a table unless you have the permissions required to do so.

For example, say your username is "bill" and you need access to the AdventureWorks database, then your DBA would need to execute the following command:

GRANT VIEW DEFINITION ON Database::AdventureWorks TO bill

(Please see our developer's blog post for the full article:
http://www.simple-talk.com/community/bl ... 19421.aspx)

chris.buckingham

Postscript: Customer found that GRANT VIEW DEFINITION was the problem and it now works as expected.