asked on

Display what SQL tables, views, permission a specific user has.

I am moving SQL servers to a new environment and I had to delete and recreate a user on the new server and it blew away their access (Permissions) to the views and tables they have access to on the current server. I need away to see which tables and views they have access to, and what permissions they have on those objects on the current server. there are over 50 tables and views so a stare and compare is not a viable option.
Any help would be greatly appreciated.

Dale Fye

I have a similar issue, although I have not done the migration yet.

Is there a way to generate a script that will recreate all of the user level roles and object level permissions in a specific database on the current server so that the script can be run on the new server? I mean, this happens alot, someone has to have a solution.

Dale

Fred Webb

ASKER

Dale, I finally found this script and I am going to modify it to generate the GRANT scripts for all the views and tables

SELECT * FROM   
    (
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT'
    ,perm.class_desc AS 'RIGHT_ON'
    ,p.NAME AS 'GRANTEE'
    ,m.NAME AS 'USERNAME'
    ,s.name AS 'SCHEMA'
    ,o.name AS 'OBJECT'
    ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
    FROM
    sys.database_permissions perm
    INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
    LEFT JOIN sys.database_role_members rm ON rm.role_principal_id = p.principal_id
    LEFT JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
    LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
    LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    UNION ALL
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT'
    ,perm.class_desc AS 'RIGHT_ON'
    ,'SELF-GRANTED' AS 'GRANTEE'
    ,p.NAME AS 'USERNAME'
    ,s.name AS 'SCHEMA'
    ,o.name AS 'OBJECT'
    ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
    FROM
    sys.database_permissions perm
    INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
    LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
    LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    ) AS [union]
    WHERE [union].USERNAME = 'USER' -- Username you will search for
    ORDER BY [union].RIGHT_ON, [union].PERMISSION, [union].GRANTEE

Open in new window

ASKER CERTIFIED SOLUTION

Fred Webb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial