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 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy