Pages

Monday, April 25, 2011

SQL Server Agent Proxies

SQL Server Agent Proxies are very helpful in executing some jobs. I had came across a situation where in I was scratching my head so as to how to accomplish a task which I wanted to run as SQL server Agent Job. First let me give some brief description about proxies.

SQL Server Agent uses proxies to manage security contexts. A proxy can be used in more than one job step. A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.

Now lemme give you a small example where how exactly a proxy can be helpful.

Say you have around 10 database servers,  both SQL Engine and SQL Agent services on these servers run on different Service Account say Machine Specific. Ex Ser1, Ser2, Ser3 and so on. Now here is the task. You want some operations may be a T-SQL or some script combined into SSIS package to be run on all these services. One way to achieve this is schedule a Job on each of these server and what if you want a consolidated data like health of all servers or logins existing on these servers as single report. This is where your Proxy comes in handy. To achieve this task get a Service Account(A windows Domain user Account), give access to this service account on all servers and then create a credential for this service account and create proxy for this credential on any of the one server and execute this Job or package from one single server and get combined data on one server. By doing so what exactly happens is SQL server agent impersonates the associated User account with proxy against the server on which the script/job needs to be run and since you have provided access to this service account on all server you will be able to run the same. Now lemme guide you through a step wise procedure to create a proxy using T-SQL.

--Detailed Steps to create a SQL proxy using T-SQL
--Step 1 - Create a credential for proxy

USE MASTER
GO
--Drop and create the demo credential if it is already existing
IF EXISTS (SELECT * FROM sys.credentials WHERE name = N'ProxyDemoCredentials')
BEGIN
DROP CREDENTIAL
[ProxyDemoCredentials]
END
GO
CREATE CREDENTIAL
[ProxyDemoCredentials]
WITH IDENTITY = N'YourDomain\ProxyDemoServiceAccount',
SECRET = N'ServiceAccountPasswordHere'
GO
--End of Step 1

--Step 2 - Create a demo proxy account
USE msdb
GO
--Drop the demo proxy if it is already existing
IF EXISTS (SELECT * FROM msdb.dbo.sysproxies WHERE name = N'ProxyDemo')
BEGIN
EXEC
dbo.sp_delete_proxy
@proxy_name = N'ProxyDemo'
END
GO
--Creating a demo proxy and assigning the demo credential to the same
EXEC msdb.dbo.sp_add_proxy
@proxy_name = N'ProxyDemo',
@credential_name=N'ProxyDemoCredentials',
@enabled=1
GO
--You Need to Enable a Proxy before using it which can be done using below step.
EXEC msdb.dbo.sp_update_proxy
@proxy_name = N'ProxyDemo',
@enabled = 1
GO
--End of Step 2

--Step 3 Granting created demo proxy to SQL Agent subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@proxy_name=N'ProxyDemo',
@subsystem_id=11 --subsystem 11 is for SSIS. You can get info using EXEC sp_enum_sqlagent_subsystems
GO
--End of Step 3

--Step 4 Granting access of security principals(service account) to demo proxy created
USE msdb
GO
--Grant proxy account access to security principals
EXEC msdb.dbo.sp_grant_login_to_proxy
@proxy_name=N'ProxyDemo'
,@login_name=N'YourDomain\ProxyDemoServiceAccount'
--,@fixed_server_role=N'<fixed_server_role name>' if any roles
--,@msdb_role=N'<msdb_role name>' if any MSDB roles
GO
--End of Step 4.


Now just use this proxy under Run AS in job step for using SSIS package.




Sunday, April 24, 2011

Get Information on All Jobs running on a SQL server Instance.

The SQL server Jobs which are running on a Instance are very Important. As a DBA you need to take care of these Jobs and have all information about the Job. Your will get information on Jobs is SysJobs but again not in the way you want. So just thought of pulling the Job information and below is the stored Procedure for the same.

This procedure give information on 

[job_id],
[Jobs],
[Category],
[Owner],
[Schedule Name],
[enabled],
[Frequecy],
[DateCreated],
[StartDate],
[NextRunDate],
[NextRunTime]

USE YourDatabaseName
GO
IF OBJECT_ID('dbo.spGetJobInfo') IS NOT NULL
DROP PROCEDURE dbo.spGetJobInfo
GO
CREATE PROCEDURE dbo.spGetJobInfo
AS
SET NOCOUNT ON
DECLARE @jobid VARCHAR(50)
DECLARE @sql NVARCHAR(1000)
CREATE TABLE #Jobs_Schedule(
[schedule_id] [INT],
[name] [SYSNAME],
[enabled] [INT],
[freq_type] [INT],
[freq_interval] [INT],
[freq_subday_type] [INT],
[freq_subday_interval] [INT],
[freq_relative_interval] [INT],
[freq_recurrence_factor] [INT],
[active_start_date] [INT],
[active_end_date] [INT],
[active_start_time] [INT],
[active_end_time] [INT],
[date_created] [DATETIME],
[desc] VARCHAR(1000),
[next_run_date] [INT],
[next_run_time] [INT],
[schedule_uid]    VARCHAR(1000),
[job_count] [INT]
) ON [PRIMARY]
DECLARE c1 CURSOR FOR SELECT job_id FROM msdb..sysjobs
OPEN c1
FETCH NEXT FROM c1 INTO @jobid
WHILE @@fetch_status=0
BEGIN
INSERT #Jobs_Schedule EXEC ('msdb..sp_help_jobschedule @job_id='''+ @jobid+''',@include_description=1')
FETCH NEXT FROM c1 INTO @jobid
END
CLOSE c1
DEALLOCATE c1

CREATE TABLE #TempJoin
(
job_id UNIQUEIDENTIFIER,
Jobs VARCHAR(250),
Category VARCHAR(250),
OwnerName  VARCHAR(100),
ScheduleName  VARCHAR(250),
ISenabled  CHAR(4),
Frequency VARCHAR(250),
Date_Created  DATETIME,
Active_Start_Date INT,
Next_Run_Date  INT,
Next_Run_Time INT
)
INSERT INTO #TempJoin
(
job_id,
Jobs,
Category,
OwnerName,
ScheduleName,
ISenabled,
Frequency,
Date_Created,
Active_Start_Date,
Next_Run_Date,
Next_Run_Time
)
(
SELECT 
a.[job_id],
a.[Jobs],
a.[Category],
l.[name] AS [Owner],
a.[Schedule Name],
a.[enabled],
temp.[desc] AS [Frequecy],
temp.date_created AS [DateCreated],
temp.active_start_date AS [StartDate],
temp.next_run_date AS [NextRunDate],
temp.next_run_time AS [NextRunTime]
FROM #Jobs_Schedule AS temp 
RIGHT OUTER JOIN 
(SELECT
j.job_id,
j.[name] AS [Jobs], 
j.owner_sid, 
c.[name] AS [Category], 
js.schedule_id, 
s.[name] AS [Schedule Name], 
CASE j.enabled WHEN 1 THEN 'YES' ELSE 'NO' END AS [enabled] 
FROM msdb..syscategories AS c 
RIGHT OUTER JOIN 
msdb..sysjobs AS j ON c.category_id = j.category_id
LEFT OUTER JOIN 
msdb..sysjobschedules AS js ON j.job_id = js.job_id
LEFT OUTER JOIN 
msdb..sysschedules AS s ON js.schedule_id = s.schedule_id)
AS a ON temp.schedule_id = a.schedule_id 
LEFT OUTER JOIN 
sys.server_principals AS l ON a.owner_sid = l.sid)

CREATE TABLE #TempJoin1
(
Name VARCHAR(250),
job_id UNIQUEIDENTIFIER,
LastRunStatus VARCHAR(25),
LastRunDate INT,
LastRunTIme INT,
)
INSERT INTO #TempJoin1
(
Name,
job_id,
LastRunStatus,
LastRunDate,
LastRunTIme
)
(
SELECT  
j.[name] AS [JobName],
j.[job_id],  
run_status = CASE h.run_status  
WHEN 0 THEN 'Failed' 
WHEN 1 THEN 'Succeeded' 
WHEN 2 THEN 'Retry' 
WHEN 3 THEN 'Canceled' 
WHEN 4 THEN 'In progress' 
END, 
h.run_date AS LastRunDate,   
h.run_time AS LastRunTime 
FROM msdb..sysjobhistory h  
INNER JOIN
msdb..sysjobs j ON h.job_id = j.job_id  
WHERE h.instance_id IN  
(SELECT MAX(h.instance_id)  
FROM msdb..sysjobhistory h GROUP BY (h.job_id))
)

