Generate Users and Roles Script

Couple of weeks back I wrote a script for generate object level permission in the database. People started requesting me to write a script which should script all the users and roles in a database. I came up with this script which will script roles and users in a database and it will also associate user with their roles.

For Application role I have given a temporary password TempPassword$ you can change it in script as per your need. I have skipped sa, dbo, guest, sys and Information Schema users because these users will be added automatically, only user created roles and users will be scripted out.

USE DBName
GO
--Script Database Users and Roles
SELECT
    CASE type
    WHEN 'S' THEN 'CREATE USER ['+name+'] FOR LOGIN ['+SUSER_SNAME(sid)+'] WITH DEFAULT_SCHEMA=['+default_schema_name +']'
    WHEN 'U' THEN 'CREATE USER ['+name+'] FOR LOGIN ['+SUSER_SNAME(sid)+'] WITH DEFAULT_SCHEMA=['+default_schema_name +']'
    WHEN 'G' THEN 'CREATE USER ['+name+'] FOR LOGIN ['+SUSER_SNAME(sid)+']'
    WHEN 'A' THEN 'CREATE APPLICATION ROLE ['+name+'] WITH DEFAULT_SCHEMA=['+default_schema_name +'],PASSWORD=''TempPassword$'''
    WHEN 'R' THEN 'CREATE ROLE ['+name+'] AUTHORIZATION ['+USER_NAME(owning_principal_id)+']'
    END FROM sys.database_principals
WHERE type IN ('S','U','G','A','R') and name not like '##%' and name not in('sa','dbo','guest','sys','INFORMATION_SCHEMA')
AND is_fixed_role <> 1
ORDER BY type
--Script Users and Associated roles
SELECT
    'EXEC sp_addrolemember '''+USER_NAME(b.role_principal_id)+''',''' +a.name + ''''
FROM sys.database_principals a
LEFT OUTER JOIN sys.database_role_members b
ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL
    AND a.type IN ('S','U','G','A','R') AND a.is_fixed_role <> 1 AND
    a.name NOT LIKE '##%' AND b.role_principal_id IS NOT NULL
ORDER BY Name