By: K. Brian Kelley | Updated: 2016-04-28 | Comments | Related: More > Security
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?
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.
- 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.
Last Updated: 2016-04-28
About the author
View all my tips