Windows Server Uptime


Start -> Run -> cmd.exe
systeminfo | find "System Up Time"


INSERT INTO WHERE NOT EXISTS

-- If @UserID and @ServerID already exists
-- in table [tblSysAdmins], the values
-- will not be inserted

INSERT INTO [tblSysAdmins] ([UserID], [ServerID], [Authorize])
SELECT @UserID, @ServerID, @Authorize
WHERE NOT EXISTS
(SELECT *
FROM tblSysAdmins
WHERE [UserID] = @UserID
AND [ServerID] = @ServerID )

Query to get Users on SQL Server 2005

CREATE TABLE #TEMP(
UserName nvarchar(200),
GroupName nvarchar(50),
LoginName nvarchar(200),
DefDBName nvarchar(50),
DefSchemaName nvarchar(50),
UserID nvarchar(10),
SID nvarchar(200))

DECLARE @ID INT, @LAST INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)

SET @ID = (SELECT MIN(DATABASE_ID) FROM sys.databases)
SET @LAST = (SELECT MAX(DATABASE_ID) FROM sys.databases)

WHILE @ID <= @LAST 
 BEGIN 
   SET @DB_NAME = (SELECT NAME 
                   FROM sys.databases 
                   WHERE database_id = @ID)


    IF @DB_NAME IS NOT NULL 
      BEGIN 
        SET @SQL = 'INSERT INTO #TEMP 
        EXEC [' + @DB_NAME + '].[dbo].[sp_helpuser]' 
        EXEC (@SQL) 
      END 


  SET @ID = @ID + 1 
 END


 SELECT UserName 
 FROM #TEMP 
 WHERE UserName NOT IN 
  ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA') 
 GROUP BY UserName


 DROP TABLE #TEMP

Query to get Users from SQL Server 2008

CREATE TABLE #TEMP(
UserName nvarchar(100),
RoleName nvarchar(50),
LoginName nvarchar(50),
DefDBName nvarchar(50),
DefSchemaName nvarchar(50),
UserID nvarchar(10),
SID nvarchar(200))

DECLARE @ID INT, @LAST INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)

SET @ID = (SELECT MIN(DATABASE_ID) FROM sys.databases)
SET @LAST = (SELECT MAX(DATABASE_ID) FROM sys.databases)

WHILE @ID <= @LAST 
 BEGIN 
   SET @DB_NAME = (SELECT NAME 
                   FROM sys.databases 
                   WHERE database_id = @ID) 


     IF @DB_NAME IS NOT NULL 
       BEGIN 
         SET @SQL = 'INSERT INTO #TEMP 
         EXEC [' + @DB_NAME + '].[dbo].[sp_helpuser]' 
         EXEC (@SQL) 
       END 
   
     SET @ID = @ID + 1 
 END 


SELECT UserName 
FROM #TEMP 
WHERE UserName NOT IN 
  ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA') 
GROUP BY UserName 


DROP TABLE #TEMP

Query to Retrieve Job Info from msdb Database


SELECT
-- Format date
((SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 1,4) + '-' +
SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 5,2) + '-' +
SUBSTRING(CONVERT(VARCHAR(20), H.run_date), 7,2)) + ' ' +

-- Format time
(
-- Hours
CASE
WHEN CONVERT(VARCHAR(20), H.run_time/10000) > 12 THEN CONVERT(VARCHAR(20), (H.run_time/10000) -12)
WHEN CONVERT(VARCHAR(20), H.run_time/10000) = 0 THEN '12'
ELSE CONVERT(VARCHAR(20), H.run_time/10000)
END + ':' +

-- Minutes
CASE LEN(CONVERT(VARCHAR(20), H.run_time/100%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_time/100%100)
ELSE CONVERT(VARCHAR(20), H.run_time/100%100)
END + ':' +

--Seconds
CASE LEN(CONVERT(VARCHAR(20), H.run_time%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_time%100)
ELSE CONVERT(VARCHAR(20), H.run_time%100)
END

+ ' ' +

-- AM or PM
CONVERT(VARCHAR(3),
CASE
WHEN CONVERT(VARCHAR(20), H.run_time/10000) > 12
THEN ' PM'
ELSE ' AM'
END

))) AS [Last Run Time],

S.NAME AS [Job Name], H.Message,

-- Format Status
CASE H.run_status
WHEN 1 THEN 'Success'
ELSE 'Failure'
END AS Status,

-- Format Run_Duration
(CASE LEN(CONVERT(VARCHAR(20), H.run_duration/10000))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration/10000)
ELSE CONVERT(VARCHAR(20), H.run_duration/10000)
END + ':' +

CASE LEN(CONVERT(VARCHAR(20), H.run_duration/100%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration/100%100)
ELSE CONVERT(VARCHAR(20), H.run_duration/100%100)
END + ':' +

CASE LEN(CONVERT(VARCHAR(20), H.run_duration%100))
WHEN 1 THEN '0' + CONVERT(VARCHAR(20), H.run_duration%100)
ELSE CONVERT(VARCHAR(20), H.run_duration%100)
END) AS [Run Duration]

FROM [msdb].[dbo].[sysjobhistory] AS H
INNER JOIN [msdb].[dbo].[sysjobs] AS S
ON S.job_id = H.job_id
WHERE S.enabled = 1
AND H.step_id = 0
ORDER BY H.RUN_DATE desc, H.RUN_TIME DESC