Tuesday, 26 August 2014

Clone permissions of another user in SQL server

This script show how copy permissions (or DB roles) from user (@usr1) to another user (@usr2).

/********


******/
DECLARE @usr1 VARCHAR(20)
DECLARE @usr2
VARCHAR(20)
DECLARE
@Grp VARCHAR(30)


DECLARE  group_cur CURSOR  FOR  SELECT name FROM sysusers WHERE issqlRole=1

SET @usr1 = 'user-orig'
SET @usr2 = 'user-target'


OPEN group_cur
FETCH  NEXT FROM group_cur INTO @Grp

WHILE @@FETCH_STATUS = 0
BEGIN
      IF EXISTS(SELECT *

                          FROM sysmembers AS member INNER JOIN sysusers AS usr ON member.memberuid = usr.uid           
                          INNER JOIN  sysusers AS usg ON member.groupuid = usg.Uid
                          WHERE usr.name = @usr1 AND usg.name= @grp)

            EXEC sp_addrolemember @Grp, @usr2
     ELSE
           EXEC sp_droprolemember @Grp, @usr2


     FETCH NEXT FROM group_cur INTO @Grp

END

CLOSE group_cur

DEALLOCATE group_cur

--------

No comments: