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.
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
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()
Reporting Services might encounter the below error, suddenly even though it was working perfectly. There can be many reason for this.
The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. (rsReportServerDisabled)
The Symmetric Key might have got corrupted.