|
 |
asp_databases thread: Urgent help wanted with creating a stored procedure
Message #1 by "Eric Van Camp" <eric.vancamp@c...> on Sat, 28 Oct 2000 10:09:53 +0100
|
|
hi,
i want to run a script for creating the stored procedure sp_checklogin
i I run the script i get the following error:
whilest running a script to create a stored procedure i get the following
error:
Msg 208, Level 16, State 2
Invalid object name 'sp_checklogin'.
what is going on?
the scipt i run in i-sql is the following:
/***************************************************************************
******
* Table : sp_checklogin
*
* Desc : Checks Valid Login
*
****************************************************************************
*****/
CREATE PROCEDURE sp_checklogin
(
@SessionID VARCHAR(40),
@Level VARCHAR(10)
/* Allowed Rights
GENERAL
REPORT
ADMIN
SYSADMIN */
)
AS
DECLARE @CoyID VARCHAR(20)
DECLARE @UserID VARCHAR(20)
DECLARE @AccessNumber TINYINT
DECLARE @Timeout INT
DECLARE @UserState TINYINT
DECLARE @CoyStatus TINYINT
DECLARE @Rights INT
/* Check Session Exists */
IF NOT EXISTS (SELECT SessionID FROM USERLOG WHERE SessionID=@SessionID)
BEGIN
SELECT Progress=1,Errormsg="Session does Not Exist."
RETURN
END
/* Check Session Details if Session Still Valid */
SELECT
@CoyID=CoyID,@UserID=UserID,@AccessNumber=AccessNumber,@Timeout=DATEDIFF(MIN
UTE,Accessdt,GETDATE()),@UserState=UserState FROM USERLOG WHERE
SessionID=@SessionID
IF NOT (@UserState = 0)
BEGIN
SELECT Progress=1,Errormsg="Session has already ended."
RETURN
END
/* Check Session Details if Session Timed Out */
IF @Timeout >= 30
BEGIN
/* Mark Session as Timed Out */
UPDATE USERLOG SET UserState=2 WHERE SessionID=@SessionID
SELECT Progress=1,Errormsg="Session has already timed out."
RETURN
END
/* Check Company Privileges */
SELECT @CoyStatus=Status FROM COMPANY WHERE CoyID=@CoyID
IF (@CoyStatus=0)
BEGIN
SELECT Progress=1,Errormsg="Company In Disabled Mode."
RETURN
END
/* Admin User */
IF (@UserID = "MASTER")
BEGIN
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
/* Other Users */
IF (@Level = "GENERAL")
BEGIN
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
IF (@Level = "REPORT")
BEGIN
SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND UserID=@UserID
IF NOT (@Rights & 1 = 1) /* Logical Operation on Bit 0 */
BEGIN
SELECT Progress=2,Errormsg="User Does Not Have REPORT rights."
RETURN
END
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
IF (@Level = "ADMIN")
BEGIN
SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND UserID=@UserID
IF NOT (@Rights & 2 = 2) /* Logical Operation on Bit 1 */
BEGIN
SELECT Progress=2,Errormsg="User Does Not Have ADMIN rights."
RETURN
END
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
IF (@Level = "SYSADMIN")
BEGIN
SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND UserID=@UserID
IF NOT (@Rights & 4 = 4) /* Logical Operation on Bit 2 */
BEGIN
SELECT Progress=2,Errormsg="User Does Not Have SYSADMIN rights."
RETURN
END
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
SELECT Progress=3,Errormsg="Invalid Operation Requested."
RETURN
GRANT EXECUTE ON sp_checklogin TO public
GO
Message #2 by "Dallas Martin" <dmartin@z...> on Sat, 28 Oct 2000 09:19:14 -0400
|
|
It seems the user attempting to execute this sproc doesn't have the required
"execute" permissions. Login in as dbo/sa and click on the sproc, then
click on the "permissions" button and assign the execute permission to this
user. If the user doesn't appear in the list of user, then the user needs to
be
added to the database as a User or needs to be added to the server.
----- Original Message -----
From: "Eric Van Camp" <eric.vancamp@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, October 28, 2000 5:09 AM
Subject: [asp_databases] Urgent help wanted with creating a stored procedure
> hi,
> i want to run a script for creating the stored procedure sp_checklogin
> i I run the script i get the following error:
> whilest running a script to create a stored procedure i get the following
> error:
> Msg 208, Level 16, State 2
> Invalid object name 'sp_checklogin'.
> what is going on?
>
> the scipt i run in i-sql is the following:
>
/***************************************************************************
> ******
> * Table : sp_checklogin
> *
> * Desc : Checks Valid Login
> *
>
****************************************************************************
> *****/
> CREATE PROCEDURE sp_checklogin
> (
> @SessionID VARCHAR(40),
> @Level VARCHAR(10)
> /* Allowed Rights
> GENERAL
> REPORT
> ADMIN
> SYSADMIN */
> )
> AS
> DECLARE @CoyID VARCHAR(20)
> DECLARE @UserID VARCHAR(20)
> DECLARE @AccessNumber TINYINT
> DECLARE @Timeout INT
> DECLARE @UserState TINYINT
> DECLARE @CoyStatus TINYINT
> DECLARE @Rights INT
>
> /* Check Session Exists */
> IF NOT EXISTS (SELECT SessionID FROM USERLOG WHERE SessionID=@SessionID)
> BEGIN
> SELECT Progress=1,Errormsg="Session does Not Exist."
> RETURN
> END
>
> /* Check Session Details if Session Still Valid */
> SELECT
>
@CoyID=CoyID,@UserID=UserID,@AccessNumber=AccessNumber,@Timeout=DATEDIFF(MIN
> UTE,Accessdt,GETDATE()),@UserState=UserState FROM USERLOG WHERE
> SessionID=@SessionID
> IF NOT (@UserState = 0)
> BEGIN
> SELECT Progress=1,Errormsg="Session has already ended."
> RETURN
> END
>
> /* Check Session Details if Session Timed Out */
> IF @Timeout >= 30
> BEGIN
> /* Mark Session as Timed Out */
> UPDATE USERLOG SET UserState=2 WHERE SessionID=@SessionID
> SELECT Progress=1,Errormsg="Session has already timed out."
> RETURN
> END
>
> /* Check Company Privileges */
> SELECT @CoyStatus=Status FROM COMPANY WHERE CoyID=@CoyID
> IF (@CoyStatus=0)
> BEGIN
> SELECT Progress=1,Errormsg="Company In Disabled Mode."
> RETURN
> END
>
> /* Admin User */
> IF (@UserID = "MASTER")
> BEGIN
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> /* Other Users */
> IF (@Level = "GENERAL")
> BEGIN
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> IF (@Level = "REPORT")
> BEGIN
> SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
UserID=@UserID
> IF NOT (@Rights & 1 = 1) /* Logical Operation on Bit 0 */
> BEGIN
> SELECT Progress=2,Errormsg="User Does Not Have REPORT rights."
> RETURN
> END
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> IF (@Level = "ADMIN")
> BEGIN
> SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
UserID=@UserID
> IF NOT (@Rights & 2 = 2) /* Logical Operation on Bit 1 */
> BEGIN
> SELECT Progress=2,Errormsg="User Does Not Have ADMIN rights."
> RETURN
> END
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> IF (@Level = "SYSADMIN")
> BEGIN
> SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
UserID=@UserID
> IF NOT (@Rights & 4 = 4) /* Logical Operation on Bit 2 */
> BEGIN
> SELECT Progress=2,Errormsg="User Does Not Have SYSADMIN rights."
> RETURN
> END
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> SELECT Progress=3,Errormsg="Invalid Operation Requested."
> RETURN
>
> GRANT EXECUTE ON sp_checklogin TO public
> GO
>
Message #3 by "Eric Van Camp" <eric.vancamp@c...> on Sat, 28 Oct 2000 16:47:24 +0100
|
|
Hi MArtin,
i think i did not explain well..
the sproc does not exist untill now and the script creates the stored
procedure for the database..
I run the script in ISQL and het the error as indicated before...I log in as
sa...and connect to the correct server, on the right database...
any idea?
txs again
eric
-----Original Message-----
From: Dallas Martin [mailto:dmartin@z...]
Sent: Saturday, October 28, 2000 2:19 PM
To: ASP Databases
Subject: [asp_databases] Re: Urgent help wanted with creating a stored
procedure
It seems the user attempting to execute this sproc doesn't have the required
"execute" permissions. Login in as dbo/sa and click on the sproc, then
click on the "permissions" button and assign the execute permission to this
user. If the user doesn't appear in the list of user, then the user needs to
be
added to the database as a User or needs to be added to the server.
----- Original Message -----
From: "Eric Van Camp" <eric.vancamp@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, October 28, 2000 5:09 AM
Subject: [asp_databases] Urgent help wanted with creating a stored procedure
> hi,
> i want to run a script for creating the stored procedure sp_checklogin
> i I run the script i get the following error:
> whilest running a script to create a stored procedure i get the following
> error:
> Msg 208, Level 16, State 2
> Invalid object name 'sp_checklogin'.
> what is going on?
>
> the scipt i run in i-sql is the following:
>
/***************************************************************************
> ******
> * Table : sp_checklogin
> *
> * Desc : Checks Valid Login
> *
>
****************************************************************************
> *****/
> CREATE PROCEDURE sp_checklogin
> (
> @SessionID VARCHAR(40),
> @Level VARCHAR(10)
> /* Allowed Rights
> GENERAL
> REPORT
> ADMIN
> SYSADMIN */
> )
> AS
> DECLARE @CoyID VARCHAR(20)
> DECLARE @UserID VARCHAR(20)
> DECLARE @AccessNumber TINYINT
> DECLARE @Timeout INT
> DECLARE @UserState TINYINT
> DECLARE @CoyStatus TINYINT
> DECLARE @Rights INT
>
> /* Check Session Exists */
> IF NOT EXISTS (SELECT SessionID FROM USERLOG WHERE SessionID=@SessionID)
> BEGIN
> SELECT Progress=1,Errormsg="Session does Not Exist."
> RETURN
> END
>
> /* Check Session Details if Session Still Valid */
> SELECT
>
@CoyID=CoyID,@UserID=UserID,@AccessNumber=AccessNumber,@Timeout=DATEDIFF(MIN
> UTE,Accessdt,GETDATE()),@UserState=UserState FROM USERLOG WHERE
> SessionID=@SessionID
> IF NOT (@UserState = 0)
> BEGIN
> SELECT Progress=1,Errormsg="Session has already ended."
> RETURN
> END
>
> /* Check Session Details if Session Timed Out */
> IF @Timeout >= 30
> BEGIN
> /* Mark Session as Timed Out */
> UPDATE USERLOG SET UserState=2 WHERE SessionID=@SessionID
> SELECT Progress=1,Errormsg="Session has already timed out."
> RETURN
> END
>
> /* Check Company Privileges */
> SELECT @CoyStatus=Status FROM COMPANY WHERE CoyID=@CoyID
> IF (@CoyStatus=0)
> BEGIN
> SELECT Progress=1,Errormsg="Company In Disabled Mode."
> RETURN
> END
>
> /* Admin User */
> IF (@UserID = "MASTER")
> BEGIN
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> /* Other Users */
> IF (@Level = "GENERAL")
> BEGIN
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> IF (@Level = "REPORT")
> BEGIN
> SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
UserID=@UserID
> IF NOT (@Rights & 1 = 1) /* Logical Operation on Bit 0 */
> BEGIN
> SELECT Progress=2,Errormsg="User Does Not Have REPORT rights."
> RETURN
> END
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> IF (@Level = "ADMIN")
> BEGIN
> SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
UserID=@UserID
> IF NOT (@Rights & 2 = 2) /* Logical Operation on Bit 1 */
> BEGIN
> SELECT Progress=2,Errormsg="User Does Not Have ADMIN rights."
> RETURN
> END
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> IF (@Level = "SYSADMIN")
> BEGIN
> SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
UserID=@UserID
> IF NOT (@Rights & 4 = 4) /* Logical Operation on Bit 2 */
> BEGIN
> SELECT Progress=2,Errormsg="User Does Not Have SYSADMIN rights."
> RETURN
> END
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> SELECT Progress=3,Errormsg="Invalid Operation Requested."
> RETURN
>
> GRANT EXECUTE ON sp_checklogin TO public
> GO
Message #4 by "Dallas Martin" <dmartin@z...> on Sat, 28 Oct 2000 14:03:06 -0400
|
|
send me the script
----- Original Message -----
From: "Eric Van Camp" <eric.vancamp@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, October 28, 2000 11:47 AM
Subject: [asp_databases] Re: Urgent help wanted with creating a stored
procedure
> Hi MArtin,
> i think i did not explain well..
> the sproc does not exist untill now and the script creates the stored
> procedure for the database..
> I run the script in ISQL and het the error as indicated before...I log in
as
> sa...and connect to the correct server, on the right database...
> any idea?
> txs again
> eric
>
> -----Original Message-----
> From: Dallas Martin [mailto:dmartin@z...]
> Sent: Saturday, October 28, 2000 2:19 PM
> To: ASP Databases
> Subject: [asp_databases] Re: Urgent help wanted with creating a stored
> procedure
>
>
> It seems the user attempting to execute this sproc doesn't have the
required
> "execute" permissions. Login in as dbo/sa and click on the sproc, then
> click on the "permissions" button and assign the execute permission to
this
> user. If the user doesn't appear in the list of user, then the user needs
to
> be
> added to the database as a User or needs to be added to the server.
>
>
> ----- Original Message -----
> From: "Eric Van Camp" <eric.vancamp@c...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Saturday, October 28, 2000 5:09 AM
> Subject: [asp_databases] Urgent help wanted with creating a stored
procedure
>
>
> > hi,
> > i want to run a script for creating the stored procedure sp_checklogin
> > i I run the script i get the following error:
> > whilest running a script to create a stored procedure i get the
following
> > error:
> > Msg 208, Level 16, State 2
> > Invalid object name 'sp_checklogin'.
> > what is going on?
> >
> > the scipt i run in i-sql is the following:
> >
>
/***************************************************************************
> > ******
> > * Table : sp_checklogin
> > *
> > * Desc : Checks Valid Login
> > *
> >
>
****************************************************************************
> > *****/
> > CREATE PROCEDURE sp_checklogin
> > (
> > @SessionID VARCHAR(40),
> > @Level VARCHAR(10)
> > /* Allowed Rights
> > GENERAL
> > REPORT
> > ADMIN
> > SYSADMIN */
> > )
> > AS
> > DECLARE @CoyID VARCHAR(20)
> > DECLARE @UserID VARCHAR(20)
> > DECLARE @AccessNumber TINYINT
> > DECLARE @Timeout INT
> > DECLARE @UserState TINYINT
> > DECLARE @CoyStatus TINYINT
> > DECLARE @Rights INT
> >
> > /* Check Session Exists */
> > IF NOT EXISTS (SELECT SessionID FROM USERLOG WHERE
SessionID=@SessionID)
> > BEGIN
> > SELECT Progress=1,Errormsg="Session does Not Exist."
> > RETURN
> > END
> >
> > /* Check Session Details if Session Still Valid */
> > SELECT
> >
>
@CoyID=CoyID,@UserID=UserID,@AccessNumber=AccessNumber,@Timeout=DATEDIFF(MIN
> > UTE,Accessdt,GETDATE()),@UserState=UserState FROM USERLOG WHERE
> > SessionID=@SessionID
> > IF NOT (@UserState = 0)
> > BEGIN
> > SELECT Progress=1,Errormsg="Session has already ended."
> > RETURN
> > END
> >
> > /* Check Session Details if Session Timed Out */
> > IF @Timeout >= 30
> > BEGIN
> > /* Mark Session as Timed Out */
> > UPDATE USERLOG SET UserState=2 WHERE SessionID=@SessionID
> > SELECT Progress=1,Errormsg="Session has already timed out."
> > RETURN
> > END
> >
> > /* Check Company Privileges */
> > SELECT @CoyStatus=Status FROM COMPANY WHERE CoyID=@CoyID
> > IF (@CoyStatus=0)
> > BEGIN
> > SELECT Progress=1,Errormsg="Company In Disabled Mode."
> > RETURN
> > END
> >
> > /* Admin User */
> > IF (@UserID = "MASTER")
> > BEGIN
> > SELECT @AccessNumber = @AccessNumber + 1
> > UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
> WHERE
> > SessionID=@SessionID
> > SELECT Progress=0,CoyID=@CoyID
> > RETURN
> > END
> >
> > /* Other Users */
> > IF (@Level = "GENERAL")
> > BEGIN
> > SELECT @AccessNumber = @AccessNumber + 1
> > UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
> WHERE
> > SessionID=@SessionID
> > SELECT Progress=0,CoyID=@CoyID
> > RETURN
> > END
> >
> > IF (@Level = "REPORT")
> > BEGIN
> > SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
> UserID=@UserID
> > IF NOT (@Rights & 1 = 1) /* Logical Operation on Bit 0 */
> > BEGIN
> > SELECT Progress=2,Errormsg="User Does Not Have REPORT rights."
> > RETURN
> > END
> > SELECT @AccessNumber = @AccessNumber + 1
> > UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
> > SessionID=@SessionID
> > SELECT Progress=0,CoyID=@CoyID
> > RETURN
> > END
> >
> > IF (@Level = "ADMIN")
> > BEGIN
> > SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
> UserID=@UserID
> > IF NOT (@Rights & 2 = 2) /* Logical Operation on Bit 1 */
> > BEGIN
> > SELECT Progress=2,Errormsg="User Does Not Have ADMIN rights."
> > RETURN
> > END
> > SELECT @AccessNumber = @AccessNumber + 1
> > UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
> > SessionID=@SessionID
> > SELECT Progress=0,CoyID=@CoyID
> > RETURN
> > END
> >
> > IF (@Level = "SYSADMIN")
> > BEGIN
> > SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
> UserID=@UserID
> > IF NOT (@Rights & 4 = 4) /* Logical Operation on Bit 2 */
> > BEGIN
> > SELECT Progress=2,Errormsg="User Does Not Have SYSADMIN rights."
> > RETURN
> > END
> > SELECT @AccessNumber = @AccessNumber + 1
> > UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
> > SessionID=@SessionID
> > SELECT Progress=0,CoyID=@CoyID
> > RETURN
> > END
> >
> > SELECT Progress=3,Errormsg="Invalid Operation Requested."
> > RETURN
> >
> > GRANT EXECUTE ON sp_checklogin TO public
> > GO
>
Message #5 by Maxime Bombardier <BombardierM@s...> on Sat, 28 Oct 2000 18:57:15 -0400
|
|
Well I copy-pasted your code and it ran fine in Query Analyzer on my SQL 7.0
box. Which version of SQL Server do you have?
One thing that could happen is that you would need a GO before this line:
GRANT EXECUTE ON sp_checklogin TO public
Try it out to see.
-----Original Message-----
From: Eric Van Camp [mailto:eric.vancamp@c...]
Sent: Saturday, October 28, 2000 5:10 AM
To: ASP Databases
Subject: [asp_databases] Urgent help wanted with creating a stored
procedure
Importance: High
hi,
i want to run a script for creating the stored procedure sp_checklogin
i I run the script i get the following error:
whilest running a script to create a stored procedure i get the following
error:
Msg 208, Level 16, State 2
Invalid object name 'sp_checklogin'.
what is going on?
the scipt i run in i-sql is the following:
/***************************************************************************
******
* Table : sp_checklogin
*
* Desc : Checks Valid Login
*
****************************************************************************
*****/
CREATE PROCEDURE sp_checklogin
(
@SessionID VARCHAR(40),
@Level VARCHAR(10)
/* Allowed Rights
GENERAL
REPORT
ADMIN
SYSADMIN */
)
AS
DECLARE @CoyID VARCHAR(20)
DECLARE @UserID VARCHAR(20)
DECLARE @AccessNumber TINYINT
DECLARE @Timeout INT
DECLARE @UserState TINYINT
DECLARE @CoyStatus TINYINT
DECLARE @Rights INT
/* Check Session Exists */
IF NOT EXISTS (SELECT SessionID FROM USERLOG WHERE SessionID=@SessionID)
BEGIN
SELECT Progress=1,Errormsg="Session does Not Exist."
RETURN
END
/* Check Session Details if Session Still Valid */
SELECT
@CoyID=CoyID,@UserID=UserID,@AccessNumber=AccessNumber,@Timeout=DATEDIFF(MIN
UTE,Accessdt,GETDATE()),@UserState=UserState FROM USERLOG WHERE
SessionID=@SessionID
IF NOT (@UserState = 0)
BEGIN
SELECT Progress=1,Errormsg="Session has already ended."
RETURN
END
/* Check Session Details if Session Timed Out */
IF @Timeout >= 30
BEGIN
/* Mark Session as Timed Out */
UPDATE USERLOG SET UserState=2 WHERE SessionID=@SessionID
SELECT Progress=1,Errormsg="Session has already timed out."
RETURN
END
/* Check Company Privileges */
SELECT @CoyStatus=Status FROM COMPANY WHERE CoyID=@CoyID
IF (@CoyStatus=0)
BEGIN
SELECT Progress=1,Errormsg="Company In Disabled Mode."
RETURN
END
/* Admin User */
IF (@UserID = "MASTER")
BEGIN
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
/* Other Users */
IF (@Level = "GENERAL")
BEGIN
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
IF (@Level = "REPORT")
BEGIN
SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND UserID=@UserID
IF NOT (@Rights & 1 = 1) /* Logical Operation on Bit 0 */
BEGIN
SELECT Progress=2,Errormsg="User Does Not Have
REPORT rights."
RETURN
END
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
IF (@Level = "ADMIN")
BEGIN
SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND UserID=@UserID
IF NOT (@Rights & 2 = 2) /* Logical Operation on Bit 1 */
BEGIN
SELECT Progress=2,Errormsg="User Does Not Have ADMIN
rights."
RETURN
END
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
IF (@Level = "SYSADMIN")
BEGIN
SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND UserID=@UserID
IF NOT (@Rights & 4 = 4) /* Logical Operation on Bit 2 */
BEGIN
SELECT Progress=2,Errormsg="User Does Not Have
SYSADMIN rights."
RETURN
END
SELECT @AccessNumber = @AccessNumber + 1
UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
SessionID=@SessionID
SELECT Progress=0,CoyID=@CoyID
RETURN
END
SELECT Progress=3,Errormsg="Invalid Operation Requested."
RETURN
GRANT EXECUTE ON sp_checklogin TO public
GO
Message #6 by "Paul" <paul001@b...> on Sun, 29 Oct 2000 10:27:25 -0000
|
|
I think this is the kinda thing I need
Please contact me
Paul
(Please note we prefer to keep discussion on the list for everyone's benefit. - Moderator)
----- Original Message -----
From: "Maxime Bombardier" <BombardierM@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, October 28, 2000 10:57 PM
Subject: [asp_databases] RE: Urgent help wanted with creating a stored pro
cedure
> Well I copy-pasted your code and it ran fine in Query Analyzer on my SQL
7.0
> box. Which version of SQL Server do you have?
>
> One thing that could happen is that you would need a GO before this line:
> GRANT EXECUTE ON sp_checklogin TO public
>
> Try it out to see.
>
>
> -----Original Message-----
> From: Eric Van Camp [mailto:eric.vancamp@c...]
> Sent: Saturday, October 28, 2000 5:10 AM
> To: ASP Databases
> Subject: [asp_databases] Urgent help wanted with creating a stored
> procedure
> Importance: High
>
>
> hi,
> i want to run a script for creating the stored procedure sp_checklogin
> i I run the script i get the following error:
> whilest running a script to create a stored procedure i get the following
> error:
> Msg 208, Level 16, State 2
> Invalid object name 'sp_checklogin'.
> what is going on?
>
> the scipt i run in i-sql is the following:
>
/***************************************************************************
> ******
> * Table : sp_checklogin
> *
> * Desc : Checks Valid Login
> *
>
****************************************************************************
> *****/
> CREATE PROCEDURE sp_checklogin
> (
> @SessionID VARCHAR(40),
> @Level VARCHAR(10)
> /* Allowed Rights
> GENERAL
> REPORT
> ADMIN
> SYSADMIN */
> )
> AS
> DECLARE @CoyID VARCHAR(20)
> DECLARE @UserID VARCHAR(20)
> DECLARE @AccessNumber TINYINT
> DECLARE @Timeout INT
> DECLARE @UserState TINYINT
> DECLARE @CoyStatus TINYINT
> DECLARE @Rights INT
>
> /* Check Session Exists */
> IF NOT EXISTS (SELECT SessionID FROM USERLOG WHERE SessionID=@SessionID)
> BEGIN
> SELECT Progress=1,Errormsg="Session does Not Exist."
> RETURN
> END
>
> /* Check Session Details if Session Still Valid */
> SELECT
>
@CoyID=CoyID,@UserID=UserID,@AccessNumber=AccessNumber,@Timeout=DATEDIFF(MIN
> UTE,Accessdt,GETDATE()),@UserState=UserState FROM USERLOG WHERE
> SessionID=@SessionID
> IF NOT (@UserState = 0)
> BEGIN
> SELECT Progress=1,Errormsg="Session has already ended."
> RETURN
> END
>
> /* Check Session Details if Session Timed Out */
> IF @Timeout >= 30
> BEGIN
> /* Mark Session as Timed Out */
> UPDATE USERLOG SET UserState=2 WHERE SessionID=@SessionID
> SELECT Progress=1,Errormsg="Session has already timed out."
> RETURN
> END
>
> /* Check Company Privileges */
> SELECT @CoyStatus=Status FROM COMPANY WHERE CoyID=@CoyID
> IF (@CoyStatus=0)
> BEGIN
> SELECT Progress=1,Errormsg="Company In Disabled Mode."
> RETURN
> END
>
> /* Admin User */
> IF (@UserID = "MASTER")
> BEGIN
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> /* Other Users */
> IF (@Level = "GENERAL")
> BEGIN
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> IF (@Level = "REPORT")
> BEGIN
> SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
UserID=@UserID
> IF NOT (@Rights & 1 = 1) /* Logical Operation on Bit 0 */
> BEGIN
> SELECT Progress=2,Errormsg="User Does Not Have
> REPORT rights."
> RETURN
> END
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
> WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> IF (@Level = "ADMIN")
> BEGIN
> SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
UserID=@UserID
> IF NOT (@Rights & 2 = 2) /* Logical Operation on Bit 1 */
> BEGIN
> SELECT Progress=2,Errormsg="User Does Not Have ADMIN
> rights."
> RETURN
> END
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
> WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> IF (@Level = "SYSADMIN")
> BEGIN
> SELECT @Rights=Rights FROM USERS WHERE CoyID=@CoyID AND
UserID=@UserID
> IF NOT (@Rights & 4 = 4) /* Logical Operation on Bit 2 */
> BEGIN
> SELECT Progress=2,Errormsg="User Does Not Have
> SYSADMIN rights."
> RETURN
> END
> SELECT @AccessNumber = @AccessNumber + 1
> UPDATE USERLOG SET Accessdt=GETDATE(),AccessNumber=@AccessNumber
> WHERE
> SessionID=@SessionID
> SELECT Progress=0,CoyID=@CoyID
> RETURN
> END
>
> SELECT Progress=3,Errormsg="Invalid Operation Requested."
> RETURN
>
> GRANT EXECUTE ON sp_checklogin TO public
> GO
>
>
>
|
|
|
View Cart
