Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Wednesday, September 9, 2015 1:51 PM
Points: 93, Visits: 273
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 @ 6:24 PM
Points: 14,469, Visits: 38,081
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1337462
Posted Monday, July 30, 2012 1:40 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 19, 2016 3:42 PM
Points: 625, Visits: 1,942
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: Wednesday, September 9, 2015 1:51 PM
Points: 93, Visits: 273
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 @ 6:24 PM
Points: 14,469, Visits: 38,081
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
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: Wednesday, September 9, 2015 1:51 PM
Points: 93, Visits: 273
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 @ 6:24 PM
Points: 14,469, Visits: 38,081
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1337596
Posted Tuesday, July 31, 2012 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 7,932, Visits: 14,336
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 @ 6:24 PM
Points: 14,469, Visits: 38,081
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1337872
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse