July 30, 2012 at 12:01 pm
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.
July 30, 2012 at 12:32 pm
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
July 30, 2012 at 1:40 pm
Are their logins set to expire?
July 30, 2012 at 2:24 pm
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.
July 30, 2012 at 2:36 pm
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
July 30, 2012 at 3:55 pm
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?
July 30, 2012 at 4:07 pm
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
July 31, 2012 at 6:48 am
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
July 31, 2012 at 7:03 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply