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

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 = "<local pc name>\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 & " - ")
      For Each fg As FileGroup In db2.FileGroups
        Debug.Write(fg.Name & " - ")
        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 & " - ")
    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 & " - ")
    For Each fg As FileGroup In db3.FileGroups
      Debug.Write(fg.Name & " - ")
      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

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

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

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

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

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

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

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

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

[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