Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Monitoring Changes to Users in databases. Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 12:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 4:04 PM
Points: 93, Visits: 262
Hi All,

I have a peculiar issue. I got a complain for some developers that their permissions are being removed every quater. They have a login L1 which is a owner of Databae DB1. But for every quater their that login is being removed from DB1.

I would like to know if there is any way I can monitor where I can get the login who could be cahnging this permission. Or is there any wayI can get exacat time this change was made to the DB1. Are there any security audit tools that may cause this to happen??

A liitle insight would be great.

Regards.
Post #1337451
Posted Monday, July 30, 2012 12:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
which is actually being dropped, the login in sys.server_principals, or the user in databasename.sys..database_principals?

if it is databasename.sys..database_principals, is the database being restored every quarter, by chance?
if the backup doesn't contain the user L1, that would explain why the login(user associated to the login, actually) seems to disappear.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1337462
Posted Monday, July 30, 2012 1:40 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 11:20 AM
Points: 554, Visits: 1,624
Are their logins set to expire?
Post #1337512
Posted Monday, July 30, 2012 2:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 4:04 PM
Points: 93, Visits: 262
Thank you for your responsese.

Yes, Indeed they are sys.database_principlas.

When it comes to expiration. They passwords are set to expire every 365 days. So it should not be a problem.

What I want to know is. Is there a way where we can query something(LIke a DMF or DMV) and find out who chaged the ownership and deleted the users from the theire respective DBs.

I am plannin to create a trace which captures the Audit Change Database Owner Event. But it will only tell us in the future.
Is there any way I can find out what happened in the past.

Regards.
Post #1337541
Posted Monday, July 30, 2012 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
ok, you can get whodunnit info fromt eh default trace, i just tested this:
so if not too much time has passed, you can find out who;
declare @path varchar(255)
SELECT @path = path from sys.traces WHERE id = 1--the Default Trace
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
INNER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
WHERE EventClass = 109 --Audit Add DB User Event
AND EventSubClass IN (3,4) --ADD USER, DROP USER implicitly does [Grant database access],[Revoke database access]


i tested it by simply going to a test database and adding and droping a couple of users.
CREATE USER [bob] WITHOUT LOGIN
CREATE USER [jeff] WITHOUT LOGIN
DROP USER [bob]
DROP USER [jeff]

then looking at the last entries in my default trace, i got the EventClass and SubClass to add filters for a WHERE statement.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1337548
Posted Monday, July 30, 2012 3:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 4:04 PM
Points: 93, Visits: 262
OMG. You are Amazing. Thank you very much. You were a ton of help.

I was wondering when we run this does it query the current trace file. What I mean is, suppose there a number of trace files for the default trace and I run this query does it search only the current trace file that is being written to?
Post #1337588
Posted Monday, July 30, 2012 4:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
na1774 (7/30/2012)
OMG. You are Amazing. Thank you very much. You were a ton of help.

I was wondering when we run this does it query the current trace file. What I mean is, suppose there a number of trace files for the default trace and I run this query does it search only the current trace file that is being written to?

that's the sweet part; the DEFAULT parameter makes it read ALL the files (five in the case of the default trace.
from http://msdn.microsoft.com/en-us/library/ms188425.aspx

If number_files is specified as default, fn_trace_gettable reads all rollover files until it reaches the end of the trace. fn_trace_gettable returns a table with all the columns valid for the specified trace. For more information, see sp_trace_setevent (Transact-SQL).


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1337596
Posted Tuesday, July 31, 2012 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 7,126, Visits: 12,727
The DEFAULT option works as you described, but you'll need to set @path this way if you want to pickup all default trace files:

SELECT  @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1337859
Posted Tuesday, July 31, 2012 7:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
beautiful correction and thank you,
I updated my snippets with a note so i remember that.
thanks!

opc.three (7/31/2012)
The DEFAULT option works as you described, but you'll need to set @path this way if you want to pickup all default trace files:

SELECT  @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1337872
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse