Transferring User Security permissions from one server database to database on another server

  • 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.

  • 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