locked
Grant Database Access to Windows Domain RRS feed

  • Question

  • I am trying to grant any user in a specific windows domain access to a database in SQL Server 2008 Express. I am using Windows Authentication only. I can create database users for specific windows logins but can't figure out how to create a user that will work for any domain member. I started with the T-SQL create login statement detailed here: http://msdn.microsoft.com/en-us/library/ms189751.aspx and tried several permutations for the LoginName argument (DOMAIN\*, DOMAIN\%, DOMAIN\$) but none seem to work.

    I'm a SQL noob and feel like I must be missing something really simple. Any help is greatly appreciated.

     

    Tuesday, March 8, 2011 3:21 PM

Answers

  • Create Login [DOMAIN\Authenticated Users] FROM WINDOWS WITH DEFAULT DATABASE = <your_database>

    USE <your_database>

    CREATE USER some_user FOR LOGIN [DOMAIN\Authenticated Users]

    GO

    GRANT <some_permissions> ON <some_objects> to <some_user>


    Twitter
    • Marked as answer by dkingston Tuesday, March 8, 2011 7:41 PM
    Tuesday, March 8, 2011 4:11 PM

All replies

  • Can you try DOMAIN\Domain Users

    or DOMAIN\Everyone

    ?

    Tuesday, March 8, 2011 3:50 PM
  • Try with the following :

     

    CREATE

     

    LOGIN [domainname\Name] FROM WINDOWS WITH DEFAULT_DATABASE = [Master], DEFAULT_LANGUAGE = [English]

     

    ------------------
    Thanks,Suhas V

    Tuesday, March 8, 2011 3:54 PM
  • Create Login [DOMAIN\Authenticated Users] FROM WINDOWS WITH DEFAULT DATABASE = <your_database>

    USE <your_database>

    CREATE USER some_user FOR LOGIN [DOMAIN\Authenticated Users]

    GO

    GRANT <some_permissions> ON <some_objects> to <some_user>


    Twitter
    • Marked as answer by dkingston Tuesday, March 8, 2011 7:41 PM
    Tuesday, March 8, 2011 4:11 PM
  • Thanks much everyone!
    For the record (and I assume the difference is due to some salient point I left out of my original question or becauseI haven't actually done what I set out to do) this is what seems to have worked:

    CREATE LOGIN [NT AUTHORITY\Authenticated Users]
    FROM WINDOWS WITH DEFAULT_DATABASE=MyDatabase

    USE MyDatabase

    CREATE USER MyNewUser FOR LOGIN [NT AUTHORITY\Authenticated Users]

    GRANT SELECT
    ,UPDATE
    ,INSERT
    ,DELETE
    ,EXECUTE
    TO MyNewUser

    Tuesday, March 8, 2011 7:59 PM