Exporting database permissions to a CSV file

Question

I am trying to export all the user information / permissions to a file for documentation. I trying to find a script out this task, is there anyway to pull all the permission from all the SQL databases from a server at one time? Working with SQL 2008 and 2012.

Answer 1

Here is a script that someone found for me it works perfect. I'm posting so other people can use.

set nocount on
declare @permission table (
Database_Name sysname,
User_Role_Name sysname,
Account_Type nvarchar(60),
Action_Type nvarchar(128),
Permission nvarchar(60),
ObjectName sysname null,
Object_Type nvarchar(60)
)
declare @dbs table (dbname sysname)
declare @Next sysname
insert into @dbs
select name from sys.databases order by name
select top 1 @Next = dbname from @dbs
while (@@rowcount<>0)
begin
insert into @permission
exec('use [' + @Next + ']
declare @objects table (obj_id int, obj_type char(2))
insert into @objects
select id, xtype from master.sys.sysobjects
insert into @objects
select object_id, type from sys.objects

SELECT ''' + @Next + ''', a.name as ''User or Role Name'', a.type_desc as ''Account Type'',
d.permission_name as ''Type of Permission'', d.state_desc as ''State of Permission'',
OBJECT_SCHEMA_NAME(d.major_id) + ''.'' + object_name(d.major_id) as ''Object Name'',
case e.obj_type
when ''AF'' then ''Aggregate function (CLR)''
when ''C'' then ''CHECK constraint''
when ''D'' then ''DEFAULT (constraint or stand-alone)''
when ''F'' then ''FOREIGN KEY constraint''
when ''PK'' then ''PRIMARY KEY constraint''
when ''P'' then ''SQL stored procedure''
when ''PC'' then ''Assembly (CLR) stored procedure''
when ''FN'' then ''SQL scalar function''
when ''FS'' then ''Assembly (CLR) scalar function''
when ''FT'' then ''Assembly (CLR) table-valued function''
when ''R'' then ''Rule (old-style, stand-alone)''
when ''RF'' then ''Replication-filter-procedure''
when ''S'' then ''System base table''
when ''SN'' then ''Synonym''
when ''SQ'' then ''Service queue''
when ''TA'' then ''Assembly (CLR) DML trigger''
when ''TR'' then ''SQL DML trigger''
when ''IF'' then ''SQL inline table-valued function''
when ''TF'' then ''SQL table-valued-function''
when ''U'' then ''Table (user-defined)''
when ''UQ'' then ''UNIQUE constraint''
when ''V'' then ''View''
when ''X'' then ''Extended stored procedure''
when ''IT'' then ''Internal table''
end as ''Object Type''
FROM [' + @Next + '].sys.database_principals a 
left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id
left join @objects e on d.major_id = e.obj_id
order by a.name, d.class_desc')
delete @dbs where dbname = @Next
select top 1 @Next = dbname from @dbs
end
set nocount off
select @@SERVERNAME as Server_name,* from @permission

Answer 2

@Damaged I used powershell to export "Database-Level Object Permissions".. you can try below soluition.

[string]$SrvIns = 'YourServerName'
[string]$db     = 'YourDatabaseName'

        $sql = "    SELECT 
        usr.name AS 'User', 
        CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END AS PermType, 
        perm.permission_name,
        USER_NAME(obj.schema_id) AS SchemaName, 
        obj.name AS ObjectName, 
        CASE obj.Type  
            WHEN 'U' THEN 'Table'
            WHEN 'V' THEN 'View'
            WHEN 'P' THEN 'Stored Proc'
            WHEN 'FN' THEN 'Function'
        ELSE obj.Type END AS ObjectType, 
        CASE WHEN cl.column_id IS NULL THEN '--' ELSE cl.name END AS ColumnName, 
        CASE WHEN perm.state = 'W' THEN 'X' ELSE '--' END AS IsGrantOption 
    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.Type <> 'S'
    ORDER BY 
        usr.name, perm.state_desc ASC, perm.permission_name ASC"

Invoke-Sqlcmd -ServerInstance $SrvIns -Database $db -Query $sql  | Export-Csv C:\permission.csv

Good Luck**