copy server level and database level roles

Todas as Respostas

• quarta-feira, 1 de agosto de 2012 22:23

   

   
  

  0

  Scripts are the best way to move server level permissions.

  If you have restored the databases to another server, you should have the database roles and permission already moved.

  When you have moved the databases, but need to catch up the logins (for server level access) look at this article: http://support.microsoft.com/kb/918992

  For determining role memberships and permissions you can leverage (from SQL Server 2005 forward) the catalog views.  The following do not generate the script, but they give you the information you need to generate your own scripts.  

  -- Server Security
  select u.name, p.permission_name, p.class_desc, object_name(p.major_id) ObjectName, state_desc from sys.Server_permissions  p join sys.Server_principals u
  on p.grantee_principal_id = u.principal_id
  order by ObjectName, name, p.permission_name
  -- Server Role Memberships
  select u.name ServerRole, u2.name Member 
  from sys.Server_role_members m
  join sys.Server_principals u on m.role_principal_id = u.principal_id
  join sys.Server_principals u2 on m.member_principal_id = u2.principal_id
  order by ServerRole
  -- Run Database scripts in each database.
  -- Database Security 
  select u.name, p.permission_name, p.class_desc, object_name(p.major_id) ObjectName, state_desc from sys.database_permissions  p join sys.database_principals u
  on p.grantee_principal_id = u.principal_id
  order by ObjectName, name, p.permission_name
  -- Database Role Memberships
  select u.name DatabaseRole, u2.name Member 
  from sys.database_role_members m
  join sys.database_principals u on m.role_principal_id = u.principal_id
  join sys.database_principals u2 on m.member_principal_id = u2.principal_id
  order by DatabaseRole

  All the best,
  RLF
  
  

  • Editado Russell FieldsMVP quarta-feira, 1 de agosto de 2012 22:23
  • Marcado como Resposta Maggie LuoMicrosoft Contingent Staff, Moderator segunda-feira, 13 de agosto de 2012 09:34
  •  
  • Responder
  • Citação