How to find out the list of users who has direct grant to a service account ?

  • Also , is it true that Grants to service accounts should go through roles as it is against audit & compliance standards ??

    If yes, than how to find out the list of users who has direct grant to a service account ?

    - Then how to revoke this grant from service account and than how to grant through the role ?

    - Is there a script that i can run and find the list of users in each SQL server instance ?

  • sshobha297 (8/4/2015)


    Also , is it true that Grants to service accounts should go through roles as it is against audit & compliance standards ??

    If yes, than how to find out the list of users who has direct grant to a service account ?

    - Then how to revoke this grant from service account and than how to grant through the role ?

    - Is there a script that i can run and find the list of users in each SQL server instance ?

    What are you calling a "service account"? Are you talking about and SQL Server Authenticated Login or a Windows Authenticated Login? If the latter, is it Windows Group or a single AD account?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Its a Windows Authenticated Login

    I am not sure what does this means ( If the latter, is it Windows Group or a single AD account? )

    But its a AD i guess..

  • sshobha297 (8/4/2015)


    Its a Windows Authenticated Login

    I am not sure what does this means ( If the latter, is it Windows Group or a single AD account? )

    But its a AD i guess..

    Can't guess here. You need to find out. Run the following through a command prompt, preferably but not necessarily from the SQL Server just to make sure that you're in the correct DOMAIN.

    NET USER "putaccountnameherewithnodomain" /DOMAIN

    If that gives you a proper return (you'll know by looking at it), then it's a single AD account (user).

    If it doesn't give you a proper return, then try the following...

    NET GROUP "putaccountnameherewithnodomain" /DOMAIN

    If that gives you a proper return, then it's a Windows AD group.

    If it doesn't give you a proper return, then it's probably be an SQL Server Authentication Login.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • sshobha297 (8/4/2015)


    Also , is it true that Grants to service accounts should go through roles as it is against audit & compliance standards ??

    If yes, than how to find out the list of users who has direct grant to a service account ?

    - Then how to revoke this grant from service account and than how to grant through the role ?

    - Is there a script that i can run and find the list of users in each SQL server instance ?

    Are you trying to figure out who has the ability to logon as the service account (I presume you mean the SQL Server Service account or possibly an application account used to connect to the database)?

    Or are you trying to figure out who has the permissions to grant permissions to those service accounts?

    Or are you trying to figure out what permissions are tied to a service account?

    There are Best Practices that would be different depending on how you answer these questions (e.g. SQL Server service account vs. application principal acting as an application service account).

    You can grant permissions to the SQL service account via domain policy and group memberships.

    You can also grant permissions to application principals via role memberships.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 5 posts - 1 through 4 (of 4 total)

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