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.
We can see this by running a query like this:
SELECT DatabaseUser.[name], DatabaseUser.[sid] AS [DatabaseSID], ServerLogin.[sid] AS [ServerSID] FROM sys.database_principals DatabaseUser INNER JOIN sys.server_principals ServerLogin ON DatabaseUser.[name] = ServerLogin.[name] WHERE DatabaseUser.[type_desc] != 'DATABASE_ROLE' AND DatabaseUser.[sid] <> ServerLogin.[sid]
As you can see from the above results these SIDs do not match, we can also use the following command within the context of the database:
USE [SQLUndercoverDB] GO EXEC SP_CHANGE_USERS_LOGIN 'REPORT' GO
so how can we solve this issue?
We make the SIDs match one another, as we can achieve this by running the following statement within the context of the database:
USE [SQLUndercoverDB] GO EXEC SP_CHANGE_USERS_LOGIN 'Auto_Fix','SQLUndercoverUser' GO
And here is the result:
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:
USE [SQLUndercoverDB] GO SELECT DatabaseUsers.[Name] AS DatabaseUser, DatabaseUsers.[sid] as DatabaseUser_SID, Serverlogins.[sid] as ServerLogin_SID FROM sys.database_principals DatabaseUsers INNER JOIN sys.server_principals ServerLogins ON DatabaseUsers.[Name] = ServerLogins.[name] WHERE DatabaseUsers.[Name] = 'SQLUndercoverUser'
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 🙂