DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Using sp_change_users_login to fix SQL Server orphaned users

Posted on September 19, 2012 Written by Andy Hayes 16 Comments

In this post, I’m going to be looking at sp_change_users_login in order to fix SQL Server orphaned users as a continuation to a previous article. There I looked at a couple of ways to transfer logins from one SQL Server to another and touched upon the issue of the orphaned “security identifier” (SID).

A typical scenario that arises is when the DBA quickly realises that the logins on the SQL Server cannot access the database. They try and add the login to the database as a user and are presented with the error:

Error 15023: User already exists in current database.

The root cause of this problem can be when a login is deleted or the database has been moved or restored to another server but the login transported in the database does not exist on the destination server.

Through SQL Server Management Studio, the database user properties will show the User type: as SQL user without login

sql user without login

sp_change_users_login to the rescue!

I first saw this error a number of years ago and due to my complete lack of experience at the time, one of my first thoughts was that I would have to remove the database users, re-add them all for each login requiring access and then proceed to add the permissions back in for user.

I quickly realised that this would be a massive waste of my time and that there had to a better way and so I proceeded to consult the search engines for a resolution. Unsurprisingly I quickly found many other people who had been in the same situation as me and that sp_change_users_login had been the cure to all their woes.

And here I am writing a post about it 🙂 Well I never would have imagined that but it was a long time ago and only clever people could put a website together back in those days when blogging platforms were a twinkle in some programmers eye.

How to use sp_change_users_login to fix SQL Server orphaned users

The best thing to do is run the following code against each database you are checking. Run this against each database. It will help you to find all the orphaned logins in your database.

USE DatabaseName
EXEC sp_change_users_login 'Report';

You will see output like the screenshot attached if there are any sql orphaned users. In this example, user “db_login1” is showing up as an orphaned user.

sp_change_users_login report

If you already have a login which you want to map your database user to, you could run the following (note that the first instance of ‘db_login1’ is the user in the database, the second instance is the login to be mapped to) :

EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';

If you don’t already have a login to map to, you can have sp_change_users_login create one for you and with a password. The following code does this and creates a login with the same name and a password of ‘aaZZww77’ as an example.

EXEC sp_change_users_login 'Auto_Fix', 'db_login1', NULL, 'aaZZww77';

sp_change_users_login auto_fix

UPDATE – 3rd Apr 2018

An alternate way to detect orphaned users in SQL Server or PDW is by running this code for each database:


SELECT dp.type_desc, dp.SID, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
WHERE sp.SID IS NULL AND authentication_type_desc = 'INSTANCE';


This is an example output:

detecting orphaned users in sql server using sp_change_users_login

For identifying orphaned users in Azure SQL Database and SQL Data Warehouse run this code in the master database:

SELECT sid FROM sys.sql_logins WHERE type = 'S';

Now run this code in each database:

SELECT name, sid, principal_id
FROM sys.database_principals
WHERE type = 'S'
AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
AND authentication_type_desc = 'INSTANCE';

Compare the two lists to see if there are user SID’s in sys.database_principals which are not found in sys.sql_logins

Fix SQL Orphaned Users Using CREATE LOGIN

You can take the SID’s identified in the previous section and use them as part of the CREATE LOGIN statement, example:

CREATE LOGIN db_login_1
WITH PASSWORD = 'use_a_strong_password_here',
SID = 0xB171D3B5A352A846847342C5E46620BA;

If you’re mapping an orphaned user to a login which already exists in master, run this:

ALTER USER <user_name> WITH Login = <login_name>;

For more info on how to fix orphaned users, click this sp_change_users_login link  and this one on troubleshooting orphaned users to view the documentation from Microsoft.

Filed Under: All Articles, SQL Server Administration, SQL Server Security Tagged With: security, sql server

How to Transfer Logins to Another SQL Server or Instance

Posted on September 15, 2012 Written by Andy Hayes 9 Comments

With any server migration, ideally you want things to run smoothly and re-creating SQL Server logins and passwords from scratch is not something you really want to or should be doing.

I am currently involved in a complete server replacement for one of our SQL Server clusters. In this environment, mixed authentication is enabled.

Back in the day when I was learning about transferring databases from one server, I quickly discovered that restoring a database from one server to another was not as straightforward as I had anticipated. I came across a hurdle which I needed to overcome in the form of the SID’s.

So first I’ll talk about the ways in which you can transfer logins from one SQL Server to another and explain about the SID’s further down the post.

Ways to transfer SQL logins and passwords between instances of SQL Server

Now you can do this the easy way or the hard way! 🙂

You can manually set up copies of your logins on your new server with passwords (if you know them) or you can automate this task with the help of either a T-SQL script or SSIS.

I don’t know about you but I don’t like manual and I would recommend that you automate this each time.

Using T-SQL to transfer logins and passwords between SQL Server instances

You can obtain a script from Microsoft here. You run the script, it creates a stored procedure called sp_help_revlogin on your SQL Server. When you run the stored procedure, it outputs CREATE LOGIN statements for all your server logins including their passwords and “sids”.

