Long running SP's - sp_prepexec

Hi All:
We have a booking engine for resorts reservations(cold fusion MX and
sql server), our sql server is performing real slow, cpu utilization
is abt 70 -95 % all the time.
Result of Long running SPs contained something like this :
declare @.P1 int set @.P1=NULL declare @.P2 varchar(8000) set @.P2=NULL
exec sp_prepexec @.P1 output, 210, 145, '02/14/04', '02/22/04', 600, 1,
@.P2 output select @.P1, @.P2 151.34 usr001 NULL server_name NULL 65
2003-08-06 11:35:47.733 2003-08-06 11:38:19.077
Please let me know what could be the problem.
Regards
Bhakta RamRam,
Not easy to pinpoint the problem rightaway, especially if its the adhoc
query issue.You can find troubleshooting details here:
HOW TO: Troubleshoot Application Performance with SQL Server
http://www.support.microsoft.com/?id=224587
HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/default.aspx?scid=kb;EN-US;243588
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"BHakta Ram" <bhaktaram@.yahoo.com> wrote in message
news:57bb2f4.0308070727.4a66e989@.posting.google.com...
> Hi All:
> We have a booking engine for resorts reservations(cold fusion MX and
> sql server), our sql server is performing real slow, cpu utilization
> is abt 70 -95 % all the time.
> Result of Long running SPs contained something like this :
> declare @.P1 int set @.P1=NULL declare @.P2 varchar(8000) set @.P2=NULL
> exec sp_prepexec @.P1 output, 210, 145, '02/14/04', '02/22/04', 600, 1,
> @.P2 output select @.P1, @.P2 151.34 usr001 NULL server_name NULL 65
> 2003-08-06 11:35:47.733 2003-08-06 11:38:19.077
> Please let me know what could be the problem.
> Regards
> Bhakta Ram

Login Properties: Deny, Disable, Lock Out? Which do I use?

Hey again!

I have a SQL login, and I want to prevent the login from logging into SQL Server.

From SSMS, I connect to the Database Engine, expand Security, Logins.
I right-click the login in question and select Properties. I select the Status page.

So I see I can deny, disable and lock out this login.
What is the difference between these three options?

Also, shouldn't the checkbox "Login is locked out" be checkable?
Currently, it's unchecked/disabled and I cannot alter this value.

I performed a cross-reference of the possible combinations:

SQL Logins:

FQ Login ID Password Status Enabled? Lock? @.@.ERROR
-- -- --
UserName (valid) Grant Enabled No 0
UserName (valid) Grant Enabled Yes 18486
UserName (valid) Grant Disabled No 18470
UserName (valid) Grant Disabled Yes 18470
UserName (valid) Deny Enabled No 18456
UserName (valid) Deny Enabled Yes 18486
UserName (valid) Deny Disabled No 18470
UserName (valid) Deny Disabled Yes 18470
UserName (garbage) Grant Enabled No 18456
UserName (garbage) Grant Enabled Yes 18456
UserName (garbage) Grant Disabled No 18456
UserName (garbage) Grant Disabled Yes 18456
UserName (garbage) Deny Enabled No 18456
UserName (garbage) Deny Enabled Yes 18456
UserName (garbage) Deny Disabled No 18456
UserName (garbage) Deny Disabled Yes 18456
(garbage) (garbage) Either Either No 18456

NT-Logins:

FQ Login ID Password Status Enabled? Lock? @.@.ERROR
-- -- --
DOMAIN\UserName N/A Grant Enabled N/A 0
DOMAIN\UserName N/A Deny Enabled N/A 18456
DOMAIN\UserName N/A Grant Disabled N/A 18470
DOMAIN\UserName N/A Deny Disabled N/A 18470

where @.@.ERROR
0 = Connects
18456 = Login failed for user 'UserName'.
18470 = Login failed for user 'UserName'. Reason: The account is disabled.
18486 = Login failed for user 'UserName' because the account is currently locked out. The system administrator can unlock it.

(Upon third garbage-password, the SQL-account is locked.)

|||

A plain 18456 "login failed" message could mean
- the user is using an incorrect username
- the user is using an incorrect password
- the user isn't defined to the datasource
- the user is denied access.

Based on these findings, I like the "disabled" setting over "denied." If a user calls me with an 18470 message, I have some confidence in that the user connecting with a valid username and password.