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

Question

I am trying to access my hosting server   s database through SQL Server Management Studio  everything till login is fine but when I use the command use myDatabase it gives me this error   The server principal        is not able to access the database        under the current security context    I searched over and the hosting service providers has listed this fix for the problem   But this is not working for me probably because it s for SQL Server Management Studio 2008 however I am using SQL Server Management Studio 2012   Can this be a problem  And if yes than can anyone tell me its alternative in SSMS 2012

User · Answer

This worked for me   use  lt Database gt  EXEC  sp change users login  Action  update one    UserNamePattern   lt userLogin gt    LoginName   lt userLogin gt      The problem can be visualized with   SELECT sid FROM sys sysusers WHERE name     lt userLogin gt   SELECT sid FROM sys syslogins WHERE name     lt userLogin gt

User · Answer

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

User · Answer

On SQL 2017 - Database A has synonyms to Database B  User can connect to database A and has exec rights to an sp  on A  that refers to the synonyms that point to B  User was set up with connect access B  Only when granting CONNECT to the public group to database B did the sp on A work  I don t recall this working this way on 2012 as granting connect to the user only seemed to work

User · Answer

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

User · Answer

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

User · Answer

We had the same error even though the user was properly mapped to the login     After trying to delete the user it was discovered that a few SPs contained  with execute as  that user   The issue was solved by dropping those SPs  dropping the user  recreating the user linked to login  and recreating the SPs   Possibly it got in this state from restoring from backup  during a time when the related login didn t exist  or bulk schema syncing  if its possible to create an SP with execute as even though the user doesn t exist   Could also have been related to this answer

User · Answer

I encountered the same error while using Server Management Objects  SMO  in vb net  I m sure it s the same in C    Techie Joe s comment on the initial post was a useful warning that in shared hosting a lot of additional things are going on   It took a little time to figure out  but the code below shows how one has to be very specific in the way they access SQL databases   The  server principal     error seemed to show up whenever the SMO calls were not precisely specific in the shared hosting environment   This first section of code was against a local SQL Express server and relied on simple Windows Authentication   All the code used in these samples are based on the SMO tutorial by Robert Kanasz in this Code Project website article     Dim conn2   New ServerConnection     conn2 ServerInstance     lt local pc name gt  SQLEXPRESS    Try     Dim testConnection As New Server conn2      Debug WriteLine  Server      testConnection Name      Debug WriteLine  Edition      testConnection Information Edition      Debug WriteLine           For Each db2 As Database In testConnection Databases       Debug Write db2 Name  amp    -          For Each fg As FileGroup In db2 FileGroups         Debug Write fg Name  amp    -            For Each df As DataFile In fg Files           Debug WriteLine df Name     -     df FileName          Next       Next     Next     conn2 Disconnect      Catch err As Exception     Debug WriteLine err Message    End Try   The code above finds the  mdf files for every database on the local SQLEXPRESS server just fine because authentication is handled by Windows and it is broad across all the databases   In the following code there are 2 sections iterating for the  mdf files   In this case only the first iteration looking for a filegroup works  and it only finds a single file because the connection is to only a single database in the shared hosting environment   The second iteration  which is a copy of the iteration that worked above  chokes immediately because the way it is written it tries to access the 1st database in the shared environment  which is not the one to which the User ID Password apply  so the SQL server returns an authorization error in the form of the  server principal     error   Dim sqlConnection1 As New System Data SqlClient SqlConnection sqlConnection1 ConnectionString    connection string with User ID Password to a specific database in a shared hosting system  This string will likely also include the Data Source and Initial Catalog parameters  Dim conn1 As New ServerConnection sqlConnection1  Try   Dim testConnection As New Server conn1    Debug WriteLine  Server      testConnection Name    Debug WriteLine  Edition      testConnection Information Edition    Debug WriteLine         Dim db2   testConnection Databases  the name of the database to which the User ID Password in the connection string applies     For Each fg As FileGroup In db2 FileGroups     Debug Write fg Name  amp    -        For Each df As DataFile In fg Files       Debug WriteLine df Name     -     df FileName      Next   Next    For Each db3 As Database In testConnection Databases     Debug Write db3 Name  amp    -        For Each fg As FileGroup In db3 FileGroups       Debug Write fg Name  amp    -          For Each df As DataFile In fg Files         Debug WriteLine df Name     -     df FileName        Next     Next   Next    conn1 Disconnect    Catch err As Exception   Debug WriteLine err Message  End Try   In that second iteration loop  the code compiles fine  but because SMO wasn t setup to access precisely the correct database with the precise syntax  that attempt fails   As I m just learning SMO I thought other newbies might appreciate knowing there s also a more simple explanation for this error - we just coded it wrong

User · Answer

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  lt your database gt  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  lt  gt  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

User · Answer

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  gt  gt  button in order for the Connection Properties and other tabs to show up

User · Answer

I believe you might be missing a  Grant Connect To  statement when you created the database user   Below is the complete snippet you will need to create both a login against the SQL Server DBMS as well as a user against the database  USE  master  GO  CREATE LOGIN  SqlServerLogin  WITH PASSWORD N Passwordxyz   DEFAULT DATABASE  master   CHECK EXPIRATION OFF  CHECK POLICY ON GO  USE  myDatabase  GO  CREATE USER  DatabaseUser  FOR LOGIN  SqlServerLogin  WITH DEFAULT SCHEMA  mySchema  GO  GRANT CONNECT TO  DatabaseUser  GO  -- the role membership below will allow you to run a test  select  query against the tables in your database ALTER ROLE  db datareader  ADD MEMBER  DatabaseUser  GO

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

Examples related to sql-server

Examples related to sql-server-2012