SCRIPT : To take backup of user permiissions, object level permissions and roles

You can use this script to to extract user permissions in a database with object level permission and roles. It is recommended to get the output in text format, so that it will be easy to have a copy.

PRINT 'DATABASE NAME : ' +DB_NAME()

SET NOCOUNT ON

/*CREATE DATABASE ROLES*/

SELECT 'CREATE ROLE [' + a.name + '] AUTHORIZATION [' + b.name + ']' from sys.database_principals a

INNER JOIN sys.database_principals b ON a.owning_principal_id = b.principal_id

where a.type = 'R' and a.is_fixed_role <> 1

GO

------------------------------------------------------------------------------------------------------------------

/*ASSIGN ROLES TO USER*/

SELECT --rm.role_principal_id,

'EXEC sp_addrolemember @rolename ='

+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')

+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships'

FROM sys.database_role_members AS rm

ORDER BY rm.role_principal_id

GO

-------------------------------------------------------------------------------------------------------------------

/*SELECT OBJECT LEVEL PERMISSION*/

SELECT

CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +

perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'

+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

ORDER BY usr.name

-------------------------------------------------------------------------------------------------------------------

/*SELECT OBJECT LEVEL PERMISSION FOR A OBJECT*/

SELECT

CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +

perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'

+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

Where obj.name = 'ULTIMATES_CURRENT'

ORDER BY usr.name

SET NOCOUNT OFF