Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Converts a database user that is mapped to a SQL Server login, to a contained database user with password. In a contained database, use this procedure to remove dependencies on the instance of SQL Server where the database is installed. sp_migrate_user_to_contained separates the user from the original SQL Server login, so that settings such as password and default language can be administered separately for the contained database. sp_migrate_user_to_contained can be used before moving the contained database to a different instance of the SQL Server Database Engine to eliminate dependencies on the current SQL Server instance logins.
Note This procedure is only used in a contained database. For more information, see Contained Databases.
Applies to: SQL Server (SQL Server 2012 through current version).
sp_migrate_user_to_contained creates the contained database user with password, regardless of the properties or permissions of the login. For example, the procedure can succeed if the login is disabled or if the user is denied the CONNECT permission to the database.
sp_migrate_user_to_contained has the following restrictions.
The user name cannot already exist in the database.
Built-in users, for example dbo and guest, cannot be converted.
The user cannot be specified in the EXECUTE AS clause of a signed stored procedure.
The user cannot own a stored procedure that includes the EXECUTE AS OWNER clause.
sp_migrate_user_to_contained cannot be used in a system database.
When migrating users, be careful not to disable or delete all the administrator logins from the instance of SQL Server. If all logins are deleted, see Connect to SQL Server When System Administrators Are Locked Out.
If the BUILTIN\Administrators login is present, administrators can connect by starting their application using the Run as Administrator option.
Requires the CONTROL SERVER permission.
A. Migrating a single user
The following example migrates a SQL Server login named Barry, to a contained database user with password. The example retains the does not change the user name, and retains the login as enabled.
B. Migrating all database users with logins to contained database users without logins
The following example migrates all users that are based on SQL Server logins to contained database users with passwords. The example excludes logins that are not enabled. The example must be executed in the contained database.
DECLARE @username sysname ; DECLARE user_cursor CURSOR FOR SELECT dp.name FROM sys.database_principals AS dp JOIN sys.server_principals AS sp ON dp.sid = sp.sid WHERE dp.authentication_type = 1 AND sp.is_disabled = 0; OPEN user_cursor FETCH NEXT FROM user_cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE sp_migrate_user_to_contained @username = @username, @rename = N'keep_name', @disablelogin = N'disable_login'; FETCH NEXT FROM user_cursor INTO @username END CLOSE user_cursor ; DEALLOCATE user_cursor ;