I am in the process of getting rid of universal users for all applications and have to create and move a great number of users from test to production and was looking for easiest way. This is the best I have found so far.
/* This script is used for moving a login and schema from test to
production. This includes creating a new schema and read and write access
The steps are:
1. Go to the test enviroment and select User Name -> CREATE to cliboard
2. Paste the new user information in the block below
3. Select the new user name
4. Highlight the [NewUser] name
5. Run a find and replace on the new user name (There should be 5)
6. Copy the schema name [NewUserSchema]
7. Replace schema name in the last block
8. Set the password in the first line
9. Run on the correct server
*/
CREATE LOGIN [NewUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[AuditDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- START paste new user info here
USE [AuditDB]
GO
/****** Object: User [NewUser] Script Date: 01/10/2011 10:46:51 ******/
GO
CREATE USER [NewUser] FOR LOGIN [NewUser] WITH DEFAULT_SCHEMA=[NewUserSchema]
GO
-- END Paste new user
EXEC sp_addrolemember 'db_datawriter', [NewUser] ;
EXEC sp_addrolemember 'db_datareader', [NewUser] ;
GO
CREATE SCHEMA [NewUserSchema] AUTHORIZATION [NewUser]
GO
