Login creation in SQL Server 2005 for SQL Server Authentication Mode

  • Abdul Basit

    Grasshopper

    Points: 11

    Comments posted to this topic are about the item Login creation in SQL Server 2005 for SQL Server Authentication Mode

  • eidermauricio

    SSC Rookie

    Points: 40

    Good Article, you should go to the next one, I mean allowing windows users by accessing with autentication mode.

    bye 😉

  • satish allamsetti

    SSC Rookie

    Points: 33

    how to create the abstract views in the sql server 2005 or 2003

    is it possible to the database queries

  • sunjiulu

    Ten Centuries

    Points: 1212

    I have a question about windows authentication.

    when one domain user belong to multiple windows domain group, say grp1 and grp2, and both groups are granted access to database and given different permissions. when this user logon to sqlserver, which path he got? through grp1 or grp2? how does sqlserver decide on taking which SID to authenticate the user?

    thanks,

    jiulu

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    The article was well done, but it could use some expansion. For instance, it might be worth discussing why you would want to allow SQL Server Authentication for one thing.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • VadimK

    SSC-Addicted

    Points: 441

    Is this article from "SQL for Dummies" book? If not, we should open a sub-forum for this kind stuff.

  • noeld

    SSC Guru

    Points: 96590

    zloy (12/18/2007)


    Is this article from "SQL for Dummies" book? If not, we should open a sub-forum for this kind stuff.

    :hehe::hehe::hehe::hehe::hehe::hehe::hehe:


    * Noel

  • Doug Krawec

    SSC Rookie

    Points: 33

    I've noticed that when I Restore a database (in my case to a different machine) that the security settings that were in place on the original database are not replicated on the restored database. In particular, the SQL Server logins do not seem to work. Does anyone know what is happening here and if there is a work around to maintain the original security settings.

  • sunjiulu

    Ten Centuries

    Points: 1212

    Doug Krawec (12/18/2007)


    I've noticed that when I Restore a database (in my case to a different machine) that the security settings that were in place on the original database are not replicated on the restored database. In particular, the SQL Server logins do not seem to work. Does anyone know what is happening here and if there is a work around to maintain the original security settings.

    doug,

    the sid in master..syslogin has to be re-created on your restored server if it's not there, the db.dbo.sysusers will have the sid because it's part of the backup.

    if you are using sql authentication, then you may have to update dbname.dbo.sysusers sid to match master.dbo.syslogins, because sid will be different from one server to another, but using windows authentication sid will come from windows domain or group, it will not change, it's just a matter of creating the windows group or user on the restored users.

    have a look on the result of

    select * from master..syslogins

    and

    select * from yourdbname..sysusers

  • Doug Krawec

    SSC Rookie

    Points: 33

    sunjiulu,

    Thanks for the explanation.

    Yes, I am using SQL Server login rather than Windows Authentication. We have created a SQL Login of the form "ApplicationUser" with access to the single application database. The database I'm restoring is a copy of Production which is being used on a development box.

    Is there a standard script that can be used to reset this SID on the restored database to match that of the instance of the SQL Server your are restoring to? I would assume you would need to check for the existence of "ApplicationUser".

  • sunjiulu

    Ten Centuries

    Points: 1212

    Doug Krawec (12/18/2007)


    sunjiulu,

    Thanks for the explanation.

    Yes, I am using SQL Server login rather than Windows Authentication. We have created a SQL Login of the form "ApplicationUser" with access to the single application database. The database I'm restoring is a copy of Production which is being used on a development box.

    Is there a standard script that can be used to reset this SID on the restored database to match that of the instance of the SQL Server your are restoring to? I would assume you would need to check for the existence of "ApplicationUser".

    doug,

    the following sql is what I do in sql2000, I believe sql2005 has a system procedure to do it, maybe a google search will give you the name of the proc. here you go.

    /*restore the database*/

    restore database yourdbname from disk='d:\dropit\backup.BAK'

    ...

    /*allow update system tables*/

    sp_configure 'allow update', 1

    reconfigure with override

    /*get the sid*/

    select sid,name from syslogins where name='yourloginname'

    /*update system table to match the sid*/

    update yourdbname..sysusers set sid=0x91729E827E48874A8883B8E08A9426F8 where name='yourloginname'

    /*not allow update system tables*/

    sp_configure 'allow update', 0

    reconfigure with override

  • John Bedi

    Newbie

    Points: 5

    When SQL authenication is necessary and there are a number of Logins required, I have attempted to use SQL to created the logins. The 3 checkboxes in the form need to be unchecked. Without this the logins do not function and you can not uncheck the boxes afterwards and have the login work. You must delete and recreate the login using the GUI. Is there something I am missing that would allow the creation of multiple users via SQL?

    John

  • Doug Krawec

    SSC Rookie

    Points: 33

    I did a search and found that SQL 2000 does have a system procedure called sp_change_users_login

    which does fix the database login to match the SQL Server instance.

    MSDN link is:

    http://msdn2.microsoft.com/en-us/library/ms174378.aspx

    Format I ended up using is: (Note, I've left off the optional SQL User (if you are mapping to a different user) and password at end):

    USE your_database

    GO

    EXEC sp_change_users_login 'Auto_Fix', ' '

    GO

  • Frederik Carlier

    Grasshopper

    Points: 17

    hello,

    looking at your previous post, I presume that you have created a production and a testdatabase and that you do a restore to a testdatabase but that it doesn't allow the authenticated user to logon to the testdatabase, as sunjiulu suggest this is because the ssid's of the sql-accounts do not match,

    in another post, it is discribed how to copy users from one server to another, see this link on another sql-forum for a way to copy the users so that your app will also work on the other database.

    link : http://blogs.techrepublic.com.com/howdoi/?p=140&tag=nl.e138

    with kind regards,

  • awadahmed_120076

    Newbie

    Points: 5

    thank you my dear but i face problem when i login by created user name

    error appears "this user not trusted "

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply