Results 1 to 7 of 7
  1. #1
    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

  2. #2
    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

  3. #3
    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

  4. #4
    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

  5. #5
    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?

  6. #6
    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

  7. #7
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •