4

This question already has an answer here:

I am still new to DBA and learning. I am having an issue when creating a Server Role using GUI. The option to create the server role does not seem to be visible in the Menu

enter image description here

But when I create a role using the following script it is successful

CREATE SERVER ROLE TestRole AUTHORIZATION MyUser

Also after creating a role I can't update it using the GUI options

enter image description here

How do I enable the Role Administration GUI options for SQLServer express?

edit Microsoft SQL Server Management Studio 12.0.2000.8

marked as duplicate by Aaron Bertrand sql-server Feb 6 '17 at 15:22

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

4

(This was asked recently and I'm going to close this as a duplicate.)

This is a known bug in Management Studio, and has existed since at least SQL Server 2012. Unlike other missing things in Management Studio (like SQL Server Agent), this has nothing to do with the version or edition of Management Studio you're using, but rather the edition of SQL Server that Object Explorer has connected to. Here's confirmation from one of the lead SSMS devs:

enter image description here

You will need to use T-SQL for this functionality until they fix it (which I think after 2016 SP1 they'll need to do a full audit of any instance-specific filtering or restrictions).

1

Many SQL Server configuration features are not implemented in the UI. Scripting is your only option for these (aside from possibly some third-party UI that implements them).

  • What do you mean no implemented in the UI - My SSMS has this feature, when connected to a Enterprise version : xomf.com/fmjxf - but is not visible when connected to Express version – Dawood Awan Feb 6 '17 at 12:57
  • My answer is correct in general, but it turns out this particular functionality is actually available in the UI when connecting to SQL Server 2012 and up. (I initially "tested" Gareth's answer by connecting SSMS 2016 to a 2008 R2 Enterprise instance...) – T.H. Feb 6 '17 at 14:43
  • @T.H. It's actually about the edition you're connecting to, not the version. It is definitely implemented in the UI (and should be fixed pretty quickly in the new, independent version of Management Studio). – Aaron Bertrand Feb 6 '17 at 15:23
  • Ah, the reason it doesn't show when connecting to 2008R2 is that creating server roles is a feature from 2012 onwards :) – T.H. Feb 6 '17 at 16:59
0

I believe that the Express version of SSMS does not include this functionality, for whatever reason. Download & install the full version of SSMS (it's now separate from the database engine, and fully compatible with Express) and the option should be there.

SSMS 2016 link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms The 2014 version can be grabbed on the 'previous releases' page near the bottom.

  • It doesn't exist in my full SSMS 2014 or 2016 installations. Does it now exist in yours Somiya? – T.H. Feb 6 '17 at 12:38
  • @Gareth Lyons I have the same issue when I connect to a SQL Express instance - but when I connect to Enterprise instance from the same SSMS I see the options in the Menu - so isn't this an instance related issue? – Dawood Awan Feb 6 '17 at 12:54
  • 1
    Regardless of SSMS edition, SSMS doesn't provide the add server role option when connected to an Express instance. Not sure if that is intentional since the functionality is available with T-SQL. – Dan Guzman Feb 6 '17 at 12:54
0

This is a limitation of using the SSMS UI against and Express edition instance. Regardless of SSMS version/edition, SSMS doesn't provide the add server role option when connected to an Express instance. Not sure if that is intentional since the functionality is available with T-SQL in Express.

As a new DBA, it's a good idea to learn tasks like this using T-SQL. Although SSMS uses T-SQL for most of it's functionality under the covers, the behavior is sometimes different than pure T-SQL. For example, if you create a new database with SSMS and take all the defaults, the resultant database will have Service Broker disabled by default. A simple CREATE DATABASE YourDatabase; statement will result in a database with SB enabled.

Not the answer you're looking for? Browse other questions tagged or ask your own question.