Lists user login attempts to a SQL Server (failed and successful login attempts)

Here is one tip, when you create the temp tables, don't explicitly name your Constraints. if you name it explicitly,it will try to create a named Constraint, but the problem is, sql server won't be able to map the temp table and the named constraint, and if you don't name it explicitly, then sql server will generate an internal id which will be based on the internal id of the temp table(remember, each temp table has its own unique object id ). Getting confused ? Don't worry, run the following Script...

-- Create the stored procedure

CREATE PROCEDURE testTempTable
@i INT = 100
AS
BEGIN
SELECT OBJECT_ID('tempdb..#a') AS IDtab,
OBJECT_ID('tempdb..PK_a') AS IDConstraint

IF OBJECT_ID('tempdb..#a') IS NOT NULL
DROP TABLE #a

CREATE TABLE #a(
[i] [int] IDENTITY(1,1) NOT NULL,
[j] [int] NULL,
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[i] ASC
)--WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
SELECT OBJECT_ID('tempdb..#a') AS IDtab,
OBJECT_ID('tempdb..PK_a') AS IDConstraint

SELECT NAME, id, xtype, info, base_schema_ver FROM tempdb..sysobjects

WHILE @i >0
BEGIN
INSERT INTO #a SELECT @@SPID
SELECT @i = @i - 1
END
SELECT * FROM #a

END

--- Step 2
--- Take two Query analyser Windows and run the following scripts on both simultaneously

--Window 1

EXEC testTempTable 100000


--Window 2

SELECT NAME, id, xtype, info, base_schema_ver FROM tempdb..sysobjects
EXEC testTempTable 100000

and watch the Query results of window#2

"
Server: Msg 2714, Level 16, State 4, Procedure testTempTable, Line 9
There is already an object named 'PK_a' in the database.
Server: Msg 1750, Level 16, State 1, Procedure testTempTable, Line 9
Could not create constraint. See previous errors.
"


So, what could be done, in order to avoid this
1. Use table variables as far as possible
2. If u wanna use temp tables, then, dont put named constraints
3. if named constraints need to be used, then after dropping the table, don't forget to drop the Constraint..