Scripting Logins & User Mappings for Windows Integrated Security

  • Del Lee

    SSCrazy

    Points: 2772

    We are making preparations to do away with mixed mode security and going to windows authentication only.  So, I'm using something like the script below to create the logins and apply permissions to each database (this is greatly simplified, but hopefully I have all the relevant parts for one of my databases).  However, after running the script, when I use the object explorer to look at the user mapping for my_user, the database my_database is not selected as I am expecting.  Also, when I look under the Security\Users node for the database, the user has a tiny red x as part of the icon.  I don't want to manually map the users via SSMS, so how do I script this properly to add the user mapping?

    USE [master]

    CREATE LOGIN [mydomain\my_user] FROM WINDOWS

    GO

    USE [my_database]

    GRANT EXECUTE TO [mydomain\my_user]

    GO

    GRANT EXECUTE TO [mydomain\my_user]

    GO

    ALTER ROLE db_datareader ADD MEMBER [mydomain\my_user]

    GO

    ALTER ROLE db_datawriter ADD MEMBER [mydomain\my_user]

    GO



    Del Lee

  • Dinosaur

    Valued Member

    Points: 53

    If I'm understanding you correctly, the users do not have connect privileges to the database.  Assuming you actually want them to be able to connect to the database, the following should do the trick:

    GRANT CONNECT TO [mydomain\my_user] ;
    GRANT CONNECT TO [mydomain\my_user] ;
  • Del Lee

    SSCrazy

    Points: 2772

    Thanks, I actually had GRANT CONNECT as well, but only in the [master] database.  I didn't have it for each database.



    Del Lee

Viewing 3 posts - 1 through 3 (of 3 total)

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