Check login

Hello, everyone. I am trying to create a procedure that checks a user's login, in the database. The login is based on e-mail address and password.

How do I construct the stored procedure to check if the username and password are correct?

Thanks,

Antonio

You could either use a stored procedure, or a function.

Code Snippet

CREATE PROCEDURE dbo.VerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)

AS

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

(or)

CREATE FUNCTION dbo.fnVerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)
RETURNS varchar(10)
AS

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

|||

Just a couple of points to make about password storage in databases.

You should never store a password in a clear (unencrypted) form in a database. Preferably you should not even store it in a decryptable form, but rather a value managled by a one-way hash. SQL Server 2005 now has the HashBytes function which will perform a cryptographically valid hash operation.

It is also recommended that you not perform a straightforward hashing but rather salt the value before hashing with another value. This means adding another known piece of information to the value before hashing the combination. This has a 2-fold advantage:

It makes a brute force attack of the hash algorithm with all the possible values harder (basically impossible if the salt is different for each password). Be aware there are projects on the internet where people are building hash lists for all the characters strings up to a certain length composed of a standard set of characters (mainly alpha/digit - upper case only) for some standard hash algorithms. Good reason for using longer passwords (12+ characters) and at least one unusual character. If you use a different salt for each user (I normally use something based upon the primary ID in the User Table) then it prevents the insider attack of moving the password hash from a user whose password is known to another allowing access to the system as that user (restoring the old hash removes signs of the hack). If the salt is held as a field in the table they can move that too (though a unique constraint might make that more difficult).sql

check ident reseed

Reseeding in SQL Server gets different results based on whether the table has ever had data in it.

ref: http://cs.thycotic.net/blogs/bryant_smith/archive/2006/01/17/65.aspx

I am having the same problem. Any suggestions ?

Are you asking how to reset the identity seed count after you delete the data from a table and you want to start over again with a new count? If so here is the code...

Example:

use Database

DBCC CHECKIDENT ('[Scheme].[Table]' , RESEED, 0)

Check box insert question

I have 3 tables - Data, Facility and FacilityKey

The FacilityKey table will hold the Data ID and Facility ID based on a series of check boxes on a form.

My question is what is the best way to do the insert into the FacilityKey table from the form? If 5 facilities are checked I don't want to do 5 separate calls to the database for inserts, but I'm a bit confused on what the best method would be.

Thank you!I can't really think of any other way. You're going to need to call 5 INSERT statements, each inserting a single row. You can use the one connection to do this. To be honest, I don't think SQL Server is going to struggle with this.

Cheers
Ken

Check available Time procedure

Hey Guys,
I hope someone can help on here with this. I have this Database where techs are scheduled and dispatched to perform tasks based on skus. What I am trying to achieve is finding the first available Tech based on their schedule and the appointments table.
Example User enters today's date and 5:30 AM and the search for all available techs to perform that task

