locked
Create SQL Login that utilized Active Directory RRS feed

  • Question

  • Hello SQL Guru,

    I’m newbie to create SQL Login that utilized Active Directory.

    I have a SQL Server 2008 Enterprise edition 64-bits that runs in Window Server 2008 R2 Standard Edition 64-bit operating Systems.

    I configured  the SQL Server 2008 with Window Authentication.

    Then, I login with built-in Administrator account into computer and connected to my SQL Server 2008. 

    e.g: Object Explorer displayed COMPUTER NAME\Database Instance Name (SQL Server 10.0.2531 – COMPUTER NAME\Admin Account)

    Then, I created SQL login on the following:

    USE AccountingDatabase;
    GO

    CREATE USER  DomainName\User Name FOR LOGIN DomainName\User Name
    GO

    EXEC sp_addrolemember N’db_datareader’, N’DomainName\User Name’
    GO

    I tested it out and the specific login still did not have an access to specific database instance  e.g: “AccountingDatabase”

    Also, I did not see the “User Mapping” under properties checked/assigned to those specific user.


    Can anyone help and give me light on best practice.

    Thanks,
    Edwin

    Friday, February 26, 2010 5:13 PM

Answers

  • Hi TJEDWIN,

    From your description, you want to create a user for your domain account, but failed.

    If I misunderstand, please let me know.

    Before you create a user for any login, you must create the corresponding  login first.
    So, try the following script:

    use AccountingDatabase;
    GO

    CREATE LOGIN DomainName\UserName FROM WINDOWS
    GO

    CREATE USER username FOR LOGIN DomainName\UserName
    go

    For more information:
    "CREATE LOGIN":http://technet.microsoft.com/en-us/library/ms189751.aspx

    Regards,
    Tom Li - MSFT
    Monday, March 1, 2010 6:12 AM

All replies

  • HI Edwin,

    What was the error when you executed the above statements ?
    How did you create the login (thru GUI or TSQL) ?

    Thanks, Leks
    Friday, February 26, 2010 6:53 PM
  • Hi TJEDWIN,

    From your description, you want to create a user for your domain account, but failed.

    If I misunderstand, please let me know.

    Before you create a user for any login, you must create the corresponding  login first.
    So, try the following script:

    use AccountingDatabase;
    GO

    CREATE LOGIN DomainName\UserName FROM WINDOWS
    GO

    CREATE USER username FOR LOGIN DomainName\UserName
    go

    For more information:
    "CREATE LOGIN":http://technet.microsoft.com/en-us/library/ms189751.aspx

    Regards,
    Tom Li - MSFT
    Monday, March 1, 2010 6:12 AM