One of the security recommendation inside SQL Server Security Best Practice white paper for guest user is that, to disable guest access in every database (expect “master”, “msdb” and “tempdb”) on SQL Server instance, and it should not be used in any circumstances. By default, guest user exists in all… Read more

Database security is essential for the organisations working in healthcare and financial sector because the databases in such organisations contain sensitive and confidential information about their customers. The organisation working in such sectors also falls under the boundaries of data regulatory and compliance requirements (such as HIPAA, PCI-DSS, SSAE16 etc.)… Read more

Database servers are configured in such a way that they can service multiple requests from multiple users. Obviously this increases the likelihood of conflicts because multiple processes can request access to same resources at the same time. This type of conflict is also known as block or blocking. Blocking usually… Read more

Today, my first article (ie. Different ways to audit SQL Server security) published on SearchSQLServer.com. In this article, I discussed about the various options available in SQL Server for auditing security-related events, which helps companies to meet various regulatory compliance requirements.

Read more

Windows Azure SQL Database also known as SQL Server Data Services or simply SQL Azure is a Microsoft’s cloud services that offers a capabilities on Microsoft SQL Server 2012 (such as Amazon S3 and Amazon Relational Database Services), as part of the Azure Services Platform. Windows Azure SQL Database… Read more

Today, I’m sharing the following T-SQL script which you can use to find which SQL Server Agent Jobs failed yesterday. I use this script as part of my daily server monitoring SSIS package, which executes this script on all production SQL server and then sends email to our group:

SET…

Read more

I wrote the following query that returns the list of all CLR functions/procedures/objects exists inside your database:

SELECT o.object_id AS [Object_ID]
      ,schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName] 
      ,o.type_desc AS [CLRType] 
      ,o.create_date AS [Created] 
      ,o.modify_date AS [Modified] 
      ,a.permission_set_desc AS [CLRPermission] 
FROM sys.objects o 
INNER JOIN sys.module_assembly_usages ma
    ON o.object_id =…

Read more

We can either use SQL Server Management Studio or use Transact-SQL query to find any SQL Agent jobs that have been setup without notification operator.

The following are the steps to find SQL Agent jobs without notification operator via SQL Server Management Studio:

1. In Object Explorer, expand SQL Agent, then…

Read more

Dedicated Administrator Connection (DAC) allows administrators emergency access to SQL Server via SQL Server Management Studio (SSMS) or via command prompt (SQLCMD utility) when SQL Server is not responding to normal connections, so that they can execute the diagnostic functions (typical set DBCC commands, DMVs and system stored procedures) to… Read more

Another potential problem that you need to tackle as a DBA is running out of disk space on the hard disk that contains tempdb.This is because SQL Server makes extensive use of tempdb when:

• Tracking versions for row-versioning concurrency.
• Performing bulk load operations on tables with triggers enabled.
• Running…

Read more

SQL Server 2012 introduces user-defined server roles. These user-defined server roles are similar to fixed server roles with only difference is that they are created by SQL Server Administrator. SQL Administrators can create, delete or manage these user-defined server roles using T-SQL or SQL Server Management Studio. User-defined roles can… Read more

Read more