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
 users_roles_script_out

Leave a Reply

Join the Conversation

2 Comments

Leave a comment

Your email address will not be published. Required fields are marked *

*


x

Related Posts

Backup Report
I was working on one the project and they were looking for a backup report which should specify whether it's physical or logical and few more det...
T-SQL Script to find the names of the StoredProcedure that has used dynamic SQL
This article has the script that will be useful to find the names of the  Stored procedure that has used dynamic sql within the definition of the...
Generate Permission script for an object or all object
In the DBA world it’s necessary to pickup permissions for an object for all objects when he\she deploys the code. During deployment if the code h...
powered by RelatedPosts