Database Restore - SQL Script for listing securables

Question

I restored a database from one server to another. After restoring the database, I came across this issue of orphaned users which I resolved using -

exec sp_change_users_login 'Update_One', 'UserName', 'LoginName'

Now, this particular user is linked with given server login. All good until here.
However, I am still having an issue that is to do with 'Securables'

As permission to many database objects was missing for this user, I Googled and found out a way - Generate Script by -

1. Select Database
2. Right click database to see context menu
3. Select 'Tasks', 
4. From the sub-menu, select 'Generate Scripts'
5. Select 'Set Scripting Options'
6. From 'Advanced' section - set 'Object Level Permissions' to true.

So, we will get a list of all the GRANT SELECT/GRANT EXECUTE scripts etc.

But, I am looking for another way, where I don't have to run through this wizard every time and I can write my own database script to list database securables and permissions for a given database user.

Can anyone please guide which (system)database tables should I look for?

Thank you!

This was answered by http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database — Jon, Jun 25 '12 at 22:18

score 2 · Answer 1 · answered Jun 07 '13 at 14:48

-- 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


-- sp_helpuser

-- select distinct 'DROP USER [' + name + ']' from sys.database_principals order by 1

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

To get back the table permissions for a given user:

  SELECT * FROM (

        select 
          USER_NAME(p.grantee_principal_id) as grantee,
          o.name                          AS TABLE_NAME,
          convert(varchar(10), CASE p.type
                WHEN 'RF' THEN 'REFERENCES'
                WHEN 'SL' THEN 'SELECT'
                WHEN 'IN' THEN 'INSERT'
                WHEN 'DL' THEN 'DELETE'
                WHEN 'UP' THEN 'UPDATE'
                END)                        AS PRIVILEGE_TYPE
        from 
        sys.database_permissions p,
        sys.objects o
        where 
        o.type in ('U', 'V') AND
        p.major_id = o.object_id AND
        p.minor_id = 0

  ) table_privileges  

WHERE grantee = 'myuser'

And for the columns on those tables:

SELECT * FROM (
        SELECT User_name(p.grantor_principal_id) 
               AS 
               grantor, 
               User_name(p.grantee_principal_id) 
               AS grantee, 
               Db_name() 
               AS table_catalog, 
               Schema_name(o.schema_id) 
               AS table_schema, 
               o.name 
               AS table_name, 
               c.name 
               AS column_name, 
               CONVERT(VARCHAR(10), CASE p.TYPE WHEN 'SL' THEN 'SELECT' WHEN 'UP' THEN 
               'UPDATE' 
               WHEN 'RF' THEN 'REFERENCES' END) 
               AS privilege_type, 
               CONVERT(VARCHAR(3), CASE p.state WHEN 'G' THEN 'NO' WHEN 'W' THEN 'YES' 
               END) AS 
               is_grantable 
        FROM   sys.database_permissions p, 
               sys.objects o, 
               sys.columns c 
        WHERE  o.TYPE IN ( 'U', 'V' ) 
               AND o.object_id = c.object_id 
               AND p.class = 1 
               AND p.major_id = o.object_id 
               AND ( p.minor_id = c.column_id 
                      OR ( p.minor_id = 0 
                           AND NOT EXISTS (SELECT * 
                                           FROM   sys.database_permissions m 
                                           WHERE  m.class = 1 
                                                  AND m.major_id = p.major_id 
                                                  AND m.minor_id = c.column_id 
                                                  AND m.TYPE = p.TYPE 
                                                  AND m.state <> p.state) ) ) 
               AND p.TYPE IN ( 'RF', 'SL', 'UP' ) 
               AND p.state IN ( 'G', 'W' ) 
               AND 0 != ( Permissions(o.object_id, c.name) & -- back compat 
                          CASE p.TYPE 
                            WHEN 'RF' THEN 4 -- REFERENCES basebit 
                            WHEN 'SL' THEN 1 -- SELECT basebit 
                            WHEN 'UP' THEN 2 -- UPDATE basebit 
                          END ) 

    ) column_privileges
WHERE grantee = 'myuser'

These are modified versions of the scripts I got from here: https://dba.stackexchange.com/a/9118/5074

Database Restore - SQL Script for listing securables

2 Answers2