SQLRx - The Daily Dose

May 2010 Tip of the Month

SQL Server Administration:  List all principal and object permissions with the following query.  Use this to periodically check that permissions have been properly set for access to objects in your databases.

SELECT USER_NAME(p.grantee_principal_id) AS principal_name,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp

ON p.grantee_principal_id = dp.principal_id

Add comment May 13th, 2010

April 2010 Tip of the Month

I am a bit behind on updating the tip of the month.  =:(

SQL Server Development:  Take apart delimited strings with ease with a function that is commonly found in Visual Basic.  Pass a delimited string into the fnSplit function and it will output the string as a table that can be queried. 

The function is called by this statement: 

SELECT * FROM dbo.fnSplit(’SQLRx,Solves,Performance,Problems’,',’)

CREATE FUNCTION [dbo].[fnSplit] (@String VARCHAR(8000), @Delimiter CHAR(1))       
      RETURNS @temptable TABLE (items VARCHAR(8000))         
      AS         
      BEGIN             
            DECLARE @idx INT              
            DECLARE @slice VARCHAR(8000)  
                       
            SELECT @idx = 1                  
                  IF LEN(@String)<1 OR @String IS NULL  RETURN 
                             
            WHILE @idx!= 0             
            BEGIN                 
                  SET @idx = CHARINDEX(@Delimiter,@String)                 
                  IF @idx!=0                     
                        SET @slice = LEFT(@String,@idx - 1)                 
                  ELSE                    
                        SET @slice = @String
                                        
                  IF(LEN(@slice)>0)                
                        INSERT INTO @temptable(items) VALUES(@slice)
                                        
                  SET @String = RIGHT(@String,LEN(@String) - @idx)                 
                  IF LEN(@String) = 0 BREAK             
            END     
      RETURN         
      END

1 comment April 1st, 2009

Windows Event Log scraper for SQL 2005 & SQL 2008

Here is a handy Windows Event Log scraper for SQL 2005 & SQL 2008!  You will have to download the scripts because my blog editor did not like the the html in my code.
 

I feel that a common check that any good DBA does on each SQL Server they are responsible for should be regularly checking the Windows Event Logs for errors and warnings.  Sometimes you can find evidence of issues that could eventually cause an outage.  And, SQL Server writes many things to the Event Log, so it is a good thing to take a look in these logs.
 

Since I am someone who likes to have the information that I want just handed to me, I made a way for the Event Log to be scraped and sent to me on a regular basis.  This makes my job easier in that I cannot forget to do this task since it lands in my e-mail inbox every day.  I thought I would share this for all the other DBA’s who are into convenience.
 

The Event Log can be queried using vbscript and the Win32_NTLogEvent WMI event class.
 

Win32_NTLogEvent WMI event class info -  http://msdn.microsoft.com/en-us/library/aa394226(VS.85).aspx
 

I created a vbscript like the one I have uploaded that queries the Win32_NTLogEvent WMI event class and places the results into a SQL table.  I have this set to only query events from the past day because looking through several days of events can be very time consuming.  And this also keeps the e-mailed output small enough to pass through our Exchange server with no issues. Download and save EventLogScraper.txt into a .vbs file. EventLogScraper VBScript  I named mine EventLogScraper.vbs. 

Now you have to create the table in SQL for the events.  Download and execute the script TableWinEventLog.txt to create the WinEventLog table.   WinEventLog SQL Script

You can execute the vbscript by either double clicking on the EventLogScraper.vbs file or by opening a command window and using the cscript command along with the path to the  EventLogScraper.vbs file.  Like this: cscript E:\Scripts\ EventLogScraper.vbs

If you query the WinEventLog table you will now only have the errors and warnings to review.  However, since I do not want to have to remember to run the script, I put the cscript command into a SQL stored procedure.  I also wanted to be able to read the output easily so I formatted the output in HTML and have it sent to me in an e-mail.  Download and execute the script ProcLoadWinEventLog.txt to create the LoadWinEventLog stored procedure.  LoadWinEventLog SQL Script     

Execute the stored proc in a SQL job:  EXEC DBAAdmin.dbo.LoadWinEventLog
 
The output will look like this when e-mailed: