Scripting out the logins but not all

When you are migrating the all the databases on a server to another then you might have used sp_help_revlogin to script out the logins. That was easy! But if your SQL instance from where you are moving the databases is holding databases for multiple application and application owner do not agree on migrating databases on same date. Or say you want to move different databases to different target servers. In this case you need to script out logins for particular database only.

You can use following query, after setting the results to text, to get login creation script for logins associated with perticular database only.

Step 1. Setting the results to text:

Step 2. Execute the following query in the database for which logins are to be scripted:

USE DatabaseName;
GO

SELECT 'EXEC [dbo].[sp_help_revlogin] @login_name = [' + l.NAME + ']'
FROM sys.sysusers u
INNER JOIN sys.syslogins l ON l.sid = u.sid

You will get the output as follows:

Step 3.
Copy the output of the above T-SQL in step 2, except the last line where it mentions number of rows affected and the first dotted line. Paste it in new query window and execute it against the master database of same (source) server as below:

Save the output of step 3 and execute it on your target server to create the required logins.

For creating login scripts for the only logins which were created after certain date you can replace the query in step 2 with following:

USE master
GO

SELECT 'EXEC [dbo].[sp_help_revlogin] @login_name = [' + NAME + ']'
FROM sys.syslogins
WHERE createdate > '2013-04-01 00:00:00.000'