What is a good way to handle an error while parsing a T-SQL command that doesn't exist on an older version of SQL Server?
5
votes
1
answer
412
views
I am trying to grant the [CONNECT ANY DATABASE] permission to a specific SQL account on multiple instances. However, one server is running SQL Server 2012 and that permission was not introduced until 2014.
I've been looking for additional ways to handle this particular error gracefully so that the remainder of the script which grants additional permissions gets executed. Currently, the script throws a terminating parser? error.
Here is the problem section of code:
IF NOT EXISTS
(
SELECT *
FROM sys.server_permissions AS perm
INNER JOIN sys.server_principals AS prin
ON perm.grantee_principal_id = prin.principal_id
WHERE perm.permission_name = 'CONNECT ANY DATABASE'
AND prin.name = ''
)
BEGIN
GRANT CONNECT ANY DATABASE TO [];
END;
The error is: "*Incorrect syntax near 'CONNECT'.*"
I tried the following which didn't work.
1. Using a TRY-CATCH block
2. Adding additional logic to the IF condition
( SERVERPROPERTY('servername') '' )
I did finally find a viable solution but wanted to learn about other ways people have resolved a similar issue.
IF NOT EXISTS
(
SELECT *
FROM sys.server_permissions AS perm
INNER JOIN sys.server_principals AS prin
ON perm.grantee_principal_id = prin.principal_id
WHERE perm.permission_name = 'CONNECT ANY DATABASE'
AND prin.name = ''
)
AND ( SERVERPROPERTY('servername') '' )
BEGIN
DECLARE @String NVARCHAR(150)
SET @String = 'GRANT CONNECT ANY DATABASE TO [];';
EXEC sp_executesql @Command = @String;
END
Asked by AABCDS
(119 rep)
Jan 16, 2025, 03:36 PM
Last activity: Jan 17, 2025, 07:19 AM
Last activity: Jan 17, 2025, 07:19 AM