sql login permissions: doubt

Hi !
Is it possible to backup (and restore) logins (no database users)? I've this doubt because if I backup a db (Db1) from SQLserver1 and then I restore it in other server (SQLserver2) without all logins than SQLserver1, results in a problem with user access to database.
Any comments? thanks in advance.Have a look here:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Han" <Han@.discussions.microsoft.com> wrote in message
news:9245067A-245E-41A4-97F8-4FB7F51A03CD@.microsoft.com...
> Hi !
> Is it possible to backup (and restore) logins (no database users)? I've
this doubt because if I backup a db (Db1) from SQLserver1 and then I restore
it in other server (SQLserver2) without all logins than SQLserver1, results
in a problem with user access to database.
> Any comments? thanks in advance.
>

In my last post I was having doubt in Registration form and obviously I'm having doubt now in Login form. I can write stored procedure if Login Control is used, but I've created Login form manually and dunno how to do, but I've tried some. Can any one help me?

Login.aspx

 "LoginUserNameLabel" runat="server" Font-Bold="True" Style="z-index: 106; left: 625px; position: absolute; top: 179px" Text="Username:" Font-Size="Small"> "LoginPasswordLabel" runat="server" Font-Bold="True" Style="z-index: 107; left: 625px; position: absolute; top: 225px" Text="Password:" Font-Size="Small"> "LoginUserNameTextBox" runat="server" Style="z-index: 108; left: 696px; position: absolute; top: 175px" BorderStyle="Solid"> "LoginPasswordTextBox" runat="server" TextMode="password" Style="z-index: 109; left: 695px; position: absolute; top: 222px" BorderStyle="Solid"> "LoginRememberMeCheckBox" runat="server" Style="z-index: 110; left: 624px; position: absolute; top: 276px" Text="Remember me next time." Font-Size="Small" /> "LoginSubmitButton" runat="server" CausesValidation="true" Style="z-index: 111; left: 786px; position: absolute; top: 288px" Text="Login" Width="65px" BackColor="Transparent" BorderColor="Black" BorderStyle="Solid" ForeColor="Black" OnAuthenticate="Login_Authenticate" />

Login.aspx.cs:

protected void LoginSubmitButton_Click(object sender, EventArgs e) {bool authenticated;string uname, pwd; uname = LoginUserNameTextBox.ToString() ; pwd = LoginPasswordTextBox.ToString(); authenticated = SiteLevelCustomAuthenticationMethod(LoginUserNameTextBox.ToString(), LoginPasswordTextBox.ToString());//e.authenticated;if (authenticated ==true) { Response.Redirect("Safety.aspx"); } }private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox) {bool boolvalue =false; SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI"); SqlCommand cmd =new SqlCommand("usp_ulogin",conn); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader dr; conn.Open(); dr = cmd.ExecuteNonQuery();while(dr.Read()) {if((LoginUserNameTextBox==dr["uname"].ToString())&(LoginPasswordTextBox==dr["pwd"].ToString())); { boolvalue=true; } dr.Close();return boolvalue; conn.Close(); } }}

Stored Procedure:

select * from mytable

create procedure usp_ulogin

@.uname varchar(30),
@.pwd varchar(20)

select uname,pwd from mytable
where
uname=@.uname and
pwd=@.pwd

Is there any thing that I should correct in my code or my code is itself wrong. And can any one explain me what is happening in the code, because I'm little week in c# ;-)

private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox)
{
bool boolvalue =false;
SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI");
SqlCommand cmd =new SqlCommand("usp_ulogin",conn);

SqlParameter uname = new SqlParameter("@.uname", SqlDbType.Varchar,30);

uname.Value=LoginUserNameTextBox;

cmd.Parameters.Add(uname);

SqlParameter pwd = new SqlParameter("@.pwd", SqlDbType.Varchar,20);

pwd.Value=LoginPasswordTextBox;

cmd.Parameters.Add(pwd);

cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr;
conn.Open();

dr =cmd.ExecuteReader()
while(dr.Read())
{
if((LoginUserNameTextBox==dr["uname"].ToString())&(LoginPasswordTextBox==dr["pwd"].ToString()));
{
boolvalue=true;
}
dr.Close();
return boolvalue;
conn.Close();
}
}
}

my brother if ur using DataReader class use com.executereader() method rather than ExecuteNonquery

second thing u should provide value to the stored procedure like this

first u should create object of sqlparameter

here 2 objects are created bcz ur stored procedure have 2 parametere having same datatype and size of sqlparameter object which u have taken in stored procedure

SqlParameter uname = new SqlParameter("@.uname", SqlDbType.Varchar,30);

