locked
what does this script do??? RRS feed

  • Question

  • SET NOCOUNT ON





    DECLARE @OldUser sysname, @NewUser sysname





    SET @OldUser = 'HRUser'

    SET @NewUser = 'PersonnelAdmin'





    SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'





    SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'





    SELECT 'EXEC sp_addrolemember @rolename ='

    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'

    FROM sys.database_role_members AS rm

    WHERE USER_NAME(rm.member_principal_id) = @OldUser

    ORDER BY rm.role_principal_id ASC



    what does the third select statement in above script do?? Please if you can explain me the above script??





    Thank you,

    Thursday, March 4, 2010 11:39 AM

Answers

  • This script looks like it generates the T-SQL scripts to clone permissions from one user to another. The 3rd statement gets all the roles to which the "olduser" is a member of and generates the relevant DDL to add the *newuser" to the same roles.
    every day is a school day
    Thursday, March 4, 2010 11:50 AM

All replies

  • This script looks like it generates the T-SQL scripts to clone permissions from one user to another. The 3rd statement gets all the roles to which the "olduser" is a member of and generates the relevant DDL to add the *newuser" to the same roles.
    every day is a school day
    Thursday, March 4, 2010 11:50 AM
  • This Script generates T-SQL command to assign the same database roles(access permissions) from @olduser to @newUser. This script requires two inputs(User1 & User2).

    Thursday, March 4, 2010 12:19 PM