When you are trying to connect to Integration Services from a Remote machine or To a Remote Machine from Management Studio, your might come across a error saying "connecting to the integration services on the computer failed with the following error access is denied", further Inline Error might be as follows.

"Connecting to the Integration Services service on the computer "" failed with the following error: "The Service cannot be started, either because it is disabled or because it has no enabled devices associated with it."

Very often when you are trying to subscribe for a SSRS Report, you may end up with the error saying "Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid.". Sample screenshot as shown below.

How many times do we need to create a Login on SQL Server installed. In an enterprise level a DBA might be doing this task several times a day.

Below is a small script which will create a  Login from a Windows Group or Account and tricky part is you can assign the Server Level Roles to this Account on the fly.

How many times we come across the situation where we need to check when a particular database object was created. Say you want to get to know when a Table was created, A stored procedure was created and so on.

Microsoft has provided us with a very simple method to achieve this. You can simply run the below query to get the created date of a particular database object. Just Replace ObjectTobeSearched with the name you want to search

select crdate as [Object_Created_Date] from dbo.sysobjects where [name] = 'ObjectTobeSearched'

As a DBA you need to know sometimes on which TimeZone your SQL Server is installed. This means the Machine/Server is installed on which TimeZone on which SQL Server is installed. There are some methods to achieve this. You can query to Windows servers using WMI and get the details. You can RDP to Machine and get the timezone of server. I found out a simple way for me is to connect to SQL Server instance using Management Studio and just run the below T-SQL. 

Note: This did not work for me on SQL Server 2000, you might want to try out your luck.

There will be cases when a particular user has created some chains of processes and those processes needs to be killed. Searching and kill all the session if they are more is bit a tidious task. Below is the simple query will give you the Kill command for all session for a particular login.

select 'Kill '+cast(spid as varchar(10)) from sysprocesses where loginame = 'LoginNameSessionToBeKilled'

Below a small piece of code to delete the SQL Job from Agent using TSQL. This code will make sure the job is deleted/dropped cleanly.

USE [msdb]
GO
DECLARE @MyJobID uniqueidentifier
select @MyJobID = job_id from sysjobs where name = 'JOBNAMETOBEDELETED'
EXEC msdb.dbo.sp_delete_job @job_id=@MyJobID, @delete_unused_schedule=1
GO

When you are creating a Windows Login in SQL Server go to search and you type in name and click check names and you end you with error something like below.

An object (User, Group, or built-in security proncipal) with the following name cannot be found.

You have created a Login in SQL Server of type SQL Server Authentication. You did not wanted to Enforce Policy Option opted, but you forgot to uncheck the option and created the login.

Later you realize that you did not wanted the Enforce Policy option. You go to Management Studio and try to uncheck the Enforce Policy option and you will end up with below error.

"The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON."

As a SQL Server DBA one of the key things is to maintain a serverlist inventory.

Below is a simple yet powerful query to have this details at hand. Further details can be included and pulled using SERVERPROPERTY()