Creating a random password.

I'm new to SQL and have no training. Just trying to learn this on my
own.
I've got a system that grabs new users from 1 table and inserts them
into a user table for a diff. system. I need to be able to assign them
a random password. I've got code that will do this using NEWID() and
RAND() functions and works fine in Query analyser. But if I try to
write a function, I find I can't have NEWID() and RAND() in a UDF. I
can write this as a Procedure, but can't figure out complete syntax of
Proc. or how to call it in my SQL update command.
SQL update command is:
UPDATE clientaccess SET password= dbo.makepassword()
WHERE password = ''
of course, that's calling it as a function. How do I call a Proc in
same UPDATE code?
Here's code as function. How would I change to proc and call?
CREATE function dbo.GenerateRandomString ()
RETURNS char(8)
As
Begin
declare @.password char(8)
declare @.choices varchar(100)
declare @.count int
set @.choices = ''
-- load up numbers 0 - 9
set @.count = 48
while @.count <=57
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
-- load up lowercase letters a - z
set @.count = 97
while @.count <=122
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
set @.count = 0
set @.password = ''
while @.count <= 8
begin
set @.string = @.string +
SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
as int)%LEN(@.choices)+1,1)
set @.count = @.count + 1
end
RETURN(@.password)
endTry putting the UPDATE in the proc definition and passing the account and
password as parameters. Note however that the end-user should change the
password (sp_password) ASAP as this approach is not very secure. Also, just
as an added "slight" layer of security , I'd recommend you use WITH
ENCRYPTION for the proc.
HTH
Jerry
<jfeldbruegge@.yahoo.com> wrote in message
news:1129045975.003145.224220@.g44g2000cwa.googlegroups.com...
> I'm new to SQL and have no training. Just trying to learn this on my
> own.
> I've got a system that grabs new users from 1 table and inserts them
> into a user table for a diff. system. I need to be able to assign them
> a random password. I've got code that will do this using NEWID() and
> RAND() functions and works fine in Query analyser. But if I try to
> write a function, I find I can't have NEWID() and RAND() in a UDF. I
> can write this as a Procedure, but can't figure out complete syntax of
> Proc. or how to call it in my SQL update command.
> SQL update command is:
> UPDATE clientaccess SET password= dbo.makepassword()
> WHERE password = ''
> of course, that's calling it as a function. How do I call a Proc in
> same UPDATE code?
> Here's code as function. How would I change to proc and call?
> CREATE function dbo.GenerateRandomString ()
> RETURNS char(8)
> As
> Begin
> declare @.password char(8)
> declare @.choices varchar(100)
> declare @.count int
> set @.choices = ''
> -- load up numbers 0 - 9
> set @.count = 48
> while @.count <=57
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> -- load up lowercase letters a - z
> set @.count = 97
> while @.count <=122
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> set @.count = 0
> set @.password = ''
> while @.count <= 8
> begin
> set @.string = @.string +
> SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
> as int)%LEN(@.choices)+1,1)
> set @.count = @.count + 1
> end
> RETURN(@.password)
> end
>|||Hi
NewID is a nondeterministic function and can't be used in a function, RAND
seems to cause issues even though it has a seed. To overcome this problems
you could pass a NEWID and Random number to the function, but that would be
less secure than using a different one for each character.
One solution when using a procedure is to loop or cursor for each row to
update:
CREATE PROCEdURE dbo.GenerateRandomString (@.password VARCHAR(8) OUTPUT)
AS
BEGIN
declare @.choices varchar(100)
declare @.count int
set @.choices = ''
-- load up numbers 0 - 9
set @.count = 48
while @.count <=57
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
-- load up lowercase letters a - z
set @.count = 97
while @.count <=122
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
set @.count = 0
set @.password = NULL
while @.count <= 8
begin
set @.password = ISNULL(@.password,'') +
SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
as int)%LEN(@.choices)+1,1)
set @.count = @.count + 1
end
END
-- Example table
CREATE TABLE clientaccess ( username varchar(40) NOT NULL, password char(8)
NOT NULL DEFAULT ('') )
-- Example data
INSERT INTO clientaccess ( username ) values ( 'john' )
INSERT INTO clientaccess ( username ) values ( 'jim' )
INSERT INTO clientaccess ( username ) values ( 'jack' )
SELECT * FROM clientaccess
DECLARE @.password varchar(8), @.username varchar(40)
DECLARE user_cursor CURSOR FOR
SELECT username
FROM clientaccess
WHERE ISNULL(password,'') = ''
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @.username
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.password = ''
EXEC dbo.GenerateRandomString @.password OUTPUT
UPDATE clientaccess
SET password = @.password
WHERE CURRENT OF user_cursor
FETCH NEXT FROM user_cursor INTO @.username
END
CLOSE user_cursor
DEALLOCATE user_cursor
GO
SELECT * FROM clientaccess
John
"jfeldbruegge@.yahoo.com" wrote:

> I'm new to SQL and have no training. Just trying to learn this on my
> own.
> I've got a system that grabs new users from 1 table and inserts them
> into a user table for a diff. system. I need to be able to assign them
> a random password. I've got code that will do this using NEWID() and
> RAND() functions and works fine in Query analyser. But if I try to
> write a function, I find I can't have NEWID() and RAND() in a UDF. I
> can write this as a Procedure, but can't figure out complete syntax of
> Proc. or how to call it in my SQL update command.
> SQL update command is:
> UPDATE clientaccess SET password= dbo.makepassword()
> WHERE password = ''
> of course, that's calling it as a function. How do I call a Proc in
> same UPDATE code?
> Here's code as function. How would I change to proc and call?
> CREATE function dbo.GenerateRandomString ()
> RETURNS char(8)
> As
> Begin
> declare @.password char(8)
> declare @.choices varchar(100)
> declare @.count int
> set @.choices = ''
> -- load up numbers 0 - 9
> set @.count = 48
> while @.count <=57
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> -- load up lowercase letters a - z
> set @.count = 97
> while @.count <=122
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> set @.count = 0
> set @.password = ''
> while @.count <= 8
> begin
> set @.string = @.string +
> SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
> as int)%LEN(@.choices)+1,1)
> set @.count = @.count + 1
> end
> RETURN(@.password)
> end
>

Creating a Password Retrieval Stored Procedure

I would like to create a stored procedure to pass a login name and password
to and have it verify the info and return a few fields of data back to the
calling function from a javascript page. I have tried a few things but can'
t
seem to verify the data and return a value to verify the data.One method is to use NOT EXISTS:
CREATE PROC MyProc
@.UserID varchar(128),
@.Password varchar(128)
AS
SET NOCOUNT ON
IF NOT EXISTS
(
SELECT MyData
FROM Users
WHERE UserID = @.UserID AND
Password = @.Password
)
BEGIN
RAISERROR ('Invalid user or password', 1, 0)
END
SELECT MyData FROM MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"EvanK" <EvanK@.discussions.microsoft.com> wrote in message
news:210A9660-5874-4AA0-9DF9-1CFE9487868B@.microsoft.com...
>I would like to create a stored procedure to pass a login name and password
> to and have it verify the info and return a few fields of data back to the
> calling function from a javascript page. I have tried a few things but
> can't
> seem to verify the data and return a value to verify the data.|||I changed the code as follows:
CREATE PROCEDURE dbo.MM_Chk_User
@.UserID varchar(128),
@.Password varchar(128)
AS
SET NOCOUNT ON
IF NOT EXISTS
(
SELECT First_Name, Last_Name
FROM LogonNames
WHERE Name = @.UserID AND
Password = @.Password
)
BEGIN
RAISERROR ('Invalid user or password', 1, 0)
END
SELECT First_Name, Last_Name
FROM LogonNames
WHERE Name = @.UserID AND
Password = @.Password
GO
I get the following error
[SQL Server]Invalid value 0 for state. Valid ranges is from 1 to 127
"Dan Guzman" wrote:

