The server principal is not able to access the database under the current security context in SQL Server MS 2012

The server principal is not able to access the database under the current security context in SQL Server MS 2012

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 users 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

The server principal is not able to access the database under the current security context in SQL Server MS 2012

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 login entry. This will bring up the Login Properties dialog.

Select User Mapping, which will show all databases on the server. Those which already have a user mapped to that login will have have the Map checkbox 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.

Note that, while its common practice to name the Users the same as the Login to avoid confusion, they dont have to match and you can name the User whatever youd like.

enter

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.

Leave a Reply

Your email address will not be published.