Get Information for Changes Not Made with SC 3?

erodriguez

I was doing some testing on SQL Server 2008 R2 with a user who did not have the SQL SC 3 product installed.

I was pleased to see their changes were still listed in the commit window for the database he manipulated. However, I noticed that the Owner was listed as dbo, not the Windows account that he uses to log in to SSMS. Looking closer, I also saw my Win account was not being used - the owner was shown to be dbo as well.

For "fun" I added a new Role and modified a schema. My changes were picked up, but they did not have an Owner identified. So if someone else made these changes, and it was out of the SC 3 workflow, what is the best practice method for identifying who made the change?

So how would "we" know who applied a change, in a dedicated database environment, if it was a change made outside of SQL SC 3? Is it possible to retrieve any information on who made the change? Sure, we can see a change is pending, but I would like to know who made it out of process, rather than have to ping an entire team and wait for someone to fess up. Given that we ask users to log into databases using their AD accounts, I wonder if this information can be tracked.

In some research I did I came across this:
http://www.mssqltips.com/sqlservertip/2 ... e-changes/

And wonder if anyone else has implemented something similar to at least monitor user changes.

erodriguez

I think we may go down the auditing path, at least first stage. Thanks. Would love to hear other input, as I'm more of a developer than a DBA.

RickyR

Hi!

Thanks for submitting your inquiry.
We have created a ticket for this situation, and would like to take this offline for further investigation.

I will contact you through the email address associated with your Forum account.

Thanks!

Rick

erodriguez

Hi Ricky,

Thanks - I dont think its a support issue per se, more like a feature request - let me clarify.

In the SC 3 pending commit window, or the get latest window, you see the SQL owner of an object, but you cannot tell who actually made the change unless you inspect the repo logs.

It would be nice if the SC 3 window listed "who" was responsible for checking in "what". This would avoid the extra work behind trying to figure out who checked in what in a multi developer environment. Whether shared or dedicated, this information would be helpful.

The other, related thing is identifying who made a db change if that change was made outside of SC 3.

In my test, I asked a user who has permissions to the database granted to his AD account, but no SC 3 install, to make a change to an object so that we could see how it behaved for those who DID have SC 3 installed. Since the object owner is listed (and not the person who made the change), it isn't at all clear who modified the object - just that it's been modified.

To mitigate this I thought we could implement auditing, which would at least catalog that an object was modified by someone. But it would be nice if there were a way to identify (perhaps there is - I'm evaluating and I may just be missing it!) who explicitly made a change, not just by object owner, but actual user login (whether it was SA, a domain account, etc).

I hope that is clearer!