Retrun all rows on Any Table from EM prompts for Username/Password .. Why ?

Hello -

I dont know something weired happened on our MSSQL server today. We are able to connect to any Databases from Enterprise Manager from a Remote server.

Once we logged in and connected to a database on my database server, it keep prompting for the user id and password as we browsing through different tables in that database?

(Right click on the table then select Open Table -> Return all rows -> and it prompts for password )

Now when we enter password the contents of Table are displayed and now when we try to see contents of another table in the database it again prompts for password.

Any Idea whats wrong? How this can be resolved as it was not happening before .. :(Weird, I'd try changing the registration of the server in EP and see if that fixes it.

Retrieving SA password

The people who originally setup the databases on our server have left and didn't pass on the password for the SA username. Is there anyway of extracting the SA password if I have access to SQL Server Enterprise Manager? I want to avoid resetting the password otherwise I the existing programs that rely upon that login name may not function problem. If anyone has any ideas I'm willing to give it a go.

Thanks.Howdy

Firstly - its not good practice - as you have just found - to have sa running any apps - you should change them asap to use another user ID ( and then you wont have this problem again ;-) , but I suspect I am now preaching to the converted....).

Check this out...

http://www.nextgenss.com/products/sqlcrack.htm

Cheers,

SG

retrieve stored procedure

I need some help retrieving the stored procedure listed below. I would like to use a drop list to select the variable "UserName" and textboxes to send the variables "StartingDate" and "EndingDate". How do I pass these variables and then have the results show up in a gridview? Any advice would be greatly appreciated.

CREATE PROCEDURE [dbo].[aspnet_starterkits_GetTimeEntryUserReportByUserNameAndDates]
@.UserName NVARCHAR(256),
@.StartingDate datetime,
@.EndDate datetime
AS
DECLARE @.UserId AS UNIQUEIDENTIFIER
SET NOCOUNT ON

SELECT @.UserId=UserId FROM aspnet_users WHERE UserName=@.UserName

SELECT
@.UserName as UserName,
SUM (timeentryDuration) AS TotalDuration,
SUM (timeentryOvertime) AS TotalOvertimeHours
FROM
aspnet_starterkits_TimeEntry
WHERE
aspnet_starterkits_TimeEntry.TimeEntryUserId=@.UserId
AND
TimeEntryDate between @.StartingDate and @.EndDate

Hello,

Here is a working sample. Hope it can help.

<%@. Page Language="C#" %
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<script runat="server"
protected void Button1_Click(object sender, EventArgs e)
{

GridView1.Visible = true;

}
</script
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<table>
<tr>
<td>
</td>
<td> <asp:DropDownList ID="ddl1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="UserName" DataValueField="UserName">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:aspnet_staterKits_TimeTracker%>"
SelectCommand="SELECT [UserName] FROM [vw_aspnet_Users]"></asp:SqlDataSource>

</td>
<td>
</td>
</tr>
<tr>
<td>T1
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>T2
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /></td>
<td>
</td>
</tr>
</table>
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Visible="false"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
<asp:BoundField DataField="TotalDuration" HeaderText="TotalDuration" SortExpression="TotalDuration" />
<asp:BoundField DataField="TotalOvertimeHours" HeaderText="TotalOvertimeHours" SortExpression="TotalOvertimeHours" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:aspnet_staterKits_TimeTracker %>"
SelectCommand="aspnet_starterkits_GetTimeEntryUserReportByUserNameAndDates2" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter Name="UserName" ControlID="ddl1" />
<asp:ControlParameter Name="StartingDate" ControlID="TextBox1"/>
<asp:ControlParameter Name="EndDate" ControlID="TextBox2"/>
</SelectParameters>
</asp:SqlDataSource>

</div>
</form>
</body>
</html>

retrieve data from a stored procedure

Hello Group
I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can't retrieve the data. Here is my code.

CREATE PROCEDURE stpMyAuthentication
(
@.fldUsername varchar( 50 ),
@.fldPassword Char( 25 )--,
--@.fldFirstName char( 30 ) OUTPUT,
--@.fldLastName char( 30 ) OUTPUT
)
As
DECLARE @.actualPassword Char( 25 )
SELECT
@.actualPassword = fldPassword
FROM
tbMembUsers
Where
fldUsername = @.fldUsername
IF @.actualPassword IS NOT NULL
IF @.fldPassword = @.actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
SELECT
fldFirstName,
fldLastName,
fldPassword
FROM
tbMembUsers
Where
fldUsername = @.fldUsername
GO

############### login page ################

 Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
 If IsValid Then
 If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
 FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
 End If
 End If
 End Sub
 Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
 Dim myConn As SqlConnection
 Dim myCmd As SqlCommand
 Dim myReturn As SqlParameter
 Dim intResult As Integer
 Dim sqlConn As String
 Dim strFirstName, strLastName As String
 sqlConn = ConfigurationSettings.AppSettings("sqlConnStr")
 myConn = New SqlConnection(sqlConn)
 myCmd = New SqlCommand("stpMyAuthentication", myConn)
 myCmd.CommandType = CommandType.StoredProcedure
 myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
 myReturn.Direction = ParameterDirection.ReturnValue
 myCmd.Parameters.Add(Trim("@.fldUsername"), Trim(strUsername))
 myCmd.Parameters.Add(Trim("@.fldPassword"), Trim(strPassword))
 myCmd.Parameters.Add("@.fldFirstName", strFirstName)
 myCmd.Parameters.Add("@.fldLastName", strLastName)
 myCmd.Parameters.Add("@.fldPassword", strPassword)
 myConn.Open()
 myCmd.ExecuteNonQuery()
 intResult = myCmd.Parameters("RETURN_VALUE").Value
 myConn.Close()
 'If strPassword = 55555555 Then
 ' Session("intDefaultPass") = 1
 'End If
 Session("strFullName") = strFirstName & " " & strLastName
 Session("strPassword") = strPassword
 If intResult < 0 Then
 If intResult = -1 Then
 lblMessage.Text = "Username Not Registered!<br><br>"
 Else
 lblMessage.Text = "Invalid Password!<br><br>"
 End If
 End If
 Return intResult
 End Function

