If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > MSDE create user SQL Authetification

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 5
MSDE create user SQL Authetification

Hi,
I'm new in MSDE and I do not know how to create a db user, running MSDE in SQL Authenticfication mode.
I can create a user using osql :
osql -E -S myserver -Q"sp_grantlogin @loginame = N'myserver\myadmin'"
and i assigned this user sysadmin role.
But when I try to connect to the db with this user, I get the following error: Login failed : reason : user not associated with a trusted SQL Server Connection.
Can anyone help me please?
Thanks a lot in advance
Severine
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Dec 2002
Location: Antwerp, Belgium
Posts: 227
Your problem is that you create a sql server login and the msde is set to accept only windows accounts as a login. So you have use a windows account for your login. Another solutions is to set the authentication mode of the database to "mixed" but I'm not sure if this is possible in msde.
__________________
Johan
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 5
Hi Jora,
First of all, thanks for your answer.
Yes, I set the mode to be a mixed Authentification, it is possible at least by modifying a registry key.
I 'm now able to create a user but i can not manage to open its default db.
here are my commands :

sp_addlogin 'loginname','pwd','mydb'
sp_grantdbaccess 'loginname', 'loginname'

but when i try to connect, i have the following error :
Can not open default database. Login failed.

I test it on a real SQL server before doing my tests on MSDE.
When I look to it, my db does not have my new user assigned.
Any idea?
Thanks
Severine
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Dec 2002
Location: Antwerp, Belgium
Posts: 227
When you issue your commands, are you connected to the 'mydb' database (which you used in the sp_addlogin procedure)? The sp_grantdbaccess grants access to the database in which it is issued.
__________________
Johan
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 5
Maybe that is what I'm missing.
How do I do that, knowing that I'm using osql to do all my manipualtions?
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 5
I know,
I found all the good commands :
osql -E -S server -Q"sp_addlogin 'user','pwd','db'"
osql -E -S server -d 'db' -Q"sp_grantdbaccess 'user',user'

Thanks again for your help, I would have spent hours without you...
Severine
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 5
It's me again.
In fact that works perfectly with SQL Server, but it fails with MSDE even if the Authentification is Mixed mode.
Any idea?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On