How to create a Role or User based on an existing Role or User in MSSQL 2005

I found myself needing to create new sql users and roles with permissions from existing users in the database in order to tidy up some database logins. After an hour or so of searching I still hadn’t found anything like what I was looking for so I asked on experts exchange.

One of the users posted this great sql script from Vyas website which creates a SQL script to set the permissions on a user or role to that of an existing user or role.

All you need to generate a change script is set the @OldUser and @NewUser parameters to those in your database and run the script. The great thing then is that you can edit the script that this script generates (eg remove some grant or denys to certain objects) before you execute it on your database.