Configuring a SQL user script

I have been working on a script to make my job easier at work. The script I needed to create was to take variables set once and create a SQL user account while setting the required permissions. After numerous trials and many failures I have it finished. I figured I would share it with the world so someone else can stop pulling out their hair. If you find a better way let me know.

I make no warranties.

USE [master] GO DECLARE @UName varchar(255), @Password varchar(255), @dbName nvarchar(255), @sqlstmt varchar(200); /*****SQL USer Account 'change test to what the account is going to be'*/ SET @UName = 'test' /*****Password for account 'change test1'*/ SET @Password = 'test1' /*****Database you wish to create the account for*/ SET @dbName = 'CGS_sde' /*****Setup user in SQL*****/ EXEC ('CREATE LOGIN '+ @UName + ' WITH PASSWORD=''' + @Password + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'); /*****Setup user in specified database set above*/ EXEC('USE ' + @dbName + ' CREATE USER [' + @UName + '] FOR LOGIN [' + @UName + ']') /*****Set permissions in database for user*/ EXEC ('USE ' + @dbName + ' EXEC sp_addrolemember ' + 'db_datareader,' + @UName); EXEC ('USE ' + @dbName + ' EXEC sp_addrolemember ' + 'db_datawriter,' + @UName); EXEC ('USE ' + @dbName + ' EXEC sp_addrolemember ' + 'db_securityadmin,' + @UName); EXEC ('USE ' + @dbName + ' GRANT CREATE PROCEDURE TO [' + @UName + ']'); EXEC ('USE ' + @dbName + ' GRANT CREATE TABLE TO [' + @UName + ']'); GO