SQL 2005 Database Not Accessible

Question

I've moved a database from sql 2000 to a new 2005 server. Everything was fine until I logged into Sql Server Management studio with one particular user. It looks like i logged in successfully but when I try to expand the database I get 'The database is not accessible'. I can log in with windows authentication and with other sql users with no problem and can see everything.

I checked and made sure the DB is not in single user mode. I also checked for orphaned logins (exec sp_change_users_login 'report') and none were found. The user has rights to the database as well.

Any help in pointing me in the right direction would be greatly appreciated.

score 3 · Answer 1 · answered Jul 01 '09 at 16:21

Most likely it's a permissions problem on the new server. Your SQL logins don't carry over automatically and if you manually create it on the new server it'll get a different SID which won't jive with the one that's granted access to the database.

Best way is to use sp_help_revlogin (KB918992 in case the link ever dies) to generate a script of the logins on the old server and then run the script on the new server. This procedure will carry over SID and password information to the new server effectively recreating the login on the new server. Once this is done the permissions set up in your database should then be in sync with the login(s) on the new server.

This shouldn't be a problem for Windows logins as SQL gets the SID from Windows, although you'll still need to set up the Windows login on the new server. sp_help_revlogin will handle this part for you as well.

score 1 · Answer 2 · answered Jul 01 '09 at 17:04

Thanks for the responses! After mucking around for a while I went and deleted the user from the database and recreated it, which worked! I had deleted the user and recreated it before, but previously I did it on the server level, not the DB level. Once I deleted the user from the DB and recreated it, it worked fine. Thanks again for the help!

score 1 · Answer 3 · answered Jul 28 '09 at 04:33

You won't have an issue with Windows-based logins as they are not associated back to the databases by a SID. This is the case with SQL logins as does cause "orphaning" as a result. Best bet is to use sp_help_revlogin as addressed (proactively) or use sp_change_users_login (reactively). I used the following script I prepared all the time before discovering sp_help_revlogin years ago. This still works:

DECLARE @user SYSNAME
DECLARE @SQL NVARCHAR(300)
DECLARE cur_Users CURSOR FOR
SELECT name
   FROM sysusers
   WHERE islogin = 1
      AND isntname = 0
      AND NAME NOT IN ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA')
   ORDER BY name
OPEN cur_Users
         FETCH NEXT 
   FROM cur_Users INTO @user
WHILE @@FETCH_STATUS = 0
   BEGIN
   SELECT @SQL = 'EXEC sp_change_users_login ' + '''' + 'UPDATE_ONE' 
+ '''' + ', ' + '''' + @user + '''' + ', ' + '''' + @user + ''''
   EXEC sp_executesql @SQL
            FETCH NEXT 
      FROM cur_Users INTO @user
   END
         CLOSE cur_Users
         DEALLOCATE cur_Users

score 1 · Answer 4 · answered Aug 04 '09 at 20:34

One of the things that changed from 2000 to 2005 has to do with a new-in-2005 database-level permission called CONNECT. In SQL Server 2000, being a user in a database also meant that you had access to the database. However, in SQL Server 2005, being a user in a database is not enough by itself to grant a user access to it. Normally you wouldn't even notice this since creating a user in SQL Server 2005, either through DDL or through the GUI, will automatically grant your user that CONNECT permission. Is it possible you lost this during the upgrade somehow?

I can recreate your issue by creating a login and mapping the login to a database, and then going to the database and issuing a REVOKE CONNECT TO username. When I try to access that database through SSMS as username, I get the same error message, The database is not accessible.

If you are certain that the user is not orphaned, I would either go into Permissions page of the Database properties through SSMS and verify that your problem user has a Grant for the CONNECT permission, or I would run the following query:

SELECT pr.name, per.* FROM sys.database_principals AS pr JOIN sys.database_permissions AS per ON pr.principal_id=per.grantee_principal_id WHERE pr.name = 'your username here' AND per.permission_name='CONNECT';

If no rows are returned, then your user needs to have the CONNECT permission added back in order to access the database as expected.

score 0 · Answer 5 · answered Jul 01 '09 at 16:14

Was it an upgrade or did you detach and reattach? If you detach and move the database to another server the users appear in the database, but they don't necessarily get the security hooks. You may need to either reset their permissions on the server, or delete the user and recreate it.

score 0 · Answer 6 · answered Jul 01 '09 at 19:18

You can also use the stored procedure sp_change_users_login to remap the database user to the appropriate login on the new server. That does the trick without deleting the database user. This also preserves the user's permissions, which is really handy if you have set granular permissions on objects in the database. (Note that this doesn't work with Windows-authenticated logins, but I usually don't have a problem with those syncing properly.)

SQL 2005 Database Not Accessible

6 Answers6

Linked