create procedure sys.sp_addsrvrolemember
@loginame sysname, -- login name
@rolename sysname = NULL -- server role name
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES
set nocount on
declare @ret int, -- return value of sp call
@isrole int -- is_srvrolemember
-- DISALLOW USER TRANSACTION
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sys.sp_addsrvrolemember')
return (1)
end
-- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS
select @isrole = is_srvrolemember(@rolename)
if @isrole is null
begin
EXEC %%System().AuditEvent(ID = 1145131603, Success = 0, TargetLoginName = @loginame, TargetUserName = NULL, Role = @rolename, Object = NULL, Provider = NULL, Server = NULL)
raiserror(15402, -1, -1, @rolename)
return (1)
end
if @isrole = 0 and not is_srvrolemember('sysadmin') = 1
begin
EXEC %%System().AuditEvent(ID = 1145131603, Success = 0, TargetLoginName = @loginame, TargetUserName = NULL, Role = @rolename, Object = NULL, Provider = NULL, Server = NULL)
raiserror(15247,-1,-1)
return (1)
end
if @rolename = N'public'
begin
raiserror(15081, -1 ,-1)
return (1)
end
-- AUDIT A SUCCESSFUL SECURITY CHECK
EXEC %%System().AuditEvent(ID = 1145131603, Success = 1, TargetLoginName = @loginame, TargetUserName = NULL, Role = @rolename, Object = NULL, Provider = NULL, Server = NULL)
-- CANNOT CHANGE SA ROLES
if suser_sid(@loginame) = 0x1 -- 'sa'
or suser_sid(@loginame) = 0x2 -- 'public'
begin
raiserror(15405, -1 ,-1, @loginame)
return (1)
end
-- ERROR IF login is a role
if exists(select * from sys.server_principals where name = @loginame and type = 'R')
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
BEGIN TRANSACTION
-- LOCK LOGIN
EXEC %%LocalLogin(Name = @loginame).Lock(Exclusive = 1)
-- ADD ROW FOR NT LOGIN IF NEEDED
if @@error <> 0 -- not found
begin
execute @ret = sys.sp_MSaddlogin_implicit_ntlogin @loginame
if (@ret <> 0)
begin
ROLLBACK TRANSACTION
raiserror(15007,-1,-1,@loginame)
return (1)
end
-- login locked
end
-- UPDATE ROLE MEMBERSHIP
EXEC %%LocalLogin(Name = @loginame).SetRoleMember(RoleName = @rolename, IsMember = 1)
COMMIT TRANSACTION
-- RETURN SUCCESS
return (0) -- sp_addsrvrolemember