Here is a little quickie for a Friday afternoon , one for the back pocket maybe?

Here is a scenario that you may have been faced with before, I am pretty sure we all have at some point but today I want to show a neat little way to get around this situation which does not involve dropping any users and/or logins and recreating.

Dude Where’s my access? I have a Database User and I am using the correct password but I cannot access the database , I need to access the Database ASAP……We have all heard this before right? well maybe not with those choice of words but you catch my drift…

First lets check that the User Does actually exist, we know the Server login exists otherwise the user would be complaining that they cannot connect to the SQL server instance.

Sure enough – there is the user ‘SQLUndercoverUser’ lets check out the permissions:

No problems there – the user has [db_datareader], [db_datawriter] and [db_owner] so we know there is not a permissions issue, so lets test this login by connecting to SQL server with the user credentials:

Connected to the Server with no issues, lets open a new query against SQLUnderCoverDB:

Hmm so despite having permissions to access the database we are receiving this error – we know that the password is correct too otherwise we wouldn’t be able to access the Server at all….

Lets have a look at what SQL is seeing behind the scenes:

Database SQL Authenticated User: SQLUndercoverUser

SQL Authenticated Server Login: SQLUndercoverUser

We have matching names however SQL is interested in the SID (Security ID) for the User/Login, this is what it uses to determine whether a Database User is associated with a Server login and vice versa.

As we can see, SQL Server has fixed the Orphaned used by updating the SID of the database user to the Server login, a quick query against sys.Database_Principles and sys.Server_Principles will show that they do indeed match:

And now the database user has the access to the database as expected, the most common causes that I have come across is when a database is restored from another server where the Server login SID does not match that of the destination server or when the the databases are synchronized in an Availability Group and the Secondary server/s have had their Server Logins created manually rather than via scripting the login from the primary and carrying over the SID then when the AG is failed over to run as Primary on the Secondary the database user no longer has access.

Hope you find this little tip useful, its now Saturday morning rather than Friday but what can I say – its been one of them days 🙂

David Fowler

David is a DBA with over 14 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK.

Adrian Buckman

After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 4 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies