March 2, 2006 at 10:00 am
Hey guys,
I'm looking for a way to automate resetting of Passwords for more than 100 users. Is there any possibility to do that with a sql script?
Thank & regards
March 2, 2006 at 9:43 pm
hi,
may this give u a way.This is a script to generate automated passwords.
from now u want to change the password for 100 user.
create a stored procedure which will change the password or update the passwords from table table_name
create table table_name
(
A varchar(20),
b varchar(20)
);
CREATE PROC sp_random_password
(
@len int = 8, --Length of the password to be generated
@password_type char(7) = 'simple'
--Default is to generate a simple password with lowecase letters.
--Pass anything other than 'simple' to generate a complex password.
--The complex password includes numbers, special characters, upper case and lower case letters
)
AS
/**To generate a simple password with a length of 8 characters:
EXEC random_password
To generate a simple password with 6 characters:
EXEC random_password 6
To generate a complex password with 8 characters:
EXEC sp_random_password @Password_type = 'complex'
To generate a comples password with 6 characters:
EXEC sp_random_password 675, 'complex'
*************************************************************************************************/
BEGIN
DECLARE @password varchar(25), @type tinyint, @bitmap char(6)
SET @password=''
SET @bitmap = 'uaeioy'
--@bitmap contains all the vowels, which are a, e, i, o, u and y. These vowels are used to generate slightly readable/rememberable simple passwords
WHILE @len > 0
BEGIN
IF @password_type = 'simple' --Generating a simple password
BEGIN
IF (@len%2) = 0 --Appending a random vowel to @password
SET @password = @password + SUBSTRING(@bitmap,CONVERT(int,ROUND(1 + (RAND() * (5)),0)),1)
ELSE --Appending a random alphabet
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE --Generating a complex password
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1 --Appending a random lower case alphabet to @password
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @type = 2 --Appending a random upper case alphabet to @password
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 4 --Appending a random special character to @password
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
END
SET @len = @len - 1
END
SELECT @password --Here's the result
END
from
Killer
March 3, 2006 at 6:24 am
Here is the article http://www.sqlservercentral.com/columnists/lPeysakhovich/automatenewloginscreation.asp
Automate New Logins Creation. But it can be done the same way to update the passwords
LP.
March 3, 2006 at 6:40 am
--in Theory, this would generate thecode to reset all SQL Logins to a default password
declare
@isql varchar(2000),
@username varchar(64),
@newpassword varchar(20)
set @newpassword='dataaccess'
declare c1 cursor for select name from sysusers
where uid > 1
and uid < 16384
and [name] not in ('guest','INFORMATION_SCHEMA','system_function_schema') -- in case this was run in master
and name not like '%\%' --should you reset passwords for domain users like DOMAINNAME\Mgonzalez ? i don't think so, as this is handled by authentication.
open c1
fetch next from c1 into @username
While @@fetch_status <> -1
begin
select @isql = '-- assign user ' + @username + ' the default password of ' + @newpassword + char(13)
select @isql = 'sp_password null,' + @newpassword + ',' + @username
print @isql
--exec(@isql)
fetch next from c1 into @username
end
close c1
deallocate c1
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply