question

JoannLiao-3511 avatar image
0 Votes"
JoannLiao-3511 asked JoannLiao-4647 commented

Upgrade SQL SERVER 2017 from 2014


Upgrade to SQL Server 2017 from 2014. As the result, these users under the folder [Security] and [Server Roles], showed different. Attach the screenshot.
Please advise how these users can be restored.



85869-image.png


sql-server-migration
image.png (186.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


: Left was the original one with SQL Server 2014. Right was having upgraded into SQL Server 2017. As the result, these users under following two folders

Security/Logins

  1. MS_PolicyEventProcessingLogin## become disabled

  2. MS_PolicyTsqlExecutionLogin## become disabled

  3. NoVIUser become disabled

In addition, all roles under Server Roles, there's no marked in red; What happend?








0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered JoannLiao-3511 commented

Hi @JoannLiao-3511,

The only difference is that SQL server 2017 add a default login NT SERVICE \ SQLTELEMETRY$BADTEST. This login is used for Extended Events. It is related to telemetry.

Refer to the thread NT SERVICE\SQLTELEMETRY User to get more information.


If the response is helpful, please click "Accept Answer" and upvote it, thank you.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Please clarify/advise at the result after having upgraded SQL Server to 2017 from 2014

  1. MS_PolicyEventProcessingLogin## become disabled

  2. MS_PolicyTsqlExecutionLogin## become disabled

  3. NoVIUser become disabled

  4. In addition, all roles under Server Roles, no marked with red.



0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered JoannLiao-4647 commented

Hi @JoannLiao-3511,

MS_PolicyEventProcessingLogin## become disabled
MS_PolicyTsqlExecutionLogin## become disabled

It is disabled both in SQL 2014 and SQL 2017. Any account enclosed in "##" are certificate based accounts and you will not be able to simply create them as you would other logins/users. The above logins are disabled by default. Please refer to MS document Certificate-based SQL Server Logins.

NoVIUser become disabled

The login is disabled in SQL 2014, it will also in disabled state after upgrade SQL server 2014 to SQL 2017.

In addition, all roles under Server Roles, there's no marked in red; What happend?

You are using a newer version SSMS to connect to SQL server 2017, this is SSMS UI design.


If the response is helpful, please click "Accept Answer" and upvote it, thank you.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.



How about these two under the folder Management - Data Collection and Resource Governor?


Please advise.
Thanks a lot. ps: btw, there's no 'accept' button on this page.

87494-image.png


0 Votes 0 ·
image.png (163.1 KiB)


Cathyji-msft,

There's no 'Accept Answer' on this page.

0 Votes 0 ·