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:
Post a Comment