2

Shortly our development team will be moving an existing SQL Server 2008R2 instance into our Production environment. Ahead of this I have been asked to review all the code on it for anything malacious (e.g. wiping their own order history from the sales database).

This has come out of the blue and I'm not sure it's particularly physically feasible but I'm willing to give it a good go, at least from the SQL side of things. The question is, where can code exist? My draft list is

  • Stored Procedures
  • Functions (all)
  • Table Triggers
  • Assemblies (shouldn't be any by our developers)
  • Rules (shouldn't be any by our developers)
  • System Database versions of the above, modified by users
  • SSIS Packages
  • Server Triggers
  • Unexpected LinkedServers
  • SQL Agent Jobs
  • SQL Agent Alerts
Improve this question
  • 1
    Any ad hoc application or even powershell/vbscript etc. sitting on the dev machine of anyone with access to the database. – Aaron Bertrand Sep 26 '14 at 11:49
  • Code can exist in many places and if you start looking on a server it will be finding needle in a haystack ... But the best place to look is in TFS or in source control that your devs are using. – Kin Shah Sep 26 '14 at 11:51
  • I did say the SQL Instance, not the tin+metal server :o) – Paul Sep 26 '14 at 12:17
  • 2
    I think the point @Aaron is making is to not limit yourself to the obvious. If you are worried about the potential risk from code on the server instance itself, you're only worried about an extremely small part of the data ecosystem. – Max Vernon♦ Sep 26 '14 at 13:29
  • @MaxVernon, as the DBA looking at the server I only care about the instance. The other engineers should be vetting the concerns you're referring to. But, as you may suspect, even more relevantly.. this is all political also. – Paul Sep 29 '14 at 7:53
1

Startup stored procedures run from master each time the instance starts.

Replication with this database as the subscriber. Not code per se, but it will change data.

Improve this answer
  • I don't see how it answers the question! – Kin Shah Sep 26 '14 at 12:58
  • @kin - the OP is asking for information about where code might be stored in a SQL Server instance; startup stored procedures are a valid spot to look, in my opinion. – Max Vernon♦ Sep 26 '14 at 13:27
  • @Max Maybe it's just semantics, but a stored procedure is a stored procedure, whether it is marked as startup or not. Presumably the stored procedures in master are already included in #1 in the OP's original list? – Aaron Bertrand Sep 26 '14 at 13:56
  • @AaronBertrand good point. I felt it was helpful to realize that the stored proc doesn't have to be discretely executed if it is marked for startup. – Max Vernon♦ Sep 26 '14 at 16:47
  • 2
    @Max yes, there is that difference, but still if you're going to review all stored procedures, you should review all stored procedures, whether you know how/when they may or may not get executed. – Aaron Bertrand Sep 26 '14 at 16:49
1

Maybe not the most advanced answer. But is there anyway in having an staging environment, or sandbox which exactly has the same specifics as the production environment?

Maybe just do a rigorous deploy to the sandbox and observe the results.

(As a quick and dirty/trail and error method of course, not a wise one when production data or dependencies are altered by this test)

Maybe to add to your list if applicable:

  • Existing user(credentials)
Improve this answer
  • Time and resource constraints do not allow for this sadly. – Paul Sep 29 '14 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.