the server principal is not able to access the database under the current security context.

Question

Problem :

I want to access my hosting server’s database through SQL Server Management Studio, everything is fine but when I use the following command use myDatabase it gives me following error:

The server principal "****" is not able to access the database "****" under the current security context.

Can anyone give me solution on my problem?

Answer 1

We had faced the same issue while deploying our report to SSRS in our PROD environment. We found that the problem could be reproduced with a event the “use ” statement. And the solution which we found is to resync the user's GUID account reference with the database in question we used "sp_change_users_login" after restoring a db. Please find below a cursor driven script to resync all accounts:

USE <your database name>

GO

-------- Reseting SQL user account guids --------------------

DECLARE @UserName nvarchar(255) 

DECLARE orphanuser_cur cursor for 

      SELECT UserName = su.name 

      FROM sysusers su

      JOIN sys.server_principals sp ON sp.name = su.name

      WHERE issqluser = 1 AND

            (su.sid IS NOT NULL AND su.sid <> 0x0) AND

            suser_sname(su.sid) is null 

      ORDER BY su.name 

OPEN orphanuser_cur 

FETCH NEXT FROM orphanuser_cur INTO @UserName 

WHILE (@@fetch_status = 0)

BEGIN 

--PRINTING @UserName + ' user name being resynced' 

exec sp_change_users_login 'Update_one', @UserName, @UserName 

FETCH NEXT FROM orphanuser_cur INTO @UserName 

END 

CLOSE orphanuser_cur 

DEALLOCATE orphanuser_cur

Answer 2

Check to see if your user is mapped to the DB you are trying to log into.

We had the same error deploying a report to SSRS in our PROD environment. It was found the problem could even be reproduced with a “use ” statement. The solution was to re-sync the user's GUID account reference with the database in question (i.e., using "sp_change_users_login" like you would after restoring a db). A stock (cursor driven) script to re-sync all accounts is attached:

USE <your database>
GO

-------- Reset SQL user account guids ---------------------
DECLARE @UserName nvarchar(255) 
DECLARE orphanuser_cur cursor for 
      SELECT UserName = su.name 
      FROM sysusers su
      JOIN sys.server_principals sp ON sp.name = su.name
      WHERE issqluser = 1 AND
            (su.sid IS NOT NULL AND su.sid <> 0x0) AND
            suser_sname(su.sid) is null 
      ORDER BY su.name 

OPEN orphanuser_cur 
FETCH NEXT FROM orphanuser_cur INTO @UserName 

WHILE (@@fetch_status = 0)
BEGIN 
--PRINT @UserName + ' user name being resynced' 
exec sp_change_users_login 'Update_one', @UserName, @UserName 
FETCH NEXT FROM orphanuser_cur INTO @UserName 
END 

CLOSE orphanuser_cur 
DEALLOCATE orphanuser_cur

I spent quite a while wrestling with this problem and then I realized I was making a simple mistake in the fact that I had forgotten which particular database I was targeting my connection to. I was using the standard SQL Server connection window to enter the credentials:

I had to check the Connection Properties tab to verify that I was choosing the correct database to connect to. I had accidentally left the Connect to database option here set to a selection from a previous session. This is why I was unable to connect to the database I thought I was trying to connect to.

Note that you need to click the Options >> button in order for the Connection Properties and other tabs to show up.

This worked for me:

use <Database>
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='<userLogin>',@LoginName='<userLogin>';

The problem can be visualized with:

SELECT sid FROM sys.sysusers WHERE name = '<userLogin>'
SELECT sid FROM sys.syslogins WHERE name = '<userLogin>';

SQL Logins are defined at the server level, and must be mapped to Users in specific databases.

In SSMS object explorer, under the server you want to modify, expand Security > Logins, then double-click the appropriate user which will bring up the "Login Properties" dialog.

Select User Mapping, which will show all databases on the server, with the ones having an existing mapping selected. From here you can select additional databases (and be sure to select which roles in each database that user should belong to), then click OK to add the mappings.

These mappings can become disconnected after a restore or similar operation. In this case, the user may still exist in the database but is not actually mapped to a login. If that happens, you can run the following to restore the login:

USE {database};
ALTER USER {user} WITH login = {login}

You can also delete the DB user and recreate it from the Login Properties dialog, but any role memberships or other settings would need to be recreated.

In my case, the message was caused by a synonym which inadvertently included the database name in the "object name". When I restored the database under a new name, the synonym still pointed to the old DB name. Since the user did not have permissions in the old DB, the message appeared. To fix, I dropped and recreated the synonym without qualifying the object name with the database name:

    USE [new_db]
GO

/****** Object:  Synonym [dbo].[synTable]    Script Date: 10/15/2015 9:45:01 AM ******/
DROP SYNONYM [dbo].[synTable]
GO

/****** Object:  Synonym [dbo].[synTable]    Script Date: 10/15/2015 9:45:01 AM ******/
CREATE SYNONYM [dbo].[synTable] FOR [dbo].[tTheRealTable]
GO