create procedure sys.sp_approlepassword
@rolename sysname, -- name of app role
@newpwd sysname -- new password
AS
declare @stmt nvarchar(4000)
-- CHECK FOR NULL PASSWORD
if (@newpwd is null)
begin
raiserror(15034,-1,-1)
return (1)
end
-- CHECK FOR NULL NAME
if (@rolename is null)
begin
raiserror(15004,-1,-1)
return (1)
end
-- DISALLOW USER TRANSACTION --
set implicit_transactions off
if (@@trancount > 0)
begin
raiserror(15002, -1, -1, 'sys.sp_approlepassword')
return (1)
end
set @stmt = 'alter application role '
set @stmt = @stmt + quotename(@rolename, ']')
set @stmt = @stmt + ' with password = '
set @stmt = @stmt + quotename(@newpwd, '''')
BEGIN TRANSACTION
exec (@stmt)
if @@error <> 0
begin
ROLLBACK TRANSACTION
return (1)
end
COMMIT TRANSACTION
-- RETURN SUCCESS --
return (0) -- sp_approlepassword
|
