none
How should I impersonate a sql login within an active directory group

    Question

  • Here is the scenario.

    • I have a Database which has tables and views.  
    • The only logins are an active directory (AD) group, and an AD application/user id
    • The application id only has read permissions on the views
    • The views are row constrained by the SUSER_NAME() function
    • I don't want the AD members to actually be able to connect to and read the data directly.

    How can I accomplish this goal?

    • The application id can connect to the DB and read the views AS one of the AD group members  (that way they only see the rows that are applicable to them.)

    I'm struggling with the permissions required to ONLY let this happen and to not grant more permissions than should exist.





    • Edited by WellOfDavid Wednesday, July 24, 2013 11:18 PM
    Wednesday, July 24, 2013 11:17 PM

Answers

  • Create a windows group and add to all ad users. Then grant SELECT (whatever) permissions to that group. Specify that windows group  to connect to the server in your application.

    GRANT EXEC ON some_sp TO user-- Grant permission on a single procedure.
    GRANT EXEC ON SCHEMA::dbo TO user-- Grant perpmission on all procedures in
                                          the dbo schema
    GRANT EXEC TO user-- Grant EXEC permission all procedures in the database.


    To grant all "normal" permissions on a table

       GRANT SELECT, UPDATE, DELETE, INSERT on tblname TO user

    To grant user access on tables in a schema:

       GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO user

    To grant him access on all tables:

       GRANT SELECT, UPDATE, DELETE, INSERT TO user


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 25, 2013 6:26 AM