SELECT 
t1.Jobs,
t1.OwnerName,
t1.Category,
t1.Date_Created,
t1.Active_Start_Date,
t1.ISenabled,
t2.LastRunDate,
t2.LastRunTIme,
t2.LastRunStatus
FROM #TempJoin t1
INNER JOIN #TempJoin1 t2
ON t1.job_id = t2.job_id

DROP Table #Jobs_Schedule
DROP Table #TempJoin
DROP Table #TempJoin1
GO


T-SQL to Get Default Audit Level of SQL server Instance

I have come across situations where you need to go and look for Default Audit Level for Installed Instances or servers. You can do this by getting info in server instances. Now what if you have around 100 server and instance. For this I could find a easy way and that’s a T-SQL function to get the default Audit Level for Instance.

Function:

USE YourDatabaseName
GO
CREATE FUNCTION dbo.fn_get_default_path()
RETURNS int
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @path int, @value_name nvarchar(20);

    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');

    -- sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';

    SET @value_name = N'AuditLevel'
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;   
    RETURN @path;
END
GO

Now to get the value run the below script:

DECLARE @Temp varchar(max),@Temp1 varchar(max)
SET @Temp = dbo.fn_get_default_path()
If @Temp = 0
Begin
Set @Temp1 = 'NONE'
END
IF @Temp = 1
BEGIN
SET @Temp1 = 'Successful Logins Only'
END
If @Temp = 2
BEGIN
SET @Temp1 = 'Failed Logins Only'
END
IF @Temp = 3
BEGIN
SET @Temp1 = 'Both Successful and Failed Logins'
END
SELECT @Temp1
GO

Monday, April 18, 2011

Get Free Disk Space for SQL server 2000

As a DBA you always need to monitor various metrics of a Database server. Disk space is one such metric you gotta watch out for. Monitoring Disk space for SQL server 2000 is bit difficult through script. Just happened to write a Script for SQL server 2000 which gives you free disk space in GB for all existing drives on database server.

SET NOCOUNT ON
DECLARE @TEMP AS VARCHAR(4000)
DECLARE @MBfree int
DECLARE @CMD1 varchar(1000)
SET @MBfree = 0
SET @CMD1 = ''
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL,
[FreeSpace] int NOT NULL)
INSERT INTO #tbl_xp_fixeddrives(Drive, [FreeSpace])
EXEC master.dbo.xp_fixeddrives
SELECT @TEMP ='
CREATE FUNCTION dbo.GetDriveSize (@driveletter CHAR(1))
 RETURNS NUMERIC(20)
 BEGIN
  DECLARE  @rs INTEGER, @fso INTEGER, @getdrive VARCHAR(13), @drv INTEGER,
@drivesize VARCHAR(20)


  SET @getdrive = ''GetDrive("'' + @driveletter + ''")''
  EXEC @rs = sp_OACreate ''Scripting.FileSystemObject'', @fso OUTPUT


  IF @rs = 0
   EXEC @rs = sp_OAMethod @fso, @getdrive, @drv OUTPUT
  IF @rs = 0
   EXEC @rs = sp_OAGetProperty @drv,''TotalSize'', @drivesize OUTPUT
  IF @rs<> 0
   SET @drivesize = NULL


  EXEC sp_OADestroy @drv
  EXEC sp_OADestroy @fso


  RETURN @drivesize
 END'
EXEC(@TEMP)
SELECT drive+':'+' '+
CAST(CAST((FreeSpace/(((dbo.GetDriveSize(drive))/1024)/1024))*100.0 as int) as VARCHAR(4000))+'%'
from #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives
SELECT @TEMP = 'USE '
SELECT @TEMP = @TEMP+'master '+'DROP FUNCTION dbo.GetDriveSize'
EXEC (@TEMP)
SET NOCOUNT OFF
GO