locked
Manage Security with SSPI RRS feed

  • Question

  • Hi All,

    I have an application connecting to the SQL as "integrated Security=SSPI", but currently we have 2 users to the server so I added these 2 users to the "Logins" of the sql server. If tomorrow, new users come up, they might not able to login as their windows authentication is not yet added to the "Login" section of SQL Server, how should I mange this? Not done these before...so your help is much appreciated.

    Thanks

    Pad

    Friday, November 2, 2012 5:34 AM

Answers

  • Hallo Padmalingam,

    let's say you have a database called "DB_STAFF" and all HR employees should have access to it.

    In this case I would create an AD group (i.e. AD_SQL_STAFF_DB_USER)

    The next step you add this group as Login to the SQL Server

    CREATE LOGIN [DOMAIN\AD_SQL_STAFF_DB_USER] FROM WINDOWS [DOMAIN\AD_SQL_STAFF_DB_USER]
    The last step is to add this group with privileged access as user to the database (i.e. DB_STAFF)
    USE [DB_STAFF]
    GO
    
    CREATE USER [DOMAIN\AD_SQL_STAFF_DB_USER] FROM LOGIN [DOMAIN\AD_SQL_STAFF_DB_USER]

    O.K. - now the only thing which has to be do is to include a process which adds the AD-Users to the AD-Group and they automatically get the permissions as all db users have.

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de


    • Edited by Uwe RickenMVP Friday, November 2, 2012 6:13 AM
    • Proposed as answer by V. Keerthi Deep Friday, November 2, 2012 1:19 PM
    • Marked as answer by Shulei Chen Friday, November 9, 2012 10:00 AM
    Friday, November 2, 2012 6:12 AM