2

I need to do an in-place upgrade for one of our critical servers: SQL 2012 to SQL 2016. This server has 200+ logins.

Is it advisable to DISABLE these logins for the upgrade? Or, SQL Server will take care of it.

I don't want anyone to login and screw-up something during the upgrade.

2 Answers 2

1

SQL Server certainly doesn't require you to disable the logins in order to upgrade, but it is going to terminate any sessions that are connected because it will stop the SQL Server service. If you are going to install a service pack, and then a CU, for example, this will result in stopping, starting, stopping, and starting the SQL Server service. So if you are concerned about users logging in and being disappointed because their query didn't finish, then yes, you might want to disable their logins first.

You can generate a T-SQL script to do this by running a query similar to the following:

SELECT 'ALTER LOGIN [' + name + '] DISABLE' 
FROM sys.server_principals WHERE TYPE = 'S' 
    AND  [name] NOT IN ('sa', '##MS_PolicyTsqlExecutionLogin##', 
       '##MS_PolicyEventProcessingLogin##')

You may need to add some more logins to the NOT IN list--just be sure that you don't end up disabling the login(s) you need to administer the system!

A similar script to enable the logins can be generated by replacing DISABLE in the script above with ENABLE.

If there is a poorly designed application using a database on the server, it's possible that a user could start something that doesn't get properly finished before their session is terminated by the service shutdown. This could happen if something that should be done in one transaction is instead being done in multiple transactions. If the first transaction succeeds, but one or more of the related transactions fail, then you might have something half-baked. Typically this is not something to be concerned about, but just including this because it's certainly possible.

4
  • Great. Thank you so much.
    – RaviLobo
    Sep 24, 2019 at 17:58
  • In your TSQL, why you are restricting only SQL LOGINS, why not [windows logins] as well?
    – RaviLobo
    Sep 24, 2019 at 18:11
  • It's an example, not a solution. " a query similar to the following" Sep 24, 2019 at 18:15
  • Gotcha! Thank you. Here's what I noticed: for SQL logins one needs to DISABLE/ENABLE the logins. whereas, for Windows Logins, one needs to REVOKE/GRANT CONNECT.
    – RaviLobo
    Sep 24, 2019 at 18:30
1

You do not need to disable any login before upgrading to SQL Server 2016. When upgrade happens SQL Server will not allow people to connect. As a best practice you must inform all stakeholders not to connect and to also make sure that application is down while upgrade is going on. If you want to try you can use logon trigger to block all logins

1
  • Great! Thank you so much. We have already informed the stakeholders; however, the server has been accessed by so many users, there's always a possibility of someone not reading the memo on time! Thank you so much sir.
    – RaviLobo
    Sep 24, 2019 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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