if object_Id( 'dbspUsrMgmt') Is Not Null
drop procedure dbspUsrMgmt
go

CREATE procedure dbspUsrMgmt
  @user varchar( 50) = NULL,
  @pwd varchar( 50) = NULL,
  @IUD char( 1) = NULL,
  @Grp varchar( 50) = NULL,
  @db char( 40) = NULL
as
/*
*************************************************************
Name: dbspUsrMgmt
Description:
   Inserts the appropriate SQL into the DBA_Assist table to
be run by the scheduler.

Usage:exec dbspUsrMgmt <user>, <password>, <group>,

Author: Steve Jones

Input Params:
-------------
@user 		varchar( 50) REQUIRED. Username for server
@pwd 		varchar( 50) REQUIRED. password
@IUD 		char( 1) REQUIRED. Action (I=insert, P=changepwd, d=delete)
@Grp 		varchar( 50) Optional. group in database, Defaults to public.
@db 		char( 40) defaults to NULL. defaults to 'DBA'

Output Params:
--------------

Return:

Results:
---------

Locals:
--------
@err		Holds error value

Modifications:
--------------

*************************************************************
*/
set nocount on
declare @err int

select @err = 0
/*
Check parameters and exit if not correct.
*/
if @user Is NULL
  select @err = -1
if @pwd Is NULL
  select @err = -1
if @iud Is NULL
  select @err = -1

if @err = -1
begin
  Raiserror( 'Parameter Error:Usage:exec dbspUsrMgmt', 12, 1)
  return @err
end

set nocount on
declare @cmd varchar( 250)

if @db Is NULL
select @db = 'DBA'
if @grp Is NULL
select @grp = 'Public'

if @IUD = 'I'
begin
  select @cmd = 'sp_addlogin ''' + rtrim( @user) + ''', ''' + rtrim( @pwd) + 
''', ' + rtrim( @db)
  insert DBA_Assist values (@cmd, getdate(), '01/01/1900')
  select @cmd = 'exec ' + rtrim( @db) + '..sp_adduser ''' + rtrim( @user) + 
''', ''' + rtrim( @user) + ''', ''' + rtrim( @grp) + ''''
  insert DBA_Assist values (@cmd, getdate(), '01/01/1900')
end
if @IUD = 'D'
begin
  select @cmd = 'exec ' + rtrim( @db) + '..sp_dropuser ''' + rtrim( @user) + 
''''
  insert DBA_Assist values (@cmd, getdate(), '01/01/1900')
  select @cmd = 'sp_droplogin ''' + rtrim( @user) + ''''
  insert DBA_Assist values (@cmd, getdate(), '01/01/1900')
end
if @IUD = 'P'
begin
  select @cmd = 'exec ' + rtrim( @db) + '..sp_password NULL, ''' + rtrim( 
@pwd) + ''', ' + rtrim( @user)
  insert DBA_Assist values (@cmd, getdate(), '01/01/1900')
end

return @err
GO
grant execute on dbspUsrMgmt to WebUser
go
if object_id( 'dbspUsrMgmt') Is Null
select 'dbspUsrMgmt Not Created'
else
select 'dbspUsrMgmt Created'
go