SQL Server – Fixing Incorrect Syntax Near ‘role’ in Generated Script

sql-server-2008-r2ssmst-sql

I've recently had to recreate a user several times.
To facilitate the creation of the user, I would right-click the user and choose script login as, create to, New Query Editor Window.

However, after doing this I receive a syntax error: 

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ROLE'.

What is going on here?

Best Answer

You need to make sure your scripting options are set to target the right version. You can do this in Tools / Options / Object Explorer / Scripting / Script for server version. In your case, you should pick 2008 (it is the umbrella for both 2008 and 2008 R2), though 2005 will probably work as well.

enter image description here

The challenge is that if you move to the SQL Server 2016 version of SSMS, some of this backward compatibility is broken (and in fact it may also be broken in the version you're using now, I haven't tested lately, but if so, it's less likely that it will be fixed):

The main reason you can't script this specific login's role for 2008 is that CREATE SERVER ROLE - in fact the ability to create a user-defined server role in the first place - was introduced in SQL Server 2012. So you will have to script the login without the role if you want to deploy to some version < 2012.

UPDATE: Microsoft just added a comment to the Connect item, indicating that it will not be fixed, and why:

Hello Aaron,


We've decided to not change the current behavior of the scripting.


1. For the "Script As" scenario from OE this is intended as the syntax to create/alter/drop server roles didn't exist prior to 2012, which is why the exception is throw (there's nothing that can be generated that would be valid)


2. For the Properties -> Script scenario that is also intended. The script button is intended to generate the script that would be ran if the "OK" button is pressed, and so it doesn't take the current SSMS scripting options into account.


I will be updating the error message displayed for #1 though to more clearly indicate what the problem is.

Related Question