Migrate the Correct Logins with a SQL Server Database

By: K. Brian Kelley   |   Comments   |   Related: > Security

Problem

I am in the process of migrating a database and I'm seeing a lot of logins on the SQL Server and quite a few users within the database. Obviously, I don't want to miss any of these that are relevant to the database. However, I don't want to migrate any logins that aren't used by the database. How do I do this quickly?

Solution

First, you're going to need sp_help_revlogin, which is provided in Microsoft KB article 918992. This stored procedure extracts the key information, including the encrypted password if working with a SQL Server-based login.

When migrating the database, we'll have several cases to deal with:

• Users mapped to Windows users.
• Users mapped to Windows groups.
• Users mapped to SQL Server logins.
• Users mapped to a login, but the names aren't the same.
• Users formerly mapped to a login, but the login has been deleted at the server level.
• Users not mapped to SQL Server logins at all.

The last two will cause us to want to provide a warning. We'll get to this shortly when we put together a migration script. First, let's set up an example database with all of these cases.

Sample SQL Server Database

First, let's set up the database:

USE master; 
GO 

-- Drop database if it already exists
IF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = 'TestLoginMigration')
  DROP DATABASE TestLoginMigration;
GO 

-- Create our test database
CREATE DATABASE TestLoginMigration;
GO 

Next we'll create the logins we need. Note that we have a mix of Windows and SQL Server based logins.

-- Ensure that we have at least one Windows login to transfer
IF NOT EXISTS(SELECT [name] FROM sys.server_principals 
      WHERE [name] = 'NT Authority\Authenticated Users')
  CREATE LOGIN [NT Authority\Authenticated Users] FROM WINDOWS;
GO 

-- We're going to intentionally create a situation where the DB has a user
-- that used to be mapped to a login but the login doesn't exist, an
-- orphaned login
IF EXISTS (SELECT [name] FROM sys.sql_logins WHERE [name] = 'LoginToBeDropped')
  DROP LOGIN [LoginToBeDropped];
GO 

CREATE LOGIN [LoginToBeDropped] WITH PASSWORD = 'SomeComplexPassword0987654321!';
GO 

-- Now to create a login we're going to maintain
IF EXISTS (SELECT [name] FROM sys.sql_logins WHERE [name] = 'LoginToTransfer')
  DROP LOGIN [LoginToBeTransferred];
GO 

CREATE LOGIN [LoginToBeTransferred] WITH PASSWORD = 'SomeComplexPassword0987654321!';
GO 

-- Finally, a login whose user name doesn't match up
IF EXISTS (SELECT [name] FROM sys.sql_logins WHERE [name] = 'MismatchedName')
  DROP LOGIN [MisMatchedName];
GO 

CREATE LOGIN [MisMatchedName] WITH PASSWORD = 'SomeComplexPassword0987654321!';
GO

Finally, we'll map the logins to users at the database level. Do note that as soon as we do this, we're going to intentionally orphan a login.

-- Add logins to the database
USE TestLoginMigration;
GO 

CREATE USER [NT Authority\Authenticated Users];
GO 

CREATE USER [LoginToBeDropped];
GO 

CREATE USER [LoginToBeTransferred];
GO 

-- Now for the mismatched login / username
CREATE USER [NotTheLoginName] FROM LOGIN [MisMatchedName];
GO 

-- Create an exception, a user that has only ever existed at the DB level.
CREATE USER [UserWithoutLogin] WITHOUT LOGIN;
GO 

-- Create the orphaned login
USE master;
GO 

DROP LOGIN [LoginToBeDropped];
GO

With our example database setup, let's look at our edge cases, users without logins and users where the login name doesn't match. To do this from SSMS, expand the database tree you are working with and go Security > Users.  Right click on one of the users and select Properties.

First, let's look at the case where a user doesn't map to a login. If we right click on LoginToBeDropped and select Properties, note how SSMS shows it on the General page "SQL user without login".

Compare this to a login that does map correctly.  If we right click on the user LoginToBeTransferred and select Properties, we see this as "SQL user with login".

Finally, look at the case of a name mismatch. Here we are selecting the user NotTheLoginName. Note that the login is included and we can clearly see the user and login mismatch.

Note: While such a mismatch is obviously permitted by SQL Server, it's not a good idea. There's a general principle called the KISS principle. KISS stands for, "Keep it simple, stupid!" The acronym and principle were developed by the US Navy decades ago. The point that it makes is when we make things more complex than they have to be, we're asking for trouble. Such is the case when you have a user and login name mismatch. At some point, this is going to lead to confusion. Therefore, avoid doing this whenever possible.

Reporting Mapping Issues

With our example database in place, let's report on the cases where a user doesn't map to a login. If the application is functioning properly then these reported users are probably okay to do without logins. However, in the event that they are necessary, we are providing the users that don't map correctly:

-- Execute with the results in text mode

-- Warn about Users without Login Matches
-- Excluding roles and "system" users
PRINT 'The following users don't map to logins at the server level.';
PRINT ' ';

SELECT U.name, U.sid, U.create_date, U.type
FROM sys.database_principals AS U
  LEFT JOIN sys.server_principals AS L
    ON U.sid = L.sid
WHERE L.sid IS NULL
  AND U.type IN ('S', 'U', 'G')
  AND U.name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
ORDER BY U.name;

And, if you've got the results showing in Text mode, you should see something like so:

Extracting the Appropriate Logins for the Database

Now let's extract the logins that are mapping to users in the database. Even in the case of the mismatched name, we're okay, because the mapping is done by the sid (security identifier). We'll use sp_help_revlogin to extract the login information and generate the CREATE LOGIN statement.

-- Generate the login information to migrate 
PRINT ' ';
PRINT 'Logins to migrate:';
PRINT ' ';

DECLARE cursLogins CURSOR FAST_FORWARD
FOR
SELECT L.[name]
FROM sys.database_principals AS U
  JOIN sys.server_principals  AS L
    ON U.sid = L.sid
WHERE U.type IN ('S', 'U', 'G')
  AND NOT U.name = 'dbo'
ORDER BY L.name;
GO 

DECLARE @login sysname;
DECLARE @SQL NVARCHAR(MAX);
OPEN cursLogins;

FETCH NEXT FROM cursLogins INTO @login;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  SET @SQL = 'EXEC master..sp_help_revlogin ''' + @login + ''';';
  EXEC (@SQL);

  FETCH NEXT FROM cursLogins INTO @login;
END;

CLOSE cursLogins;
DEALLOCATE cursLogins;

And here's what the output will look like. Now you have what you need to perform the migration.

Next Steps

• Learn how to script all logins for a disaster recovery situation.
• Read how to clone a login (Part 1 of 3).
• Identify orphaned logins/users in your database.
• Understand how users get access to a database.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips