Study your Data

It is important to have a secure environment, enforcing the least privilege principle in your servers and databases, but this can be difficult to achieve on large environments, where a lot of people have elevated privileges or several people have permissions to perform tasks over your data or schema. As an additional measure, an audit log should be implemented in those scenarios and review these often. SQL Server provides the functionality to implement audit logs at Server and Database level. Starting SQL Server 2008, all SQL Server editions support Server level audits. Database audits are supported beginning with SQL Server 2016 (13.x) SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions. On this post I will show you how to configure and view audit logs: Implement Audit Log Under security, go to Audits and select New audit... Specify a name for the audit and select an audit destination (I personally prefer a

I run a technical blog, so it is necessary to put code in almost every post. Most of the GUIs we use to do our work (let's say SSMS or Visual Studio) already provide this functionality, but when you copy and paste the code to the blog, you need to format it first, to pass from this: DELETE FROM database1.schema1.table1 WHERE column1=valuex /* generic comment */ SELECT * FROM database2.schema2.table2 WHERE column2=456 To this: 1 2 3 DELETE FROM database1.schema1.table1 WHERE column1=valuex /* generic comment */ SELECT * FROM database2.schema2.table2 WHERE column2=456 These are some sites I use: http://hilite.me/   My site of choice, it supports several languages and you can select predefined styles and customize them on-site. http://poorsql.com/ Only for SQL, it provides a more robust keyword detection, but the format and customization is more limited, it also offers some obfuscation options. http://www.dpriver.com/pp/sqlformat.htm Also a good opti

As any of the available SQL Server services, Reporting Services will use all of the server memory if needed, in some cases when you have multiple products configured for the same server, you need to limit these values, we use WorkingSetMaximum and WorkingSetMinimum configuration values to achieve this task. Where to set these values? Find the .config file We need to localize the RsReportServer.config configuration file in our server, this file is in the SQL Server installation folder under MSRS00.XXXX\Reporting Services\ReportServer where the 00 is your reporting services version and the XXXXX are your instance name. NOTE: Before changing any parameter in this file, also take a backup of the file contents first. Find the Service tag Once you have located the file, proceed to find the <Service></Service> tags, and add the <WorkingSetMinimum> and <WorkingSetMaximum> as desired, values are specified in kilobytes, so for example, to set a valu