uname.Value=LoginUserNameTextBox;

cmd.Parameters.Add(uname);

SqlParameter pwd = new SqlParameter("@.pwd", SqlDbType.Varchar,20);

pwd.Value=LoginPasswordTextBox;

cmd.Parameters.Add(pwd);

i have chnged ur code appropriately above it may work for u

check out ur stored procedure once more

|||

Hai,

I did as you said, but getting 2 errors:

Error 1 'System.Data.SqlDbType' does not contain a definition for 'Varchar'

Error 2 'System.Data.SqlDbType' does not contain a definition for 'Varchar'

My Code is Below:

protected void LoginSubmitButton_Click(object sender, EventArgs e) {bool authenticated;string uname, pwd; uname = LoginUserNameTextBox.ToString(); pwd = LoginPasswordTextBox.ToString(); authenticated = SiteLevelCustomAuthenticationMethod(LoginUserNameTextBox.ToString(), LoginPasswordTextBox.ToString());//e.authenticated;if (authenticated ==true) { Response.Redirect("Safety.aspx"); } }private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox) {bool boolvalue =false; SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI"); SqlCommand cmd =new SqlCommand("usp_ulogin",conn);SqlParameter uname =new SqlParameter("@.uname", SqlDbType.Varchar,30);uname.Value=LoginUserNameTextBox;cmd.Parameters.Add(uname);SqlParameter pwd =new SqlParameter("@.pwd", SqlDbType.Varchar,20);pwd.Value=LoginPasswordTextBox;cmd.Parameters.Add(pwd); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader dr; conn.Open(); dr =cmd.ExecuteReader();while(dr.Read()) {if((LoginUserNameTextBox==dr["uname"].ToString())&(LoginPasswordTextBox==dr["pwd"].ToString())) { boolvalue=true; } dr.Close();return boolvalue; conn.Close(); } }

I'm really confused!

|||

If u r using VS2005 u have to write like this

SqlParameter pwd =new SqlParameter("@.pwd", Data.SqlDbType.Varchar,20);

and remainig thing is ok

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Yes I'm using VS 2005. The application is executing correctly, but it is not redirected to "Terms.aspx" page. I dunno whether the boolean is returning correct value.

protected void LoginSubmitButton_Click(object sender, EventArgs e)
 {
bool authenticated =false;
string uname, pwd;
 uname = LoginUserNameTextBox.ToString();
 pwd = LoginPasswordTextBox.ToString();
 authenticated = SiteLevelCustomAuthenticationMethod(LoginUserNameTextBox.ToString(), LoginPasswordTextBox.ToString());
//e.authenticated; -- //what is this used for. I've used this for Login Control. But here, is it necessary?if (authenticated ==true)
 {
 Response.Redirect("Terms.aspx");
 }
 }private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox)
 {
bool boolvalue =false;
 SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI");
 SqlCommand cmd =new SqlCommand("usp_ulogin", conn);
//SqlParameter uname = new SqlParameter("@.uname", System.Data.SqlDbType.Varchar,30); SqlParameter uname =new SqlParameter("@.uname", System.Data.SqlDbType.VarChar, 30);
 uname.Value = LoginUserNameTextBox;
 cmd.Parameters.Add(uname);
 SqlParameter pwd =new SqlParameter("@.pwd", System.Data.SqlDbType.VarChar, 20);
 pwd.Value = LoginPasswordTextBox;
 cmd.Parameters.Add(pwd);
 cmd.CommandType = CommandType.StoredProcedure;
 SqlDataReader dr;
 conn.Open();
 dr = cmd.ExecuteReader();
while (dr.Read())
 {
if ((LoginUserNameTextBox == dr["@.uname"].ToString()) & (LoginPasswordTextBox == dr["@.pwd"].ToString()))
 {
 boolvalue =true;
//return boolvalue; }else { LoginLoginFailureLabel.Text ="Login failed";
 }
 dr.Close();
//return boolvalue; conn.Close(); }return boolvalue;
//conn.Close(); }

|||

any idea?

|||

U applied executereader. Actually u have to use executenonquery.Plz check out.

u may use execute reader also. I dont no wy u applied while here.

U can write sql stmt like this

This is in stored procedure

Select user,pwd from users where user=@.user and pwd=@.pwd

In .net u have to add the parameters like this. i dont no c# i m giving u in vb.net

cmd.parameter.add("@.uid",data.sqldbtype.varchar,30).value=txtuser.text

cmd.parameter.add("@.pwd",data.sqldbtype.varchar,20).value=txtpwd.text

Plz gothrough it sure u will get it.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

I'm really getting confused with this. Every time when I change something, I get error.

When I change cmd.ExecuteReadyer() tocmd.ExecuteNonQuery(), I gett error as Cannot implicitly convert type 'int' to 'System.Data.SqlClient.SqlDataReader'

Here is my code again. I dunno whether the logic works correct or not. The application is executing. I'm entering the correct username and password in the text box, it shows the same Login.aspx page only. Its not redirecting to Terms.aspx.

protected void LoginSubmitButton_Click(object sender, EventArgs e) {bool authenticated =false;string uname, pwd; uname = LoginUserNameTextBox.ToString(); pwd = LoginPasswordTextBox.ToString(); authenticated = SiteLevelCustomAuthenticationMethod(LoginUserNameTextBox.ToString(), LoginPasswordTextBox.ToString());//e.authenticated;if (authenticated ==true) { Response.Redirect("Terms.aspx"); } }private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox) {bool boolvalue =false; SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI"); SqlCommand cmd =new SqlCommand("usp_ulogin", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter unamepar =new SqlParameter("@.uname", System.Data.SqlDbType.VarChar); SqlParameter pwdpar =new SqlParameter("@.pwd", System.Data.SqlDbType.VarChar); unamepar.Direction = ParameterDirection.Input; pwdpar.Direction = ParameterDirection.Input; unamepar.Value = LoginUserNameTextBox; pwdpar.Value = LoginPasswordTextBox; SqlDataReader dr; conn.Open(); dr = cmd.ExecuteNonQuery();while (dr.Read()) {if ((LoginUserNameTextBox == dr["uname"].ToString()) & (LoginPasswordTextBox == dr["pwd"].ToString())) { boolvalue =true; } dr.Close(); conn.Close(); }return boolvalue; }

|||

Finally I got it resolved. Actually I didn't add the parameter to the command. And I didn't pass the text value, that is the reason I was not able to redirect to
"terms.aspx" file. Anyhow thanks for all of them who helped me a lot. I really gained many things from you people.

Here is the corrected code in C#:

private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox)
 {
bool boolvalue =false;
 SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI");
 SqlCommand cmd =new SqlCommand("usp_ulogin", conn);
 cmd.CommandType = CommandType.StoredProcedure;
 SqlParameter unamepar =new SqlParameter("@.uname", System.Data.SqlDbType.VarChar);
 SqlParameter pwdpar =new SqlParameter("@.pwd", System.Data.SqlDbType.VarChar);
 unamepar.Direction = ParameterDirection.Input;
 pwdpar.Direction = ParameterDirection.Input;
 unamepar.Value = LoginUserNameTextBox;
 pwdpar.Value = LoginPasswordTextBox;
 cmd.Parameters.Add(unamepar);<-- I didn't add the parameter earlier to the command
 cmd.Parameters.Add(pwdpar);<-- I didn't add the parameter earlier to the command
 SqlDataReader dr;
 conn.Open();
 dr = cmd.ExecuteReader();
while (dr.Read())
 {
if ((LoginUserNameTextBox == dr["uname"].ToString()) & (LoginPasswordTextBox == dr["pwd"].ToString()))
 {
 boolvalue =true;
 }
 }
return boolvalue;
 dr.Close();
 conn.Close(); }
protected void LoginSubmitButton_Click1(object sender, EventArgs e)
 {
bool authenticated =false;
string uname, pwd;
 uname = LoginUserNameTextBox.Text.ToString();<-- And this is another error which I did
 pwd = LoginPasswordTextBox.Text.ToString();<-- And this is another error which I did
 authenticated = SiteLevelCustomAuthenticationMethod(uname,pwd);
//e.authenticated;if (authenticated ==true)
 {
 Response.Redirect("Terms.aspx");
 }
else { LoginLoginFailureLabel.Text ="Username or Password incorrect";
 }
 }

|||

I think u didnt check my code properly. Clearly i mentioned the parameters coding. Plz check out

cmd.parameter.add("@.uid",data.sqldbtype.varchar,30).value=txtuser.text

cmd.parameter.add("@.pwd",data.sqldbtype.varchar,20).value=txtpwd.text

Thank u

Baba

|||

Yes I checked your answer, but I dunno vb.net that much. But guessed that I made some mistake by seeing your code. Any way, thanks a lot for the help. I'll try to work in vb.net also. Your reply was very worth. Thanks.

Friday, March 9, 2012

Logins (Sessions)

Logins (Sessions)

Logins (Sessions)

Login with stored procedure

sql login permissions

Blog Archive

About Me