You can then take that script, run it on your new SQL Server to set up the server logins there. Obviously taking care to look over it before you execute it to make sure that it has captured everything and to ensure that you are not copying over anything that you do not need on your new instance.

The script will set up the logins on your new server in using the default language configured for that server so make sure that is configured correctly first.

Using the transfer logins task in SSIS to copy logins and passwords between SQL Server instances

There is a transfer logins task within SSIS which will also do the job. In the screenshot attached, I am using the new version of SSIS which ships with SQL Server 2012. The transfer logins task falls under “other tasks” in the the SSIS toolbox and is available in older versions of SSIS.

transfer logins between sql server servers or instances

So hopefully you can see from the screenshot that the transfer logins task is fairly self explanatory. The great thing about SSIS is that it holds your hand in many ways and makes things as easy as possible.

You can create connections from this task, enter the login details for your source and destination, choose which logins to copy and you can configure the task to behave in different ways when the login exists.

As you can see I have circled “CopySids” so now is a good time to write about them in more detail.

So what is a SID?

SID is short for “security identifier” A SID is an internal id which gets assigned to a server login when the login is created.

The SID can be viewed by querying the sys.server_principals system view or you can also view it by looking at sys.syslogins

SELECT principal_id, sid, name
FROM sys.server_principals
SELECT sid, name
FROM sys.syslogins

Each server login, be that a SQL login, Windows login or one which was created using a certificate or asymmetric key, gets assigned a SID. So lets imagine an example scenario where a new login is created and it happens to be a SQL Server login, that login gets named “server_login1”.

You then create a database login inside the AdventureWorks2012 database using the server login just created. Lets call that “db_login1”.

Now view the SID’s in sys.server_principals and sys.database_principals from AdventureWorks2012 and the SID will be of the same value.

SELECT sid
FROM sys.server_principals
WHERE name = 'server_login1'

USE AdventureWorks2012
GO
SELECT sid
FROM sys.database_principals
WHERE name = 'db_login1'

Unless you create your logins on your new server using a method such as the sp_help_revlogin or the SSIS transfer logins task with “CopySids” enabled, your logins will be created with new SIDs.

I am not sure whether it is possible that your new server will ever assign the same SID but I do know that it is very unlikely. Perhaps you have more chance of winning the lottery jackpot 🙂

So what problems does this cause?

As the SIDs are different, when you come to copy across/restore your databases from your old server, the database logins simply won’t work as they will be orphaned from their original SID.

You may encounter this error for example if you try and add a server login to a database which you have just copied across.

Error 15023: User already exists in current database

This will then need to be corrected.

There is a procedure that you can run in order to do this and it basically takes the server login and database login and marries them up by name with the same SID.

This procedure is called sp_change_users_login and I’m going to talk about that in this post.

Filed Under: All Articles, SQL Server Administration, SQL Server Security Tagged With: security, sql server

Auditing SQL Server Logins

Posted on June 30, 2012 Written by Andy Hayes 1 Comment

Auditing SQL Server logins is done by way of writing audit information to the SQL Server Logs found under “Management->SQL Server Logs” in Management Studio.

In order to enable auditing of SQL Server logins, a simple operation needs to be performed by changing settings in the properties of the SQL Server.

Auditing levels vary. It’s possible to audit just failed logins, both failed and successful logins or just successful logins.

To enable auditing, in Management Studio right mouse click your SQL Server and choose “Properties”. Under “Select a page”, choose “Security” and the following options will be displayed.

auditing sql server logins

A restart of the SQL Server service is needed for any changes to the SQL Server login auditing to take effect.

After the restart, the server will being auditing all login activity based on the setting chosen.

Please be aware that this can create very large log files in your log directory should you have a busy server with lots of connection activity and if you have enabled logging to log successful logins. Logging will also be recorded in your Windows application event log.

I wrote this post recently on how you can quickly reclaim space consumed by your sql server error logs using sp_cycle_errorlog

The resulting output found in the SQL Server logs will look similar to this

2012-06-30 10:18:19.44 Logon Login succeeded for user ‘NT ServiceMSSQL$SQLSERVER2012’. Connection made using Windows authentication. [CLIENT: ]
2012-06-30 10:18:19.57 Logon Login succeeded for user ‘NT ServiceMSSQL$SQLSERVER2012’. Connection made using Windows authentication. [CLIENT: ]
2012-06-30 10:28:14.29 Logon Error: 18456, Severity: 14, State: 8.
2012-06-30 10:28:14.29 Logon Login failed for user ‘andy’. Reason: Password did not match that for the login provided. [CLIENT:]
2012-06-30 10:43:18.98 Logon       Login failed for user ‘test’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

Why would I audit SQL Server logins?

I typically use it to help figure out why connections to the database server are failing. If your application is having trouble connecting to your database server then this login information is great to have because it helps to tell you how far along in the authentication process the connection is.

If no entry for the user connecting is recorded then the application is not even getting as far as the database server. If output similar to the last two entries is seen then the error is detailed enough to help you easily figure out why there is a connection issue.

You may also be having some other security problem so these logs are very useful tool to the DBA to help troubleshoot problems with SQL Server security.

 

Filed Under: All Articles, SQL Server Security Tagged With: security, sql server

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close