sp_help_revlogin for contained database users?

Question

I've been using sp_help_revlogin for a long time to transfer server principals from one SQL instance to another.

Is there a sp_help_revlogin equivalent that can output create scripts for partially contained database users? I understand that if I backup/restore the contained user comes along, but I have a need to deploy users from one contained database to another, different, contained database.

Argenis has a great article about this but the 0xsql.com site is unresponsive. There is a cached version of the article if you can do without the pictures: https://webcache.googleusercontent.com/search?q=cache:F24yFIM_WqYJ:https://www.0xsql.com/2014/07/28/scripting-out-contained-database-users/+&cd=12&hl=en&ct=clnk&gl=us — Aaron Bertrand, Oct 05 '18 at 12:21
Take a read at this, you might discover something new and interesting https://blog.netnerds.net/2016/06/its-2016-why-is-sp_help_revlogin-a-thing/ — Mattia Nocerino, Oct 05 '18 at 12:32
Why are you transferring contained database users? They are contained in the database, and if you fail over, restore or attach the database somewhere else, they will come along. — David Browne - Microsoft, Oct 05 '18 at 12:39
@DavidBrowne-Microsoft, I have sandboxes that I need to copy the login to and the database is too big for the staging environment as is. — Craig Efrein, Oct 05 '18 at 13:11

score 4 · Accepted Answer · answered Oct 05 '18 at 13:22

While David is right, the contained user will automatically come along with the contained database if you backup/restore to another location (that's kind of the point), I can see some use cases for this, like creating the same contained user across a set of databases, with the same password, without having to know (or change) the password. Or deploying the same user that has been created on dev / QA / test / staging environments to the production copy of the database, without moving the database.

If you know (or can change) the password, of course, scripting out all the contained users in a database is pretty simple, just take this output and replace whatever with the desired password for each contained user:

SELECT N'CREATE USER ' + QUOTENAME(name) 
  + N' WITH PASSWORD = N''whatever'';'
FROM sys.database_principals
WHERE authentication_type_desc = N'DATABASE';

If the requirement is that you don't know the password, though, it is a bit more complex. Based mostly on this post by Argenis Fernandez (which is currently not very snappy):

First, make sure there isn't a server-level login with the same name as the contained user.
Connect to the instance using the Dedicated Administrator Connection (see this post for troubleshooting steps if you can't).
Switch context to the contained database where your existing user lives. I'm going to assume the contained user is named bob.

Run the following:

USE OldContainedDatabase;
GO

DECLARE @sql nvarchar(max);

SELECT @sql = N'CREATE LOGIN ' + QUOTENAME(name) 
  + N' WITH PASSWORD = ' + CONVERT(varchar(256), password, 1) 
  + N' HASHED;'
FROM sys.sysowners 
WHERE name = N'bob';

-- if the target contained database is on the same server:
EXEC sys.sp_executesql @sql;

-- otherwise:
SELECT @sql; -- run this output on the target server

Connect to the target contained database (no longer need DAC), create the user from the server-level login, migrate that user to a contained user, and drop the login:

USE NewContainedDatabase;
GO

CREATE USER [bob] FROM LOGIN [bob];
GO

EXEC sys.sp_migrate_user_to_contained 
  @username     = N'bob', 
  @rename       = N'keep_name', 
  @disablelogin = N'disable_login'; 
GO

DROP LOGIN [bob];

I just tested this on my machine and was able to log in to both databases explicitly using the same username and password, but users would have been able to do the same even if I didn't happen to know their password throughout this whole process.

sp_help_revlogin for contained database users?

1 Answers1