Incorrect syntax near 'role' with generated script

Question

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?

Answer 1

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):

• Connect #1870405 : [SSMS] - CTP 2.4 - Can't script certain downlevel options

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.

• Reaces: I think it might very well be broken, as some things script out fine (such as adding roles) and other very much related things don't (scripting out the roles when choosing to script out the user). I didn't know there was this option though! Pretty useful!
• Reaces: This might just be me being daft (or not understanding the workings fully), but I don't understand there's nothing that can be generated that would be valid. As there is clearly a correct syntax that exists and as stated in #2 the SSMS script generator is capable of picking it up.
• Aaron Bertrand: @Reaces he's saying there's no way to create that role for 2008 R2, because it wasn't possible to create server roles in 2008 R2. Show me how you would create a server role in 2008 R2 manually?
• Reaces: Ah I guess that's where my confusion comes from! I didn't get an error trying to create a server role, my error was when adding a user to an existing role.
• Aaron Bertrand: @Reaces That was a different problem, in 2012+ the syntax for adding a member to a server role is ALTER SERVER ROLE, which you get by default, and which won't work in 2008 R2 (it requires sp_addsvrolemember IIRC). If you change the server target version, some scripting options will update for the older syntax, like the scripting options from within Object Explorer, but not wizards or dialogs that have a script button. It's inconsistent at best, and I smell a different Connect item coming on...