Script Database Roles, Application Roles, Server Roles

I would like a tsql script to script out the already created database roles,
application roles, and server roles for a SQL Server 2005 database.

Please help me create tsql script to complete task using the adventureworks
database.

Thanks,

I think Aaron wrote this script
DECLARE @db sysname,
@role sysname,
@rolecur nvarchar(MAX),
@addusr nvarchar(MAX),
@addrolemember nvarchar(257),
@oldusr sysname,
@newusr sysname

SELECT @oldusr = 'frits',
@newusr = 'neufrits'

DECLARE dbcur CURSOR STATIC LOCAL FOR
SELECT quotename(name) FROM sys.databases
OPEN dbcur

WHILE 1 = 1
BEGIN
FETCH dbcur INTO @db
IF @@fetch_status <> 0
BREAK

SELECT @addrolemember = @db + '..sp_addrolemember'

SELECT @rolecur =
'DECLARE rolecur CURSOR STATIC GLOBAL FOR
SELECT rol.name
FROM ' + @db + ' .sys.database_principals rol
JOIN ' + @db + ' .sys.database_role_members rm
ON rm.role_principal_id = rol.principal_id
JOIN ' + @db + ' .sys.database_principals usr
ON rm.member_principal_id = usr.principal_id
JOIN master.sys.server_principals sp
ON usr.sid = sp.sid
WHERE sp.name = @templateusr'

EXEC sp_executesql @rolecur, N'@templateusr sysname', @oldusr
OPEN rolecur

WHILE 1 = 1
BEGIN
FETCH rolecur INTO @role
IF @@fetch_status <> 0
BREAK

SELECT @addusr = 'USE ' + @db + '
IF USER_ID(@newusr) IS NULL
CREATE USER ' + quotename(@newusr)
EXEC sp_executesql @addusr, N'@newusr sysname', @newusr

EXEC @addrolemember @role, @newusr
END

DEALLOCATE rolecur
END

DEALLOCATE dbcur

"Tim Patterson" wrote in message

>
> I would like a tsql script to script out the already created database
> roles,
> application roles, and server roles for a SQL Server 2005 database.
>
> Please help me create tsql script to complete task using the
> adventureworks
> database.
>
> Thanks,