the tables ddl is
USE [Schedule]
GO
/****** Object: Table [dbo].[AllDays] Script Date: 05/31/2006 01:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AllDays](
[ID] [int] NOT NULL,
[DayString] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_WorkingDays] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [Schedule]
GO
/***Appointments Table where trouble is *****
/****** Object: Table [dbo].[Appointments] Script Date: 05/31/2006 01:17:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Appointments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Customer_ID] [int] NOT NULL,
[Tech_ID] [int] NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL,
[App_Date] [datetime] NOT NULL,
[Created_By] [int] NOT NULL,
[Date_Created] [datetime] NOT NULL,
[Sku_ID] [int] NOT NULL,
[Comment_ID] [int] NOT NULL,
CONSTRAINT [PK_TechsShifts] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Comments] FOREIGN KEY([Comment_ID])
REFERENCES [dbo].[Comments] ([ID])
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Techs] FOREIGN KEY([Tech_ID])
REFERENCES [dbo].[Techs] ([ID])

USE [Schedule]
GO
/****** Object: Table [dbo].[Schedule] Script Date: 05/31/2006 01:19:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schedule](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Tech_ID] [int] NOT NULL,
[AllDayID] [int] NOT NULL,
[ShiftStartTime] [datetime] NOT NULL,
[ShiftEndTime] [datetime] NOT NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Schedule] WITH CHECK ADD CONSTRAINT [FK_Schedule_AllDay] FOREIGN KEY([AllDayID])
REFERENCES [dbo].[AllDays] ([ID])

Plus the Techs Table which holds their ID's names etc
I have a snapshot (http://webdivisions.net/images/relation.gif) of the relationship posted here if that can help

Thanks for any inputDefine "first available tech".
Does an appointment have to fall completely within a single tech's shift?
Can an appointment span a day?
And why are you using varchar to store date values?

Look, a basic query would look like this:

select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime <= Appointments.EndTime
where not exists
(select *
from Appointments Committed
where Commited.TechID = Schedule.Tech_ID
and ((Committed.ShiftStartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Committed.ShiftEndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Appointments.StartTime BETWEEN Committed.ShiftStartTime and Committed.ShiftEndTime)))|||Thanks for the reply blindman,
What I meant by first tech can be best explained with this scenario.
End user selects date as 06/02/2006 at 10:00 AM then search. the query should return all available techs for that date and period of time and the closest time available if 10:00 AM is not available on that day.
Example*******************
Tech available from date available
Tech1 10:00 AM 06/02/2006
Tech2 11:00 AM 06/02/2006
tech2 9:00 AM 06/02/2006
************************
now as for your question
>Does an appointment have to fall completely within a single tech's shift?
Yes, all techs will be required to reschedule their appointment if they cant finish them on day assigned.
Can an appointment span a day?
No they shouldnt, its just not within the nature of the business to do this.
And why are you using varchar to store date values?
I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.

I hope that answers your questions.

Thanks for your time|||...
And why are you using varchar to store date values?
I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.

"[DayString] [varchar](20) "??

Will the logic I gave you in my sample code work for your situation?|||"[DayString] [varchar](20) "??
Oh this table hold an integer representing the day and DayString is the day itself.
**************************
ID DayString
1 Sunday
2 Monday
**************************
I tried to run the SQL the following

select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime <= Appointments.EndTime
where not exists
(select *
from Appointments Committed
where Committed.Tech_ID = Schedule.Tech_ID
and ((Committed.StartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Committed.EndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Appointments.StartTime BETWEEN Committed.StartTime and Committed.EndTime)))

the result was no records. Am I missing something here?
Thanks for your help|||Logic error. Should have been:
select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime >= Appointments.EndTime
.........to find shifts that bracket the appointments.|||Thanks Blindman :-)
After the I changed the script to what you recommended, The statements returned one row.
Now the stupid question, Is it possible to return the top 1 then the closest available techs as well?
IE, If user enters 12/02/2007 10:30 AM and we have tech1 who starts at 9:30 AM on that day and have no appointment but tech2 starts 12:00 PM. and have an appointment starting 12/02/2007 1:30 PM and ending 12/02/2007 4:00 PM. is it possible to list tech2 with his/her availablity. this way the user doesnt have to go back and query the database again by changing date and time.

second stupid question and that is related to my lack of experience in SQL is how can send this SP the date and time to look for.

Thank you very much for your help.|||In answer to your first question, just about anything can be done in SQL. But you are getting into some fairly complex business logic that is particular to your application. It will require a substantial amount of effort in requirements gathering and programming to get this right. You either need to get educated on SQL fast, or hire a contractor to do the work for you.

To answer your second question, the datetime value can be sent to the stored procedure as a parameter. Please read about stored procedures in Books Online.|||Thank you for all your help blindman
You have been great

I'll be hitting the book store today!|||Hit Books Online first. It's free.

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURS
have a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:

> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS
|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:
> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.

check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:

> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.

Charts no longer showing on report?

Hi, I have 8 charts on a report that no longer show up on the report.

the datasets they are based on still produce data when queried.

All I see is white space plus the textbox titles?

The charts are there in layout view and still correctly reference the right datasets and fields.

figured it out, one field was 1 char longer than I thought rtrim sorted the problem

Charting problem

Hi all. I have plotted a chart based on the data obtained. However, is there a way to limit the number of bars to be displayed in the chart?
Lets say, I have data from Jan to Dec, i needed all the data because i need the accumulated figures from each month.
But, I do not wish to display in the bar chart for data from Jan to Dec.
There are section in the detail for hiding some data, but I dont know about chart.
Any comments are appreciated!Well, give us an example - what period do you wish to chart on, if it's not Jan-Dec??|||Lets say the data needed is from Jan to Dec, because I need to calculate the running total for the accumulated figures for other processing purposes.
Then for the chart, I would like to plot the data from Sept to Dec only. Is it possible?
The date field is used as a criteria for grouping.

Changing the width of table column based on data returned

I would like to size the width of a table column to be big enough for the
data being returned. I don't seem to be able to use and expression for the
width parameter. How might I dynamically resize the with of table columns?THere is a property of a textbox which will allow its size to grow to fit
the text value... Just check the box...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:D3F216B4-9589-44D6-96F4-4F55DBFB34DE@.microsoft.com...
>I would like to size the width of a table column to be big enough for the
> data being returned. I don't seem to be able to use and expression for
> the
> width parameter. How might I dynamically resize the with of table
> columns?|||Greg,
The only thing I've seen so far in all my playing around is a height
increase/decrease, not a width version. Rightclick the field in question,
and choose properties. On the first tab is "Can increase to accomodate
contents" and "Can decrease to accomodate contents". I don't know why MS
doesn't have a dynamic width increase. Maybe the Dev team didn't put it in
so it wouldn't mess with the column structure.
Catadmin
"Greg Larsen" wrote:
> I would like to size the width of a table column to be big enough for the
> data being returned. I don't seem to be able to use and expression for the
> width parameter. How might I dynamically resize the with of table columns?