I came across a problem when using Microsoft SQL Server Management Studio 2008 to connect and manage a database which is housed on my shared host’s SQL Server 2005, the problem was that I couldn’t actually access my database, as I clicked the “Database” node within “Object Explorer” I received the message:
The server principal “username” is not able to access the database “databasename” under the current security context. (Microsoft SQL Server, Error: 916)
Anyway, after an hour, I decided that I would try and access my database within Microsoft Visual Studio 2008, using the “Server Explorer”. I set myself up with a connection to the database server, and hooray, I could connect and access everything. That left me wondering why it wasn’t working in the Management Studio, so I spent some more time trawling the net for an answer (since I’m hardly a SQL Server guru!). I eventually found something to help me out…
When I clicked on the “Databases” node within SQL Server Management Studio, the software would (in the background) execute an T-SQL query to retrieve a list of databases along with additional information about those databases, one of those pieces of information is collation, which I didn’t have permission to do for every database, only my own.
- In Object Explorer, click Databases
- Display “Object Explorer Details” (F7) or “View –> Object Explorer Details”
- Right click the column headers and deselect “collation”
- Refresh Databases, and hopefully solved