April 5, 2010 at 9:04 pm
I have created a user in a development database with limited permissions. I now want to transfer that users login and all the associated database permissions to a production database.
Transferring the actual login is a fairly simple task which I have completed. But I need to find a way of scripting out all the permissions that the user has in the development database to add those permissions to the production database.
Does anyone have a script that will script out all the permissions allocated to a single user of a database to run on the production server?
Thanks for your help.
April 6, 2010 at 12:52 pm
For object level permissions:
--SQL 2005 ONLY
--This script will generate all user permissions and the code to
recreate them as required.
SELECT C.name 'Schema',
B.name AS Object,
D.name username,
A.type permissions_type,
A.permission_name,
A.state permission_state,
A.state_desc,
state_desc
+ ' ' + permission_name
+ ' ON ['+ C.name
+ '].[' + B.name + '] to ['
+ D.name
+ ']' COLLATE LATIN1_General_CI_AS AS
Generated_Code
FROM sys.database_permissions AS A JOIN sys.objects AS B ON
A.major_id =
B.object_id
JOIN sys.schemas AS C ON B.schema_id =
C.schema_id
JOIN sys.database_principals AS D ON
A.grantee_principal_id = D.principal_id
ORDER BY 1, 2, 3, 5
HTH!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply