| Author |
Topic |
|
olibara
Yak Posting Veteran
94 Posts |
 Posted - 2010-08-22 : 18:08:40
|
| HelloI make a copy of a database on my Local SQL Express serverThe server use Windows AuthentificationWhat I want to do is to set up a SQL server Authentification in order to use my application with the same User Id and Paswword as in production ( for compatibility purpose)How can I do that ?What are the steps ?It seems a little bit obfuscateThanks for any help |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
olibara
Yak Posting Veteran
94 Posts |
Posted - 2010-08-23 : 01:53:57
|
HelloProduction is Using SQL Authentification- I don't have any problem to buil or change the connexion string- I can acceed my test base with a new connexion string as thisdatabase=ZIStat;server=BARA01\\SQLEXPRESS;Trusted_Connection = True; What I want to do is to add User ID and PWD to ma Test base to be able to run my application without having to change the connexion stringdatabase=ZIStat;server=BARA01\\SQLEXPRESS;User ID=olibara;pwd=olibara; So the question is HOW can I do that What are the steps ? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
olibara
Yak Posting Veteran
94 Posts |
Posted - 2010-08-23 : 15:32:07
|
quote:
Then you just need to add the login to the SQL Server via Management Studio. Find the logins section in there and add it.
Thank youBut it is certainly not so simple. I've spended all my sunday to find such "easy" wayThis it the reason why I'm trying to refers to gurus to find the roadbook steps to set up such login properlyI'm still on it right now ! |
|
|
|
olibara
Yak Posting Veteran
94 Posts |
Posted - 2010-08-23 : 15:44:38
|
Hello I think that I'm a step plus loin  Now I can open a connexion on the database using Sql authentication connexion stringBut I get the message quote:
"The SELECT permission was denied on the object 'Alias', Database 'ZIStat', schema 'dbo'."}
So what else is missingSorry to be so stupid but I'm a developper, not a DB Admin and the topic about settings User Id an Login to Sql Server is really obfuscate !! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
olibara
Yak Posting Veteran
94 Posts |
Posted - 2010-08-24 : 01:42:43
|
quote:
How did you make the copy of the database? Did you use BACKUP/RESTORE?
Yes but this is no matter, because I also wants to set the same rights to a newly created database |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
olibara
Yak Posting Veteran
94 Posts |
Posted - 2010-08-24 : 14:26:23
|
| Finaly hera are the steps1- In SQL server Management studio, right click on Main server Node2- Select Security Node3- Set Radio Button SQL Server And Windows Authentication mode4- Reboot5- Create a new Login / PaswordUSE master;CREATE LOGIN olibara WITH PASSWORD = 'olibara01'; Bt default SQL server requires a enhforced password, it can be changed later6- Add DU UsersUSE ZIStat; CREATE USER olibara FOR LOGIN olibara;USE Touch; CREATE USER olibara FOR LOGIN olibara; 7- If necessary release pasword Enforcement :- Open MNG Studio- Select node Security / Login- Right-Click on Login property to change- UnCheck option Enforce Pasword Policy- Change passwordThen you can open the database with SQL Authentication connexion string but you will get the message on any select{"The SELECT permission was denied on the object 'Alias', database 'ZIStat', schema 'dbo'."} 1- Open MNG Studio2- Select la database3- Extend sécurity node of database4- Extend User Node5- User Property6- In the Chechedlistbox "Role Member" (I still don't know the purpose of the other)7- Check DB_Owner, DB_SecurityAdmin ( It seems to be enough ) |
|
|
|
|
How to add a user id and password to SQL Server Da