April 9, 2008 at 10:02 am
Brian Laws, can u please provide me with complete script for sa password changes as you script has missing dbo.usp_CheckUserPassword and dbo.usp_WriteError thanks in advance.
April 9, 2008 at 10:14 am
Here you go:
create procedure [dbo].[usp_CheckUserPassword]
/*======================================================================
Name of Procedure: usp_CheckUserPassword
Date Created: 02/23/06
Created By: Brian Laws
Description: Check to see if a given password is valid for a server
Returns: 0 = Success, 1 = Failure
Error codes:
======================================================================
Date ModifiedDeveloperDescription of Mod/ModNumber
======================================================================
declare @ErrorMessage varchar(1000)
declare @Return bit
exec @Return = dbo.usp_CheckUserPassword 'ServerName', 'sa', 'test3', @ErrorMessage output
print 'Return value: ' + cast(@Return as char(1)) + '. Error message: ' + isnull(@ErrorMessage, '')
======================================================================*/
(
@ServerNamevarchar(50),
@UserNamevarchar(128),
@Passwordvarchar(128),
@ErrorMessagevarchar(1000) OUTPUT
)
as
begin
set xact_abort off
set nocount on
declare @conn int -- ado connection object
declare @hr int -- ole goto return value
declare @src varchar(255) -- ole error source
declare @desc varchar(255) -- ole error description
declare @ConnectionString varchar(255)-- connection string
declare @Returnbit-- Return value (0 = success, 1 = failure)
declare @ErrAdo int
declare @ErrScr varchar (255)
declare @ErrDesc varchar (255)
set @ConnectionString = 'Provider=SQLOLEDB;Data Source=' + @ServerName + ';User ID=' + @UserName + ';Password=' + @Password + ';'
set @Return = 1
--create the connection object
exec @hr = sp_oacreate 'adodb.connection', @conn out
if @hr <> 0
begin
exec sp_oageterrorinfo @conn, @src out, @desc out
set @errado = @hr
set @errscr = @src
set @errdesc = @desc
goto ExitError
end
-- Set the connection string
exec @hr = sp_oasetproperty @conn, 'ConnectionString', @ConnectionString
if @hr <> 0
begin
exec sp_oageterrorinfo @conn, @src out, @desc out
set @errado = @hr
set @errscr = @src
set @errdesc = @desc
goto ExitError
end
-- Attempt to open the connection
exec @hr = sp_oamethod @conn, 'open'
if @hr <> 0
begin
exec sp_oageterrorinfo @conn, @src out, @desc out
set @errado = @hr
set @errscr = @src
set @errdesc = @desc
goto ExitError
end
set @Return = 0
goto ExitCleanup
ExitError:
-- If there's an error, return the error message
set @ErrorMessage = @desc
ExitCleanup:
-- Cleanup the connection object
exec @hr = sp_oadestroy @conn
if @hr <> 0
begin
exec sp_oageterrorinfo @conn, @src out, @desc out
set @errado = @hr
set @errscr = @src
set @errdesc = @desc
set @ErrorMessage = @desc
end
return @Return
end
GO
use Errors
GO
create procedure [dbo].[usp_WriteError]
/*======================================================================
Name of Procedure: usp_WriteError
Date Created: 12/14/05
Created By: Brian Laws
Description: Record errors to the Errors database
Error codes: 70001
======================================================================
Date ModifiedDeveloperDescription of Mod/ModNumber
======================================================================
exec usp_WriteError 'this is a test', 1, 2, 3, 'proc', 4
select * from errors.dbo.errors
======================================================================*/
@ErrorMessagenvarchar(4000),
@ErrorNumberint,
@ErrorStateint,
@ErrorSeverityint,
@ErrorProcedurenvarchar(126),
@ErrorLineint
as
begin
set nocount on;
begin try
insert into Errors.dbo.Errors
(
ErrorMessage,
ErrorNumber,
ErrorState,
ErrorSeverity,
ErrorProcedure,
ErrorLine
)
values
(
@ErrorMessage,
@ErrorNumber,
@ErrorState,
@ErrorSeverity,
@ErrorProcedure,
@ErrorLine
)
end try
begin catch
-- On error, just raise a message to the system.
declare @error_message nvarchar(4000), @error_number int, @error_state int, @error_severity int
select @error_number = error_number(), @error_state = error_state(), @error_severity = error_severity()
set @error_message = left('Error Message: ' + error_message() + ' Error Number: ' + cast(@error_number as nvarchar(10)) + '.', 4000)
raiserror(70001, @error_severity, @error_state, @error_message) WITH LOG
end catch
end
GO
GRANT EXECUTE ON [dbo].[usp_WriteError] TO [public]
And here's the Error table:
use Errors
GO
CREATE TABLE [dbo].[Errors](
[ErrorID] [int] IDENTITY(1,1) NOT NULL,
[ErrorDateTime] [datetime] NOT NULL CONSTRAINT [DF_Errors_ErrorDateTime] DEFAULT (getdate()),
[ErrorMessage] [nvarchar](4000) NULL,
[ErrorNumber] [int] NULL,
[ErrorState] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorProcedure] [nvarchar](126) NULL,
[ErrorLine] [int] NULL,
[UserName] [nvarchar](256) NULL CONSTRAINT [DF_Errors_UserName] DEFAULT (suser_name()),
[HostName] [nvarchar](30) NULL CONSTRAINT [DF_Errors_HostName] DEFAULT (host_name()),
[AppName] [nvarchar](128) NULL CONSTRAINT [DF_Errors_AppName] DEFAULT (app_name()),
CONSTRAINT [PK_Errors] PRIMARY KEY CLUSTERED
(
[ErrorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User who generated the error' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Errors', @level2type=N'COLUMN',@level2name=N'UserName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the host which generated the error.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Errors', @level2type=N'COLUMN',@level2name=N'HostName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the application which generated the error.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Errors', @level2type=N'COLUMN',@level2name=N'AppName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date/time of the error' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Errors'
April 9, 2008 at 1:22 pm
Brian Thanks a lot for providing me complete script
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply