Cannot find the symmetric key 'x', because it does not exist or you do not have permission - Error in SQL Server by serverku

Recently I ran into an issue while executing stored procedure and issue was with permission of symmetric keys. Here I am sharing an error and fix of that issue. Following is the script run against the user database in which we got a problem. Here SymmetricKeyCert is a certificate name and SymmetricKeyTest is a symmetric key name in the example.

Error :
Cannot find the symmetric key 'x', because it does not exist or you do not have permission.

Fix : 

USE <DB Name>
GO

-- SELECT * FROM sys.certificates
-- Find associated certificate name 
-- Grant permission to SYMMETRIC KEY and ON CERTIFICATE 

GRANT CONTROL ON CERTIFICATE :: SymmetricKeyCert TO [UserTest]
GO
GRANT REFERENCES ON SYMMETRIC KEY::SymmetricKeyTest TO [UserTest]
GO
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKeyTest TO [UserTest]
GO

This is a what I got a solution and I would like to request to share some such type of permission related issues and workaround for same, may it helps to readers. Have a great day.

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys' - SQL Server by serverku

Recently, while working with one query to get schedule job status, I used xp_sqlagent_enum_jobs object which gives details of state values of all jobs in msdb database and this is undocumented object. But received an error while running the following query for same,

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa';

Error :

Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

As per error, says it does not have execute permission on mssqlsystemresource database for sys schema.

Solution :
For the solution just need to assign execute permission on dbo.xp_sqlagent_enum_jobs in the master database to user under the query to be run. So the following query needs to be run against master database,

USE master
GO
GRANT EXECUTE ON xp_sqlagent_enum_jobs TO test;

Hope this help you if you will receive such an error and you may enjoy this post. Stay tuned for further posts!

Could not find the Distributor or the distribution database for the local server - Error while posts a tracer token in Replication by serverku

A few days back, I spoke about the manual failover of mirroring and also explained one issue and workaround too. Continuing with the same failover, I want to express one more issue here. This issue is not very critical but it somehow to create an issue while collecting some information for report or any other purpose. Let me elaborate everything here, why and how this error raised.
You all know about system stored procedure sys.sp_posttracertoken which posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics, which we can schedule on some frequency to post tracer tokens. Tracer tokens can be inserted with Replication monitor also,

You can find the tsql code for same below which must be run against the publisher database,

USE publisherdb 
GO

DECLARE @out_tracer_token_id INT 

EXEC sys.Sp_posttracertoken 
  @publication = N'<Publication Name>',  -- Put Publication name here
  @tracer_token_id=@out_tracer_token_id out 

SELECT @out_tracer_token_id 

Error
But after a failover when I tried the same tsql code in the switched publisher database I received an error,

"Could not find the Distributor or the distribution database for the local server.
The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor."

Solution
This script was running fine in the original publisher database before failover. During the investigation as per error message I found sp_helpdistributor was returning NULL values in publisher database. sp_helpdistpublisher also not showing publisher in Distributor server or a server where distribution database belongs to. That means we have to do two things,

1. Configure distributor at publisher.
2. Configure publisher at distributor.

So moving ahead towards the solution and apply below solution,
1. sp_adddistributor which creates an entry in linked server and executed at a publisher in the master database to configure remote distributor,

Use master
GO

EXEC sp_adddistributor  
           @distributor=  '<Distributor>' , -- Put your distributor server name here
           @password= 'testpwd' -- password of distributor_admin 

2. sp_adddistpublisher configures a publisher in distributor server, which executed at the distributor side in the master database,

USE master
GO

EXEC sp_adddistpublisher 
  @publisher= '<Publisher>'  -- Put publisher servername here
 ,@distribution_db= 'distribution'  -- Distribution database name
 ,@security_mode= 1 
 ,@login= 'sa' 
 ,@password= 'testpwd' 

After this workaround I was able to ran this script successfully at the publisher and scheduled for  every 5 minutes, so I can use it for replication latency alert and reports too.  Are you using sys.Sp_posttracertoken system stored procedure? Share your feedback here.

SQL Server is unable to complete the New Publication Wizard - Replication Error by serverku

The last time we saw the issue we faced during setup of replication and it was due to original server name change. We came out from one issue But today I am writing for another issue which I faced in the same for replication configuration. I think this is something bad happen during workaround for the solution earlier, by mistake, something went wrong while old server drop and it were used in replication. Even we will look for the solution for this too. So let us move on the error which I get on initial stage of replication configuration while adding publication using Publication Wizard,

“TITLE: New Publication Wizard
------------------------------
SQL Server is unable to complete the New Publication Wizard.

------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Invalid object name 'msdb.dbo.MSdistributiondbs'. (Microsoft SQL Server, Error: 208)”

It seems these objects getting destroyed while workaround for earlier error. Finally, I got solution online as how to create those objects in msdb database,

USE MSDB
GO

CREATE TABLE [MSdistributiondbs] (
 [name] [sysname] NOT NULL 
 ,[min_distretention] [int] NOT NULL
 ,[max_distretention] [int] NOT NULL
 ,[history_retention] [int] NOT NULL
) 
GO

CREATE TABLE [dbo].[MSdistpublishers] (
 [name] [sysname] NOT NULL 
 ,[distribution_db] [sysname] NOT NULL 
 ,[working_directory] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
 ,[security_mode] [int] NOT NULL 
 ,[login] [sysname] NOT NULL 
 ,[password] [nvarchar] (524) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
 ,[active] [bit] NOT NULL 
 ,[trusted] [bit] NOT NULL 
 ,[thirdparty_flag] [bit] NOT NULL 
) 
GO

Above objects created and finally I was succeeding to go ahead for my next steps!

SQL Server is unable to connect to server 'ServerName' - Error while setup replication by serverku

One day when I was setting up replication in my local machine, when I clicked to create a new publication from replication tab, I faced one error and the details,

Error :
TITLE: New Publication Wizard
------------------------------
SQL Server is unable to connect to server 'PARESH\MSSQLSERVER2012'.
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server.
Specify the actual server name, 'ADMIN\MSSQLSERVER2012'. (Replication.Utilities)

This is because of earlier my machine name was “ADMIN” and SQL server installed at that time. After some time I changed my machine name to “PARESH”. Currently SQL Server instance showing “PARESH\MSSQLSERVER2012” from SSMS and showing “"ADMIN\MSSQLSERVER2012” while running @@SERVERNAME from query analyzer. So this is the reason why I am facing above mentioned error. This will raise same error while trying to configure replication and the default SQL instance connected with “(local)” or “.” as a name.

Solution :
I have one option which we have to connect SQL Server with the actual server name and it will be done with change SQL server name to machine name\instance name first. You can change the SQL Server instance name with following script,

EXEC sp_dropserver 'ADMIN\MSSQLSERVER2012' -- <Old Name\Instance>
GO

EXEC sp_addserver 'PARESH\MSSQLSERVER2012','local' -- <New Name\Instance>
GO

When you run a first statement which in above mentioned script, you may face the following error too,

Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'ADMIN\MSSQLSERVER2012'.

And we have solved too,

EXEC sp_dropremotelogin 'ADMIN\MSSQLSERVER2012'; -- <Old Name\Instance>
GO

After running all steps we can go ahead for the next step of replication. To check renamed server name with @@SERVERNAME. Among you also faced this issue also. Can we have the other options for the solution here?