Sample Header Ad - 728x90

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