create procedure sys.sp_addrolemember
@rolename sysname,
@membername sysname
AS
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @roluid int,
@owner int,
@memuid int,
@ret int
-- Was a part of check to prevent inside a user Xact. Leave for backward compat--
set implicit_transactions off
--cannot change membership of public
if @rolename = 'public'
begin
raiserror(15081, -1,-1)
return(1)
end
BEGIN TRANSACTION
-- SHARE LOCK ROLE --
EXEC %%Owner(Name = @rolename).Lock(Exclusive = 0)
-- ROLE NAME (OBTAIN OWNER FOR PERMISSIONS) --
IF @@ERROR = 0
select @roluid = principal_id, @owner = owning_principal_id from sys.database_principals
where name = @rolename and type = 'R'
-- ERROR IF ROLE NOT FOUND OR PUBLIC --
if @roluid is null
begin
ROLLBACK TRANSACTION
raiserror(15014,-1,-1,@rolename)
return (1)
end
-- CHECK PERMISSIONS --
-- Only member of db_owner can add members to db-fixed roles --
if (not is_member('db_owner') = 1) and
(not ( (@roluid >=16384 and @roluid < 16400) and is_member('db_owner') = 1)) and
(not ( (@roluid < 16384 or @roluid >= 16400) and is_member('db_securityadmin') = 1)) and
(not ( (@roluid < 16384 or @roluid >= 16400) and
((is_member(user_name(@owner)) = 1) or (has_perms_by_name(@rolename, 'role', 'alter') = 1))
))
begin
ROLLBACK TRANSACTION
EXEC %%System().AuditEvent(ID = 1296125010, Success = 0, TargetLoginName = NULL, TargetUserName = @membername, Role = @rolename, Object = NULL, Provider = NULL, Server = NULL)
raiserror(15247,-1,-1)
return (1)
end
else
begin
EXEC %%System().AuditEvent(ID = 1296125010, Success = 1, TargetLoginName = NULL, TargetUserName = @membername, Role = @rolename, Object = NULL, Provider = NULL, Server = NULL)
end
-- EXCL LOCK MEMBER --
EXEC %%Owner(Name = @membername).Lock(Exclusive = 1) -- may fail
-- CHECK MEMBER NAME (ATTEMPT ADDING IMPLICIT ROW FOR NT NAME) --
if @@error = 0 -- lock succss, indicate member exists
select @memuid = principal_id from sys.database_principals where name = @membername
if @memuid is null -- indicate lock failed
begin
EXEC @ret = sys.sp_MSadduser_implicit_ntlogin @membername
if (@ret = 0)
select @memuid = principal_id from sys.database_principals where name = @membername
-- Member locked by sp_MSadduser_implicit_ntlogin
end
if @memuid is null
begin
ROLLBACK TRANSACTION
raiserror(15410, -1, -1, @membername)
return (1)
end
-- CANNOT CHANGE MEMBERSHIP OF FIXED ROLES OR DBO --
if @memuid in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, sys
or (@memuid >= 16384 and @memuid < 16400)
begin
ROLLBACK TRANSACTION
raiserror(15405, -1 ,-1, @membername)
return (1)
end
-- CHECK FOR CIRCULAR MEMBERSHIPS --
if is_rolemember(@membername, @rolename) = 1
begin
ROLLBACK TRANSACTION
raiserror(15413, -1, -1)
return (1)
end
-- SET ROLE MEMBER FOR THIS USER --
-- INVALIDATE CACHED PERMISSIONS (MEMBERSHIP CHANGES PERMISSIONS) --
EXEC %%Owner(Name = @membername).SetRoleMember(RoleID = @roluid, IsMember = 1)
COMMIT TRANSACTION
-- RETURN SUCCESS --
return (0) -- sp_addrolemember
|
