The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’. - Error in SQL Server by serverku

For a security reason, I have created some users to have a permit to execute scheduled jobs only. So, given some required permissions in the msdb database, Even though users are not able to execute scheduled jobs in SQL Server. Below are the agents database roles are given for msdb database.

Even have above database roles in msdb database to execute scheduled jobs, users received following error when tried to run a job.

The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’.

After finding a solution with online reference, found a script to check required permissions of users. Below is a script used to check for same.

USE msdb
GO

SELECT 
 PR.NAME, 
 DP.PERMISSION_NAME, 
 DP.STATE_DESC
FROM SYS.DATABASE_PERMISSIONS DP
 JOIN MSDB.SYS.OBJECTS O 
  ON DP.MAJOR_ID = O.OBJECT_ID
JOIN SYS.DATABASE_PRINCIPALS PR
 ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID
WHERE O.NAME = 'SP_START_JOB'
GO

Finally, I saw EXECUTE permission was denied on SQLAgentUserRole and TargetServersRole roles over sp_start_job system stored procedure in msdb database. I granted it and it works finally. I would like you to share your experience of such relevant errors.

Access to the remote server is denied because no login-mapping exists - SQL Server Error by serverku

Recently, when I was working with security and changed some level of access and permission of some logins\users, I received an error while accessing data through linked servers with some logins which was working earlier. An error is reported as below.

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.

After finding solution following, it worked. Here is a some change of linked server and below is a script to used for same. Just adding a logins to linked server which has an issue to access it.

Use master
GO

EXEC master.dbo.sp_addlinkedserver 
 @server = N'LinkedServerName', 
 @provider=N'SQLNCLI',
 @srvproduct = 'MS SQL Server', 
 @provstr=N'SERVER=ServerName\InstanceName;User ID=myUser'


EXEC master.dbo.sp_addlinkedsrvlogin 
 @rmtsrvname = N'LinkedServerName', 
 @locallogin = NULL , 
 @useself = N'False', 
 @rmtuser = N'myUser', 
 @rmtpassword = N'*****'
GO

Here is the just script and change your user name in place of ‘myUser’ and appropriate server\instance name. Please share your comments if you received such errors and workaround for same.

Can not find the types 'x', because it does not exist or you do not have permission - SQL Server by serverku

Recently, when I was working with security of SQL Server, I faced one permission issue of custom data type and received an error below,

msg 15151, Level 16, State 1, Procedure xxx, Line 9
Cannot find the types 'x', because it does not exist or you do not have permission

This error received while executing stored procedure and this custom data type used in a stored procedure. The user has executed and appropriate permission of base tables, even it raised an error. Finally, I came to a solution and given permission below,

Use <YourDBName>
GO
GRANT VIEE DEFINITION ON TYPE :: DBO.X TO <User Name>
GRANT EXECUTE ON TYPE :: DBO.X TO <User Name>
GRANT CONTROL ON TYPE :: DBO.X TO <User Name>
GO

This is just what I faced and the solution applied to resolve it. Please share your comments if you ran into a custom data type issue.

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.