#MSSQL #Users #Research Admins, owners, users, roles, role members, and database permissions

role_permissions.sql

-- server 'sysadmin'
SELECT   name as 'sysadmin',type_desc,is_disabled
FROM     master.sys.server_principals 
WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name

-- database owner
select suser_sname(owner_sid) as 'owner'
from sys.databases
where name = DB_NAME()

-- database users
SELECT name FROM sys.database_principals
WHERE TYPE IN ('S','U')
ORDER BY name ASC

-- database roles
-- role definitions
SELECT
dp1.name as RoleName,
dp2.name AS OwnedBy,
'CREATE ROLE [' + dp1.name + '] AUTHORIZATION [' + dp2.name + ']' AS cmd
FROM 
sys.database_principals dp1
JOIN 
sys.database_principals dp2 ON dp1.owning_principal_id = dp2.principal_id
WHERE dp1.type = 'R'
AND dp1.is_fixed_role = 0
AND dp1.name <> 'public'

-- role members
SELECT p2.name dbrole, p.name dbuser, 'EXEC sp_addrolemember ''' + p2.name + ''', ''' + p.name + '''' cmd
from 
sys.database_role_members m
join 
sys.database_principals p on m.member_principal_id = p.principal_id
join
sys.database_principals p2 on m.role_principal_id = p2.principal_id
WHERE p.name <> 'dbo'
ORDER BY p2.name, p.name

-- database permissions
SELECT
--/*
  prin.name AS UserName,
  objsch.name SchemaName,
  perm.class,
  perm.state_desc + ' ' + perm.permission_name AS Permission,
  CASE 
    WHEN perm.class = 1 AND perm.minor_id <> 0 THEN 'COLUMN'
    WHEN perm.class = 1 THEN obj.type_desc
    ELSE perm.class_desc
  END AS ObjectType,
  CASE
    WHEN perm.class = 3 THEN sch.name
    WHEN cols.object_id IS NOT NULL THEN obj.name + '.' + cols.name
    WHEN perm.class = 0 THEN DB_NAME()
    ELSE ISNULL(obj.name, 'n/a')
  END AS ObjectName,
--*/
perm.state_desc + ' ' + perm.permission_name collate SQL_Latin1_General_CP1_CI_AS + 
CASE WHEN perm.class <> 0 -- don't do this part for databases
  THEN
    ' ON ' +
    CASE
        WHEN perm.class = 3 THEN 'SCHEMA::[' + sch.name + ']'
        WHEN cols.object_id IS NOT NULL THEN '[' + objsch.name + '].[' + obj.name + '](' + cols.name + ')'
        WHEN perm.class = 0 THEN DB_NAME()
        ELSE ISNULL('[' + objsch.name + '].[' + obj.name + ']', 'n/a')
    END --AS ObjectName--,
    ELSE ''
END
+ ' TO ['
+   prin.name 
+ ']' AS cmd
FROM 
sys.database_permissions perm
JOIN
sys.database_principals prin on perm.grantee_principal_id = prin.principal_id
LEFT JOIN
sys.all_objects obj ON perm.major_id = obj.object_id
LEFT JOIN
sys.all_columns cols ON perm.major_id = cols.object_id and perm.minor_id = cols.column_id
LEFT JOIN
sys.schemas objsch ON obj.schema_id = objsch.schema_id
LEFT JOIN
sys.schemas sch ON perm.major_id = sch.schema_id
WHERE prin.name <> 'public'
AND prin.name <> 'dbo'
--AND perm.major_id >= 0
--AND perm.class_desc <> 'DATABASE'
ORDER BY 
  prin.name, 
  perm.class,
  ObjectType,
CASE
    WHEN perm.class = 3 THEN '[' + sch.name + ']'
    WHEN cols.object_id IS NOT NULL THEN '[' + objsch.name + '].[' + obj.name + '](' + cols.name + ')'
    WHEN perm.class = 0 THEN DB_NAME()
    ELSE ISNULL('[' + objsch.name + '].[' + obj.name + ']', 'n/a')
END