0

I've recently taken on responsibility for a new MSSQL database.

I can see nearly every stored procedure has repetitive boiler plate code which sets up the user context/row permissions and logs execution.

Are there any tools which can be used to simplify and remove the repetition?

Something that would work similar to an entry/exit aspect if I was writing C# code.

I have the full redgate toolbelt and MS visual studio at my disposal if that helps provide a solution.

Kind Regards,

Steve

Stephen Hewison
  • 324
  • 2
  • 9
  • 3
    "I can see nearly every stored procedure has repetitive boiler plate code which sets up the user context/row permissions and logs execution." We don't see it. Please update your question with it. – miriamka May 11 '21 at 14:43
  • 1
    `which sets up the user context/row permissions and logs execution`it's quite likely most of this code can be eliminated *and* security improved, if SQL Server and database security features are used properly. For example. views can be used to restrict what specific accounts can read, execution permissions on stored procedures can limit what can be executed. Column and Row-level security can limit the results even for users that can read tables directly – Panagiotis Kanavos May 11 '21 at 14:48
  • 1
    Auditing (which is implemented by logging) can be done by using traces and extended events. Typically, this is avoided because it's expensive BUT doing this in the stored procedures is probably even more expensive – Panagiotis Kanavos May 11 '21 at 14:49
  • 1
    Finally, this assumes there are separate accounts for each user. If everyone logs in with the same account and passes an application `user ID` a lot of SQL Server security features won't be ueable. If that's the case, it's probably a design error - it means that **business**-level auditing is performed using *database*-level features – Panagiotis Kanavos May 11 '21 at 14:51

0 Answers0