Error 916 When Connecting SQL Server 2008 Management Studio To SQL Server 2005 Express

I installed a SQL Server 2008 Express today on my local machine, and after a successful local installation, I tried to connect (from the SQL Server 2008 Management Studio) to an instance of SQL Server 2005 Express on a network server. It failed when loading the databases list with error 916, indicating that my account didn’t have permission on the Report Services Temp Database.

I resolved this by logging in with Administrator on the server and adding a user mapping for all Domain Users to that database, keeping only Public as permission, so not giving actually any new permissions for the database (my account is a domain user). After that it loaded fine.

Anyway, other databases seemed to have the exact same configuration as the temporary database of Reporting Services 2005 but didn’t fail like that, so I don’t have an explanation for the issue, just the workaround above. To be sure that this doesn’t happen again I added the same user mapping on all other databases on the server so that all domain users can browse the databases themselves without having permission to read/write any tables.

I think this is a bug in the new SQL Server 2008 Management Studio as 2005 Management Studio didn’t have this problem.

Update: I found out that the database with problems had a database state (in Properties/Options) of NORMAL | SHUTDOWN. I found that when a database is in SHUTDOWN state, the problem occurs. To temporarily resolve the issue you just need to issue a query on the database and its state will become NORMAL (without SHUTDOWN) and then SQL Server 2008 Management Studio will browse the databases correctly. But it’s probably only a matter of time until SHUTDOWN state becomes applied again.

Clubhouse Tags: Clubhouse, How-to, SQL Server, Express

About Sorin Dolha

My passion is software development, but I also like physics.
This entry was posted in Computers and Internet. Bookmark the permalink.

4 Responses to Error 916 When Connecting SQL Server 2008 Management Studio To SQL Server 2005 Express

  1. Jan says:

    Did you find out something new? Is this a bug?
    There are some databases visible to all users now even if the users dont have rights to see them. If you take away the rights, SSMS stops working. ­čśŽ

  2. Sorin says:

    No, I didn’t try anymore.

  3. Unknown says:

    Hi,Great blog with interesting informations. I can use it t solve my problem.ThanxM.

Add a reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s