> One method is to use NOT EXISTS:
> CREATE PROC MyProc
> @.UserID varchar(128),
> @.Password varchar(128)
> AS
> SET NOCOUNT ON
> IF NOT EXISTS
> (
> SELECT MyData
> FROM Users
> WHERE UserID = @.UserID AND
> Password = @.Password
> )
> BEGIN
> RAISERROR ('Invalid user or password', 1, 0)
> END
> SELECT MyData FROM MyTable
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "EvanK" <EvanK@.discussions.microsoft.com> wrote in message
> news:210A9660-5874-4AA0-9DF9-1CFE9487868B@.microsoft.com...
>
>

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?
In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change. A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:

> Is there a way to create a login and password, or change a login's password
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?
|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
[vbcol=seagreen]
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change. A
> dialog box will open and you can change the password in there, near the top.
> Type in the password in the Password box, and the same password again in the
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have a
password.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
[vbcol=seagreen]
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change. A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
> Is there a way to create a login and password, or change a login's password
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change. A
> dialog box will open and you can change the password in there, near the top.
> Type in the password in the Password box, and the same password again in the
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
> > Is there a way to create a login and password, or change a login's password
> > in management studio (sql 2005) without using a sql script? Any wizard or
> > dialog box?|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have a
password.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:
> > In the Management Studio, open the Object Explorer and expand your Server,
> > then Security, then Logins and right-click on the login you want to change. A
> > dialog box will open and you can change the password in there, near the top.
> > Type in the password in the Password box, and the same password again in the
> > Confirm Password box.
> >
> >
> > --
> > AndyP,
> > Sr. Database Administrator,
> > MCDBA 2003
> >
> >
> > "mkiger" wrote:
> >
> > > Is there a way to create a login and password, or change a login's password
> > > in management studio (sql 2005) without using a sql script? Any wizard or
> > > dialog box?

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change.
A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:

> Is there a way to create a login and password, or change a login's passwor
d
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
[vbcol=seagreen]
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change
. A
> dialog box will open and you can change the password in there, near the to
p.
> Type in the password in the Password box, and the same password again in t
he
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
>|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way
.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have
a
password.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
[vbcol=seagreen]
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:
>

creating a linked server

I am trying to create a linked server to an Access database. I can easily d
o this if the Access database does not have a database password on it BUT if
it does I can not figure out how to do this. Does anybody here know how to
do this'examnotes <anonymous@.discussions.microsoft.com> wrote:

quote:

---

>I am trying to create a linked server to an Access database. I can easily
do
>this if the Access database does not have a database password on it BUT if
it
>does I can not figure out how to do this. Does anybody here know how to do
>this'

---

Just playing with this yesterday.
EM > Security > Linked Servers
Will allow you to setup security for linked severs.

CreateFile() Error

I am using SQL 7.0 to establish connection with my ISP SQL ,using the correct UserName/Password given by my ISP.However I encounter this error message and my ISP technical support say that no problem with the server.

Error Source:Microsoft OLE DB Provider for SQL Server

Error Description:Client unable to establish connection [DBNMPNTW]ConnectionOpen [CreateFile()]

Please help me with this.Go to you SQL Server Network Utility and change protocol type to TCP/IP.|||Thanks EdwardP.Problem solved!!!
But how about this error,?

[DBMSSOCN] General netwprk error?

Umm ,is it cause by my computer setting that do not join the domain?

CreateFile() Error

I am using SQL 7.0 to establish connection with my ISP SQL ,using the correct UserName/Password given by my ISP.However I encounter this error message and my ISP technical support say that no problem with the server.

Error Source:Microsoft OLE DB Provider for SQL Server

Error Description:Client unable to establish connection [DBNMPNTW]ConnectionOpen [CreateFile()]

Please help me with this.

*Note* connect through internetWhen you say you are using sql server 7 to connect - are you using enterprise manager or query analyzer ? If so, have you tried both.|||I am using enterprise manager ,DTS import wizard.|||Are you saying you can connect to enterprise manager but when you use the dts import wizard you receive this error ? Is the sql server running where on the isp side - and this sql server is version 7 ? If you can connect to enterprise manager ok, can you access tables ... ?