5

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 = '<ServerPrincipal>'
)
BEGIN
    GRANT CONNECT ANY DATABASE TO [<ServerPrincipal>];
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

AND ( SERVERPROPERTY('servername') <> '<2012ServerName>' )

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 = '<ServerPrincipal>'
)
AND ( SERVERPROPERTY('servername') <> '<2012ServerName>' )
BEGIN
    DECLARE @String NVARCHAR(150)
    SET @String = 'GRANT CONNECT ANY DATABASE TO [<ServerPrincipal>];';
    EXEC sp_executesql @Command = @String;
END

1 Answer 1

7

Using dynamic SQL you can catch a syntax error in the nested batch. EG

begin try
    exec ('GRANT CONNECT ANY DATABASE TO [<ServerPrincipal>];')
end try
begin catch 
  print error_message()
end catch

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.