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
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' ;
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','ALTER_INDEX')
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
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
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
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 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 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
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)
-- */