Incorrect syntax near 'role' with generated script

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?

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.



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 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.

StackExchange Database Administrators Q#117011, answer score: 9