sql server 2005 express edition adding new user 1

[baran121]

hello i am triying to add new user with code from sql server 2005 express edition
i can add to sql server 2000 the code below, but i can not add to sql server 2005 express. please help me. thank you.

exec sp_addlogin @p_user,@p_user,'mydb','English'
exec sp_adduser @p_user,@p_user
exec sp_addsrvrolemember @p_user, 'securityadmin'
exec sp_grantdbaccess @p_user

 

[gmmastros]

Are you getting an error message? If so, what error are you getting?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[baran121]

gmmastros my new user is IBRAHIM and i got bolow error message

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.
Msg 15007, Level 16, State 1, Procedure sp_adduser, Line 15
'IBRAHIM' is not a valid login or you do not have permission.
Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 68
'IBRAHIM' is not a valid login or you do not have permission.
Msg 15007, Level 16, State 1, Line 1
'IBRAHIM' is not a valid login or you do not have permission.

 

[gmmastros]

[red][tt]Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.[/tt][/red]

It appears as though you are trying to create a login with a password that is the same as the login.

[tt]exec sp_addlogin [red]@p_user,@p_user[/red],'mydb','English'[/tt]

SQL server uses (by default) the password policy established on the computer/domain that SQL server is installed on. This is actually a good thing (believe it or not). I suspect that you wanted to make a password that would be easy to give to your new user. Right? I would suggest something like this instead.

[tt]exec sp_addlogin @p_user,[!]'Welcome.' +[/!] @p_user,'mydb','English'[/tt]

Then, when you give tell the user what their username and password is, you say, "Your user name is George, and your password is Welcome.George".

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[baran121]

thank you very much.

now i understand that sql 2005 doesnt let to use user name for password.

 

[gmmastros]

Here's a little more info...

SQL2000 did not confirm that the password you use for SQL complied with the password policy of your active directory domain. SQL2005 does check this, but it doesn't have to.

In SQL2000, to create a new login, you needed to use the sp_addlogin system stored procedure.

In SQL2005, you can still use sp_addlogin, but there is a new command that is recommended instead. sp_addlogin does not support password policies or expiration dates. The new command Create login allows you to bypass (set to off) the password policies and expirations.

create login [IBRAHIM]
with   Password = 'IBRAHIM', 
       Default_Database = MyDB, 
       Default_Language = English,
       [!]Check_Policy = OFF,
       Check_Expiration = OFF[/!];

The Create Login command allows you to set the "Enforce password policy" and "Enforce password expiration" to off. You'll notice that when you create a login using SQL Server Management Studio, and you select SQL Server Authentication, there are check boxes for these options.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[baran121]

thank you very very much.