Category Archives: misc

Drop and Recreate Synonyms

This script is useful to script out all synonyms on a database before restoring on top another copy from another environment.

It is also useful to script out what's there, examine it, and see if any modifications are needed. See this one to verify synonyms

Rememer to change the output to "results to text".

 

SET NOCOUNT ON
SELECT
'IF EXISTS (SELECT NAME FROM SYS.OBJECTS WHERE NAME = '''+NAME+''')
DROP SYNONYM ['+NAME+'];
CREATE SYNONYM ['+NAME+'] FOR '+BASE_OBJECT_NAME+'

'
FROM sys.synonyms


Verify Synonyms

The following script gets a list of the synonyms, parses them, and then the checks if the existence of the object on the sys.objects table.

Any errors encountered are assumed as "not a valid synonym"

/*
Author: Miguel Quintana

Description:
Reads all SYNONYMS on the database and test if they are valid.
It checks their EXISTENCE on the SYSOBJECTS table
and uses the Link Server if available

No configuration needed. Just run it under the DB that you are testing Synonyms

*/


-- this variables are for the cursor
DECLARE @NAME NVARCHAR(100)
  ,@SERVERNAME NVARCHAR(20)
  ,@DBName NVARCHAR(100)
  ,@SCHEMA NVARCHAR(100)
  ,@OBJECTNAME NVARCHAR(1000)
  ,@BaseObjectName NVARCHAR(1000)
-- This are to process the data
DECLARE @CMD NVARCHAR(MAX)
DECLARE @RESULTS TABLE (
  VALUE NVARCHAR(100)
  ,SYNONYM NVARCHAR(100)
  ,BaseObjectName NVARCHAR(100)
  ,Link_SERVER NVARCHAR(100)
  )
DECLARE CUR CURSOR
FOR
SELECT NAME
  ,PARSENAME(base_object_name, 4) ServerName
  ,COALESCE(PARSENAME(base_object_name, 3), DB_NAME(DB_ID())) AS dbName
  ,COALESCE(PARSENAME(base_object_name, 2), SCHEMA_NAME(SCHEMA_ID())) AS schemaName
  ,PARSENAME(base_object_name, 1) AS objectName
  ,BASE_OBJECT_NAME
FROM sys.synonyms
order by ServerName DESC, NAME



OPEN CUR
FETCH NEXT FROM CUR INTO 
   @name
  ,@SERVERNAME
  ,@DBNAME
  ,@Schema
  ,@ObjectName
  ,@BaseObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Test SYNONYMS that use Link Servers
  IF @SERVERNAME IS NOT NULL
  BEGIN
    SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''')
    SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + '''''
    ELSE
    SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + ''''''') AT [' + @SERVERNAME + ']'
    print @cmd
	BEGIN TRY
      INSERT INTO @RESULTS
	  EXEC (@cmd)
    END TRY
    -- IF the exec fails for any reason, the SYNONYM is not valid
    BEGIN CATCH
      INSERT INTO @RESULTS
      VALUES (
        'NOT A VALID SYNONYM'
        ,@NAME
        ,@BaseObjectName
        ,@SERVERNAME
        )
    END CATCH
  END
  -- Test SYNONYMS that do not use Link Servers
  ELSE
  BEGIN
    SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''')
    SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''','''' ''''
    ELSE
    SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @@SERVERNAME + ''''''')'
    print @cmd    
	
	BEGIN TRY
      INSERT INTO @RESULTS
      EXEC (@cmd)
    END TRY
    -- IF the exec fails for any reason, the SYSNONYM is not valid
    BEGIN CATCH
      INSERT INTO @RESULTS
      VALUES (
        'NOT A VALID SYNONYM'
        ,@NAME
        ,@BaseObjectName
        ,@@SERVERNAME
        )
    END CATCH
  END
  FETCH NEXT FROM CUR INTO
     @name
    ,@SERVERNAME
    ,@DBNAME
    ,@Schema
    ,@ObjectName
    ,@BaseObjectName
END
CLOSE CUR
DEALLOCATE CUR

SELECT Value
  ,[SYNONYM]
  ,UPPER(Link_SERVER) 'Link Server'
  ,BaseObjectName
  ,COALESCE(ss.data_source,'') 'Data Source'
FROM @RESULTS r
LEFT JOIN master.sys.servers ss on r.Link_SERVER = ss.name


ORDER BY VALUE


Link Servers are supported.

 

Sometimes, it will report as not valid SYNONYM even though you are able to use them (select top 1 * from <sn_syonym_name>). but users without sysadmin cannot.

There are many reasons why that would happen, usually drop & recreate fixes the issue or even running the following:

 


EXEC sp_serveroption @server = '',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

 

Querying CommandLog

If you have implemented Ola Hallenger scripts, http://ola.hallengren.com/, you will have a CommandLog table where you keep track of execution times.

The following query will help you get information from it, very useful for tracking backup failures:

USE DBA
SELECT
DatabaseName,
StartTime,
DATEDIFF(MINUTE,StartTime,EndTime) 'Duration in Min',
EndTime,
ErrorNumber,
ErrorMessage,
Command,
replace(
cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(1000))
,'errorlog','')
FROM DBO.COMMANDLOG
WHERE 1=1
--and StartTime > dateadd(dd,-1,getdate())
and CommandType in ('BACKUP_DATABASE','BACKUP_LOG','RESTORE_VERIFYONLY')
--and CommandType in ('xp_create_subdir','xp_delete_file')
--and CommandType in ('DBCC_CHECKDB')

--and ErrorNumber <> 0

order by ID desc

Below, we take advantage of window functions and we can see the last time a database had a DBCC checkdb done, along with its duration

 

    USE DBA
    
	;with Last_DBBC as
	(
	SELECT
    DatabaseName,
    StartTime,
    EndTime,
	ROW_NUMBER() over (PARTITION by databasename order by StartTime desc) 'rownumber'
    FROM DBO.COMMANDLOG
    WHERE 1=1
	and CommandType in ('DBCC_CHECKDB')
   	)
	select
	DatabaseName,
	StartTime, EndTime,
	DATEDIFF(MI,StartTime,EndTime) 'Duration in Min',
	DATEDIFF(DD,StartTime,GETDATE()) 'Age of DBCC in days'
	from Last_DBBC
	where 1=1
	and rownumber = 1

Link Server Information

 

A very simply script to list all link servers available on the SQL Server


SELECT 
 ss.name 
,ss.product 
,ss.provider
,ss.data_source 
,case sl.uses_self_credential 
	when 1 then 'Uses Self Credentials' 
    else coalesce(ssp.name,'') 
	end 'Local login'
, coalesce(sl.remote_name,'') 'Remote Login Name'
,case ss.is_rpc_out_enabled
	when 1 then 'True'
	else 'False'
	end 'RPC Out Enabled'
 ,case ss.is_data_access_enabled 
   when 1 then 'True'
   else 'False'
   end 'Data Access Enabled'
,ss.modify_date 
FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp ON ssp.principal_id = sl.local_principal_id
 where 1=1
Order by ss.name

 

SysAdmins on SQL

The following query well known query will list all logins with SYSADMIN access to a sql Server:

    SELECT  p.name 'LoginName',
	    p.type_desc

    FROM    sys.server_principals p
    JOIN	sys.syslogins s ON p.sid = s.sid
	WHERE   
		p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
		-- Logins that are not process logins
        AND p.name NOT LIKE '##%'
		-- exclude disabled
		and p.is_disabled = 0
		-- Logins that are sysadmins
		AND s.sysadmin = 1
ORDER BY Type

The problem is that the query may return Windows Groups, and we might want to know membership to those groups.

Which is why we rather run the following query instaead:


/*
DESCRIPTION:  List users with SYSADMIN access 
If a Windows group has sysadmin access
it will list its members

Lists only enabled users at SQL level
cannot check if valid AD account

CONFIGURATION: None
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012
DOES NOT WORK

*/

DECLARE @logininfo table
( [LoginName] sysname
, [type] char(8), [priv] char(9), [mpln] sysname
, [GroupMembership] sysname
)

DECLARE @GroupName sysname

DECLARE cur CURSOR FOR

	SELECT
			p.name
    FROM    sys.server_principals p
    JOIN	sys.syslogins s ON p.sid = s.sid
	WHERE   
			p.type_desc IN ('WINDOWS_GROUP')
			-- exclude disabled
			and p.is_disabled = 0
			-- Logins that are sysadmins
			AND s.sysadmin = 1

OPEN cur
FETCH NEXT FROM cur INTO @GroupName
WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT INTO @logininfo
	EXEC master..xp_logininfo 
	@acctname = @GroupName,
	@option = 'members'

FETCH NEXT FROM cur INTO @GroupName
END
CLOSE cur
DEALLOCATE cur

SELECT 
	LoginName,
	'WINDOWS_GROUP' 'Type_Desc',
	GroupMembership 'GroupMembership'
FROM @logininfo  
UNION ALL
SELECT  p.name 'LoginName',
		p.type_desc,
		'' 'GroupMembership'
    FROM    sys.server_principals p
    JOIN	sys.syslogins s ON p.sid = s.sid
	WHERE   
		p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN')
		-- Logins that are not process logins
        AND p.name NOT LIKE '##%'
		-- exclude disabled
		and p.is_disabled = 0
		-- Logins that are sysadmins
		AND s.sysadmin = 1
ORDER BY GroupMembership, Type_Desc
        

 

Telnet to Send Mail

One of the tools available to test database mail is to take SQL out of the equation and test directly using Telnet.

The first step is to try to Telnet, using the right port.

These commands are run from the CMD prompt on the host where you want to send emails from, and since it's old technology, you cannot make typos. The delete key most of the time does not work so you will have to start over.

Let's say the SMTP server is mail.domain.ext and you are sending emails from a "dummy" account called "admin@domain.com" to your own account "sqldba@email.com"

telnet mail.domain.ext 25

If we are not able to even Telnet to the SMTP server, there might be Network or Firewall issues. Otherwise it should open a new window, and a black prompt

Trying 10.152.152.152...
Connected to mail.domain.ext.
Escape character is '^]'.
220 mail.domain.ext ESMTP Sendmail ?version-number?; ?date+time+gmtoffset?

Then start your commands:

HELO Mail.com
250 mail.domain.ext Hello local.domain.name [loc.al.i.p], pleased to meet you

Don't worry too much about your local domain name although you really should use your exact fully qualified domain name as seen by the outside world the mail server has no choice but to take your word for it as of RFC822-RFC1123. This should give you:

250 mail.domain.ext Hello local.domain.name [loc.al.i.p], pleased to meet you

Enter your email info:

MAIL FROM: admin@domain.ext
250 2.1.0 mail@domain.ext... Sender ok
RCPT TO: sqldba@email.com
250 2.1.0 mail@otherdomain.ext... Recipient ok

DATA
Type in something here
.

To end the message enter a single "." on a line on it's own.

The mail server should reply with: 250 2.0.0 ???????? Message accepted for delivery.

If we are able to Telnet, but we are not allowed to send emails ("unable to relay message"), it means that we are talking to the SMTP server, but we are not part of the Account Control List (ACL) on the SMTP server. Ask the Admin to add you to the ACL so we can relay emails to your own network (provide the part after the @ sign of your email).

You can close the connection by issuing the QUIT command. The mailserver should reply with something like:

221 2.0.0 mail.domain.ext closing connection Connection closed by foreign host.

 

Database Mail Troubleshooting

Database Mail Troubleshooting can be tricky.

The first step is to Telnet to the SMTP server and see if you can send emails, and receive them.

 

After verifying that Telnet works, we usually start by getting the name of the profile and account details to test sending Email:


declare @subj nvarchar(100)
set @subj = 'Email from' + @@servername

EXEC sp_send_dbmail
  @profile_name='DBA_PROFILE'
, @recipients='sqldba@email.com'
, @subject=@subj
, @body='Please review this email Test 1'
--,  @file_attachments='E:\MSSQLDBA\Scripts\backup_report\log\bk_report.csv'
go

Then we would look at the actual queue:


-- Mail Queue: sent ITEMS
select * from  msdb.dbo.sysmail_mailitems
order by send_request_date desc

-- Display Error Queue
SELECT * FROM msdb.dbo.sysmail_event_log;

 

This query tracks specific emails and their error, if any

use MSDB
select a.mailitem_id, a.subject, a.body, a.sent_status, b.description
from sysmail_allitems a
left join sysmail_log b on a.mailitem_id = b.mailitem_id
order by a.mailitem_id desc

order by a.mailitem_id desc

Database Mail rarely breaks, but sometimes there are delays on the queue mails and you might need to clear the queue. In one occasion, I received the error:

The DB Mail gave the following error: Object Instance Not Set to an Instance of an Object.

The Maintenance Plans said:

Failed to acquire connection "Local Server Connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

This happened right after a Windows Update that modified .Net Framework. Reverting to the old version or even repairing it fixed DB Mail

As I learn more about it, I'll be adding more

Database Mail Info

Database Mail profile information, along with account names and SMTP server


/*
Compatibility list:
MSSQL2005 - MSSQL2008 - MSSQL2008R2 - MSSQL2012
 
Does not work
MSSQL2000
 
Update Log:
*/

DECLARE       @DBEngineLogin       VARCHAR(100)
EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N'HKEY_LOCAL_MACHINE',
             @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
              @value_name   = N'ObjectName',
              @value        = @DBEngineLogin OUTPUT

select 
p.name
, p.description
,a.name
, a.description
, a.email_address
, a.display_name
, a.replyto_address
, s.servertype
, s.servername
, s.port
, coalesce(s.username,@DBEngineLogin) -- If the username is empty, it is authenticating using Windows Credentials
, s.use_default_credentials
, s.enable_ssl
from msdb.dbo.sysmail_profile p
inner join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id
inner join msdb.dbo.sysmail_account a on a.account_id = pa.account_id
inner join msdb.dbo.sysmail_Server s on a.account_id = s.account_id
GO

Generate a Random Password

Useful to generate random passwords


    /*
    DESCRIPTION: 
    Generates a (pseudo)random string of chars to be used in a password
     
    Configuration: on top. Length and desired chars
     
    Compatibility list:
    MSSQL2005
    MSSQL2008
    MSSQL2008R2
    MSSQL2012
     
    Not tested:
    MSSQL2000
     
    Update Log:
    */
     
    set nocount on
     
    declare
    	@plength int,
    	@password varchar(50),
    	@string varchar(72), 
    --52 possible letters + 10 possible numbers + up to 20 possible extras
    	@numbers varchar(10),
    	@extra varchar(20),
    	@index tinyint
     
     /* configuration  */
     
    set @plength = 10
     
     
    -- eliminate 0, 1, i, l, o, o to make the password more readable
    set @string = 'abcdefghjklmnpqrstuvwxyzabcdefghijkmnpqrstuvwxyz' -- option @charset = 0
    set @numbers = '23456789'
    set @extra = '>_!@#$%&=?<>' -- special characters
     
     
    /* end configuration  */
     
     
    set @string = @string + @numbers + @extra
     
    set @password = ''
    	
    	while (@plength > 0)
    	begin
    		set @index = (abs(checksum(newid())) % len(@string)) + 1 
    		
    		set @password = @password + substring(@string, @index, 1)
     
    		set @plength -= 1 --set @plength = @plength - 1
    	end
     
     
    /*
    -- This section actually changes the password of the SA account
    declare @cmd nvarchar(100)
    set @cmd = 'alter login [sa] with password=n'''+@password+''' unlock'
     
    execute (@cmd)
    -- */