February 16, 2010 at 9:28 am
Hi all,
I want to capture permissions for all user in a database in Sql server 2005.
I have to restore a db using a full backup file from a Prod to Dev db. Problem I have is, I want to keep all the existing permissions prior to restore on Dev db. If anyone has script to capture and restore all user permission on dev db that will be very helpfull.
Thanks in advance
Gill
February 16, 2010 at 10:21 am
This works for me. other people might have written a better one.
select OBJECT_SCHEMA_NAME(sys.objects.OBJECT_ID) AS [SCHEMA], sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
sys.database_permissions.permission_name,
sys.database_permissions.state permission_state,
sys.database_permissions.state_desc,
state_desc + ' ' + permission_name +
CASE when OBJECT_SCHEMA_NAME(sys.objects.OBJECT_ID) IS NOT NULL
THEN ' on ['+ OBJECT_SCHEMA_NAME(sys.objects.OBJECT_ID) + '].'+ '[' + sys.objects.name + ']'
ELSE '' END
+
' to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
left join sys.objects on sys.database_permissions.major_id = sys.objects.object_id
join sys.database_principals on sys.database_permissions.grantee_principal_id =
sys.database_principals.principal_id
where sys.database_permissions.major_id >=0
order by 1, 2, 3, 5
Craig Outcalt
February 16, 2010 at 10:25 am
I should mention that you'll need to revoke everything first.
The way I do it, which is admittedly awkward is to run the script on the DB you want to blow the security away from and change the grants and denies to revokes, then run the script you saved from the original DB.
Cheers!
Craig Outcalt
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply