Post reply

SQL Error The server principal '' is not able to access the database '' under the current security context

  • October 26, 2012 at 6:28 am

    #270200

    I am getting the above error after moving a database to a new server. The strange thing is that I created a new SQL account to connect to the database and gave it access but in the error message above the database listed is one that it does not have access to and should not have access to. I tried running sp_change_users_login 'Auto_fix' against the database but it said it found 0 orphaned users. I'm not sure why SQL thinks the user is trying to connect to this database. Anybody come across this before?

  • October 26, 2012 at 1:05 pm

    #1553357

    A few things to look at:

    - How is the new SQL account trying to connect to the database?

    - Can you connect the client to the database using SSMS?

    - Is the login's default database set correctly, and does it have a corresponding user in the database you are trying to connect to?

    - Is the authentication mode on the new instance set to SQL Server and Windows authentication?

    Joie Andrew
    "Since 1982"

  • October 26, 2012 at 1:19 pm

    #1553363

    Specify a default database that you know the login has access to. Joie has some good questions that need answering as well

  • October 26, 2012 at 1:26 pm

    #1553366

    when you restore a database on a different server, the users in the database become Orphaned, because the SID inside the database is not the SID that exists for the login;

    this should help you fix the users...simply run this on that database to see if the users are disconnected:

    SELECT

    CASE

    WHEN svloginz.name is not null and dbloginz.sid <> svloginz.sid

    THEN '--Login Exists but wrong sid: remap!

    ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(svloginz.name) + ';'

    ELSE 'CREATE LOGIN ' + quotename(dbloginz.name) + ' WITH PASSWORD=N''NotARealPassword'' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;

    ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(dbloginz.name) + ';'

    END

    from sys.database_principals dbloginz

    LEFT OUTER JOIN sys.server_principals svloginz

    on dbloginz.name = svloginz.name

    WHERE dbloginz.type IN ('S','U')

    AND dbloginz.name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply