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

Question

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

sshobha297

Grasshopper

Points: 17
More actions
August 4, 2015 at 8:52 am

#319549

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 ?

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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)

sshobha297 Grasshopper Points: 17 More actions · Answer 2

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..

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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