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.
Fix:
-
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 bingo!