At this time I am not getting any errors. How can I retrieve the data that I am after?
Michaeldude
your code is all screwed up. re-work on your logic. you dont need to select the actualpassword in a sep variable and compare it against whats in the variable. you can directly compare it against the value in the table.

i'd would rewrite your query as :

CREATE PROCEDURE stpMyAuthentication
(
@.fldUsername varchar( 50 ),
@.fldPassword varChar( 25 ),
@.fldFirstName varchar(30) OUTPUT,
@.fldLastName varchar( 30 ) OUTPUT
@.intRes int OUTPUT
)
As
SET NOCOUNT OFF
BEGIN
--@.intRes 0=no user. 1=valid. 2=invalid passwd
IF EXISTS ( Select * from tbMembUsers where fldUsername = @.fldUsername and fldPassword=@.fldPassword )
BEGIN
SELECT @.fldFirstName=fldFirstName, @.fldLastName =fldLastName
FROMtbMembUsers
WHEREfldUsername = @.fldUsername AND fldPassword=@.fldPassword
SET@.intRes=1
END
ELSE
BEGIN
-- INVALID PASSWORD
IF EXISTS ( Select * from tbMembUsers where fldUsername = @.fldUsername )
SET @.intRes=2
--No CREDENTIALS FOUND SO RETURN 0
IF EXISTS ( select * From tbMembUsers where fldUsername = @.fldUsername and fldPassword=@.fldPassword )
SET @.intRes=0
END
SET NOCOUNT OFF
END

and from the asp.net page you can use the sample code here to modify it acc to your stored proc.

Dim myCommand As SqlCommand
Dim myParam As SqlParameter
dim resvalue as integer
myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "testmovein_cusinfo"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@.username",SqlDbType.varchar,30))
myCommand.Parameters("@.username").Value = Trim(username)
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.int
mycommand.Parameters.Add(myParam)
Try
If objCon.State = 0 Then objCon.Open()
mycommand.ExecuteNonQuery()
resvalue=convert.toint16((mycommand.Parameters("@.result").Value))
Catch exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
 objCon.Close()
End If
'objCon.Dispose()
End Try

hth|||why do you SET NOCOUNT OFF twice?

and i think you'll confuse him with
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.int
mycommand.Parameters.Add(myParam)
----

mycommand.Paramters.Add("@.Result", SqlDbType.int)
mycommand.Parameters("@.Result").Direction = ParameterDirection.Output

does the same thing. :)|||yeah sorry abt the nocount thing.

theres always more than one way you can do a thing in. i just cut n pasted some template code.|||Thanks guys. I finally got it. Even though this code works just fine, I am wondering if I have some unnecessary code. Can you please check it for me thanks.

 Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
 ' Variable Declaration
 Dim myConn As SqlConnection
 Dim myCmd As SqlCommand
 Dim myParam As SqlParameter
 Dim intResult As Integer
 Dim sqlConn As String
 Dim strFirstName, strLastName As String
 Dim resvalue As Integer
 sqlConn = ConfigurationSettings.AppSettings("sqlDbConn")
 myConn = New SqlConnection(sqlConn)
 myCmd = New SqlCommand("stpMyAuthentication", myConn)
 myCmd.CommandType = CommandType.StoredProcedure
 'output parameter
 myParam = myCmd.CreateParameter()
 myParam.Direction = ParameterDirection.Output
 myParam.SqlDbType = SqlDbType.Int
 myCmd.Parameters.Add(myParam)
 '########################################################
 myCmd = New SqlCommand
 myCmd.Connection = myConn
 myCmd.CommandText = "stpMyAuthentication"
 myCmd.CommandType = CommandType.StoredProcedure
 myCmd.Parameters.Add("@.intRes", SqlDbType.Int)
 myCmd.Parameters("@.intRes").Direction = ParameterDirection.Output
 myCmd.Parameters.Add(Trim("@.fldUsername"), Trim(strUsername))
 myCmd.Parameters.Add(Trim("@.fldPassword"), Trim(strPassword))
 myCmd.Parameters.Add("@.fldFirstName", SqlDbType.VarChar, 30)
 myCmd.Parameters("@.fldFirstName").Direction = ParameterDirection.Output
 myCmd.Parameters.Add("@.fldLastName", SqlDbType.VarChar, 30)
 myCmd.Parameters("@.fldLastName").Direction = ParameterDirection.Output
 '########################################################
 Try
 Response.Write("1<br>")
 If myConn.State = 0 Then myConn.Open()
 myCmd.ExecuteNonQuery()
 resvalue = Convert.ToInt16((myCmd.Parameters("@.intRes").Value))
 strFirstName = myCmd.Parameters("@.fldFirstName").Value
 strLastName = myCmd.Parameters("@.fldLastName").Value
 Session("strFullName") = strFirstName & " " & strLastName
 Catch exc As Exception
 Response.Write(exc)
 Finally
 If myConn.State = ConnectionState.Open Then
 myConn.Close()
 End If
 'objCon.Dispose()
 End Try
 Select Case resvalue
 Case 0
 lblMessage.Text = "Username Not Registered!<br><br>"
 Case 1
 If strPassword = "55555555" Then
 Session("intDefaultPass") = 1
 End If
 Case 2
 lblMessage.Text = "Invalid Password!<br><br>"
 End Select
 Return resvalue
 End Function