Determine who granted priveleges to who

Question

Determine who granted priveleges to who

Express12

SSCoach

Points: 18526
More actions
February 6, 2008 at 2:54 pm

#203012

Recently, several of our Windows users (all in Windows group: "MyGroup") acquired C,R,U,D access to a handfull of Production DB's. What is the best method for determing "who" granted these accesses/priveleges to the group "MyGroup" ?
(fyi, we do not run C2 auditing in our production environment for performance reasons)
BT

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I'm pretty sure there's no way to figure out who did it this time, but you could run a trace or create a DDL trigger to be alerted to future attempts.

Greg

Steve Jones - SSC Editor SSC Guru Points: 738642 More actions · Answer 2

I agree with Greg that this is probably not stored anywhere. If you have logins audited, you could check who logged in at various times.

However if no one will admit it, then it doesn't necessarily do any good to figure this out. You'll just fracture the team and create trust issues. Be better to go about preventing it in the future and removing the permissions. Then you can try and handle it moving forward.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

SELECT Permission_name, class_desc, OBJECT_NAME(major_id), grantee.name AS 'Permission granted to', grantor.name AS 'Permission granted by'

FROM sys.database_permissions dp

INNER JOIN sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id

INNER JOIN sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id

This, in theory, should tell you who granted what permission. Problem is, the database principal that it stores maps to dbo for a member of the sysadmin role.

A good way to keep an eye on this kinda thing in SQL 2005 is to create a DDL trigger on the database for DDL_Database_Security_Events and to have that trigger log to a table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 4

Do you have any access request process that is documented in the company?

This may garner some clues. Especially if MyGroup is new on the Domain.

Also consider notifying the users before you revoke the priveleges. Maybe this would spark some needed conversation.

Put the triggers in place if you haven't already.

Did Gail's query give any clues?

Greg E