how to create my Validate Login SP IN sql 2000 ?

hi,all:

I'm new to Sql 2000,now I have a login asp.net page and I used the sql 2000 database.

my login page included a user id and password need user inputed. if the user input the correct userid

and password ,IE will transfer to main page,or there will show eorror message in login page.

my SP like this:

CREATE PROCEDURE dbo.Usp_Accounts_ValidateLogin
@.userid char(4) ,
@.EncPassword binary
AS
if (select count(*) from hhmxUserData whereUserid=@.userid andUserPWD=@.EncPassword) >0

return 1

else

return 0
GO
my asp.net code like this:

dim result As Integer

dim rowsAffected as integer

myConnection.Open()
Dim command As SqlCommand = BuildIntCommand(storedProcName, parameters)
rowsAffected = command.ExecuteNonQuery
result = CInt(command.Parameters("ReturnValue").Value)
myConnection.Close()

Return result

I test it in sql 2000,it's ok.but when I performed it and retrieve the "returnValue", it still return 0 .

so how can I create my correct SP ?

thanks so much.

Change your datatype of userid from char(4) to varchar(4). When you use CHAR, if your user enter the userid which is less than 4 characters in length spaces will be added at the end and your search query may not look like what is should be.

|||

Yes,my UserID is fixed 4 characters length (like: 1012,1008),I think it's not problem,the point is I'm always get the wrong returnValue in my asp.net code when I performed my SP.

So maybe my SP has problem ?

|||

I would recommend you use OUTPUT parameters. check out Books On Line for more info.

check the 2nd part inthis articleto retrieve the output from OUTPUT variable.

How to create login with default database

Hi,

I can't performe this:

use databaseX

CREATE LOGIN [abc]

WITH PASSWORD ='xxxxxxxxxxxxx',

DEFAULT_DATABASE = db_name()

or

DEFAULT_DATABASE = [ db_name() ] as well

I know that

DEFAULT_DATABASE = databaseX

works, but how can I create login with default database with db_name() ?

thanks,

You have to use dynamic SQL to form the CREATE LOGIN statement and execute it. Most DDL parameters only support literals or variables as values. Ex:

declare @.db nvarchar(130)

set @.db = quotename(db_name())

exec ('create login [abc] with password = ''xxxxx'', default_database = ' + @.db)

|||

Or, you could use sp_defaultdb (note that this sp is deprecated and may be removed from future versions according to MS):

USE MyDb
go

CREATE LOGIN [abc]
WITH PASSWORD ='xxxxxxxxxxxxx';

declare @.db sysname;

set @.db = db_name();

exec sp_defaultdb [abc], @.db

|||

thank you guys,

Can I execute this command from V.B, PowerBuilder or Delphi as well " by ODBC " ?

Or can I just do from Sql environment?

thanks,

|||

Hi Alessandro,

You can execute this from VB/.NET as well. For example, you can execute it against a valid ado connection object, via smo etc.

Cheers

Rob

How to create database having userid and password

Hi,
I am using the sql server for creating the database. I want to create the database which is having userid and password. This I require since I wanted to restrict acces to database and also only authorise user to database can use the userid and password created at time of creation of database.
So how can I create the database which is having userid and password?The situation you mention is not possible. SQL Server datafiles have to be secured at OS level, if not they can be deattached, copied and attached to any other instance.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

But I think that the user who creates the database has the right to decide the users of the database. Or can u give any alternative way to protect the database from other users.

|||

See... that is how SQL Server Security Model works... SQL Server has two level security ... One at the serverl level (LOGIN) and one at the database Level (USER)... If u have access to server(LOGIN) it does not mean that u have access to all the databases... No... u just cant do anything if u have only login... these login has to have permission(added) to a database to access the database... The only thing is that how u understand and interpret the architecture..

MikeRt wrote:

Hi,

But I think that the user who creates the database has the right to decide the users of the database. Or can u give any alternative way to protect the database from other users.

