locked
Any script to take a backup of the database users, roles and their object level permissions ? RRS feed

  • Question

  • Any script to take a backup of the database users, roles and their object level permissions ?
    Monday, May 28, 2012 9:37 AM

Answers


  • SELECT HAS_DBACCESS ( 'database_name' )
    SELECT HAS_PERMS_BY_NAME('Adventureworks2008', 'DATABASE', 'BACKUP DATABASE');


    In SQL Server 2005, you can use the Has_Perms_By_Name() function 
    (http://msdn2.microsoft.com/en-us/library/ms189802.aspx).

    > For example, I would like to list all stored procedures which a role has 
    > execute permission for.

    This is an example of usage:

    SELECT o.SchemaAndName,
    has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
    FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
    SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
    FROM sys.objects
    WHERE type = 'P') AS o
    ----------Group access----------------------------------------------------
    select s1.name 
      from syslogins s1 
           join dtm..sysusers s2 on s1.sid = s2.sid 
                and s2.hasdbaccess = 1
     where s1.isntgroup = 1 and s1.hasaccess = 1 and s1.sysadmin = 0
     order by s1.name

    ---------------------------------------------------------------------------------
     select [Role] = r.name, [Member] = m.name
       from   sys.database_role_members rm
       join   sys.database_principals r on rm.role_principal_id = r.principal_id
       join   sys.database_principals m on 
              rm.member_principal_id = m.principal_id
       order  by r.name, m.name
    -----------------------------------------------------------------------------------------------
    fn_my_permissions
    ---------------------------------------------------------------------------------
    select p.state_desc + ' ' + p.permission_name +
     ' OBJECT::' + s.name collate Latin1_general_CI_AS+ +'.'+o.name
     collate Latin1_general_CI_AS+ ' TO ' + u.name collate Latin1_general_CI_AS + ' GO',
     p.* from sys.database_permissions p inner join sys.objects o 
    on p.major_id = o.object_id inner join sys.schemas s on s.schema_id = o.schema_id 
    inner join sys.database_principals u on p.grantee_principal_id = u.principal_id

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Iric Wen Sunday, June 3, 2012 2:10 PM
    Monday, May 28, 2012 9:39 AM
    Answerer

All replies


  • SELECT HAS_DBACCESS ( 'database_name' )
    SELECT HAS_PERMS_BY_NAME('Adventureworks2008', 'DATABASE', 'BACKUP DATABASE');


    In SQL Server 2005, you can use the Has_Perms_By_Name() function 
    (http://msdn2.microsoft.com/en-us/library/ms189802.aspx).

    > For example, I would like to list all stored procedures which a role has 
    > execute permission for.

    This is an example of usage:

    SELECT o.SchemaAndName,
    has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
    FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
    SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
    FROM sys.objects
    WHERE type = 'P') AS o
    ----------Group access----------------------------------------------------
    select s1.name 
      from syslogins s1 
           join dtm..sysusers s2 on s1.sid = s2.sid 
                and s2.hasdbaccess = 1
     where s1.isntgroup = 1 and s1.hasaccess = 1 and s1.sysadmin = 0
     order by s1.name

    ---------------------------------------------------------------------------------
     select [Role] = r.name, [Member] = m.name
       from   sys.database_role_members rm
       join   sys.database_principals r on rm.role_principal_id = r.principal_id
       join   sys.database_principals m on 
              rm.member_principal_id = m.principal_id
       order  by r.name, m.name
    -----------------------------------------------------------------------------------------------
    fn_my_permissions
    ---------------------------------------------------------------------------------
    select p.state_desc + ' ' + p.permission_name +
     ' OBJECT::' + s.name collate Latin1_general_CI_AS+ +'.'+o.name
     collate Latin1_general_CI_AS+ ' TO ' + u.name collate Latin1_general_CI_AS + ' GO',
     p.* from sys.database_permissions p inner join sys.objects o 
    on p.major_id = o.object_id inner join sys.schemas s on s.schema_id = o.schema_id 
    inner join sys.database_principals u on p.grantee_principal_id = u.principal_id

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Iric Wen Sunday, June 3, 2012 2:10 PM
    Monday, May 28, 2012 9:39 AM
    Answerer
  • you can also use GENERATE Script from task and create script for USERs and database Roles
    Monday, May 28, 2012 10:22 AM