CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
Vladislav Zalesak
Vladislav Zalesak

Reputation: 713

"Renaming" a database role

For reasons i don't want to elaborate, it has been decided to rename our database roles. I know there is no simple way to rename a role (no idea why), but i came up with this solution:

create table #RoleAssignment (id int identity, RoleName varchar(256), UserName varchar(256))
insert into #RoleAssignment
select roles.name as RoleName, users.name as UserName 
  from sys.sysusers as roles
  join sys.database_role_members as members
    on members.role_principal_id = roles.uid
  join sys.sysusers as users
    on users.uid = members.member_principal_id
 where roles.issqlrole = 1
   and roles.name like 'OldRolePrefix_%'

declare @id int = null
declare @RoleName varchar(256)
declare @NewRoleName varchar(256)
declare @UserName varchar(256)

select top 1 @id = id, @RoleName = RoleName, @UserName = UserName,@NewRoleName = replace(RoleName,'OldRolePrefix_','NewRolePrefix_') from #RoleAssignment

while (@id is not null)
begin
    execute sp_executesql N'sp_droprolemember @InSPRoleName, @InSPUserName',N'@InSPRoleName varchar(256), @InSPUserName varchar(256)', @InSPRoleName = @RoleName, @InSPUserName = @UserName 
    execute sp_executesql N'sp_addrolemember @InSPRoleName, @InSPUserName',N'@InSPRoleName varchar(256), @InSPUserName varchar(256)', @InSPRoleName = @NewRoleName, @InSPUserName = @UserName 
    delete from #RoleAssignment where id = @id
    set @id = null
    select top 1 @id = id, @RoleName = RoleName, @UserName = UserName from     #RoleAssignment
end

After this ,the scripts are run that set up role permissions.

I tested this and overall it works. But even after readign and testing out I am not sure: Did i forgot about anything?

Thank you a lot for any ideas/insights

Upvotes: 3

Views: 3398

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

You didn't specify the version of SQL Server you are using but in SQL Server 2008 and later, you can rename a role with ALTER ROLE:

ALTER ROLE OldRoleName WITH NAME = NewRoleName;

Upvotes: 12

Related Questions