quarta-feira, 1 de agosto de 2012 20:52
How to copy/move server level and database level roles with permission/current setting to new server?
Todas as Respostas
quarta-feira, 1 de agosto de 2012 22:23
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 DatabaseRoleAll the best,