just don't add any other user to the database ... then only the owner will have the permission... read about dabase role /server role in BOL... This is a normal requirement and u can do this simply setting user/login in right role...

Madhu

|||

Thanks for clerifying the concept.

But to access the master database 'sa ' is the universally fix login. U can access the Master database using 'sa' login. Then u can create the database.

So I want to give the database I(application) created userid and password so that even if sql server is having some login id and password The application can access the newly created database using its own id and password.This also doesn't warrent application to know the login and password for sql server.And application works independently. Can it be possible and how?

MikeRT

how to create Crystal Report 7 from password protected Access 97

Dear Mr.Babu (Qmax) and All,

I am using Crystal Report 7 and MS Access 97 with VB6.0.
and I am unable to generate report if Access is password protected.

I have seen ur below code for the above problm in dev-archive

crCommon.LogonInfo(0)= "dsn=;uid=;pwd=MyPassword;dsq="
crCommon.DataFiles(0) = "C:\My Document\db.mdb"

but LogonInfo() is not available in my VB6.0.?

Pls. help me to solve this.

thanks and Regards,
V.K.VenkatesanDear Mr.Babu (Qmax) and All,

Finally, I have solved my problem... just by reinstalling VB6.0...

Thanks and Regards,
Venkat

How to create a login userid and password for Ms SQL Server 2005 Express?

My PC is Window XP Pro and I'm using Microsoft SQL Server 2005 Express and Microsoft SQL Server Management Studio Express.

My question is how to create a login userid and password under "SQL Server Authentication"? (as shown inhttp://www.findingsteve.net/print_screen.jpg)

Any tutorial about this I can read?

by using

Aspnet_regsql.exe

and here how to use ithttp://msdn2.microsoft.com/en-us/library/ms229862(VS.80).aspx

hope this well help

|||

Hi mobi88, thanks for the reply.

Just wondering is your way the only way to create a user account for "SQL Server Authentication"?

Can that be done in Microsoft SQL Server Management Studio Express? Sorry if I ask stupid question. I just have no idea how it works.

When I choose "Window Authentication", I don't need to key in any username and password. So, I press Connect. And I went to "Security" > "Logins" to create a user id. Then, I went to login using that user id but it doesn't work. It says,

Cannot connnect to ...

Additional information:
--> Login failed for user 'xxx'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

Any guide?

|||

sorry for the misunderstanding. the aspnet_regsql is to create the membership tables in side you database; now i think you have a problem with making connection with the DB. here if you want to connect with the DB with a user that you defined you must add this user your DB security directory, and give that user the permissions to connect and write to DB ( you might give the user DBOWNER permission, as you like) and after that you can connect your db with that user id as trusteed connection (for more information) when you use the windows authentication, you will be known as trusted user and the connection will be opened directly because your machine is a trusted user over the sql server!!.

to add a user to your db go to the security folder under the root of your sql server and add a new user there. and at the same screen from the user maping you can assign the new user to the DB's that you want and give the needed permissions

hope that will help.

|||

I tried to do so but still can't. i even tried a user called "sa" but also received the same error mesg. Do I need to do anything with "Server type" and "Server name"?

Currently, my "Server type" is "Database engine" and my "Server name" is "ABC/SQLEXPRESS". ABC is my computer name.

Edited note: Thanks for the link. I already have my connection string in my web.config. Now, I just need to know how to create the userid I key in in web.config in SQL Server 2005.

|||

the connection string must look like this

<connectionStrings>
<add name="PolKitap.Properties.Settings.mylibraryConnectionString"
connectionString="Server=.\SQLEXPRESS;Database=pollibDB;Uid=username;Pwd=password;"
providerName="System.Data.SqlClient" />
</connectionStrings>

but you must make sure that the user is assigned to the db as DBOWNER. and how to add new user to the db;

checkthis site

hope this will help this time :D

|||

I think I still can't get it. But, thanks for all your help.