0

We have a requirement for application (NextGen ERH) to work with SQL Server (2016, on-premises) DB under credentials with sysadmin role.

Our need is to prevent that tech user from setting DB to a single user mode - we noticed that sometimes such happens and would like to prevent future cases.

So, question is - how I may achieve it? Revoke alter any database from username - didn't help, user is still able to set DB to a single user mode.

DB user is local to SQL server, not Windows authentication.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
vyarovoy
  • 62
  • 7
  • 3
    You can't stop sysadmin from doing anything – DavidG Apr 26 '17 at 22:07
  • 1
    ^^^ Exactly. Create a user with far fewer rights. – Xavier J Apr 26 '17 at 22:09
  • You're going to have a hard time selling a product like that to the infrastructure team – Nick.Mc Apr 26 '17 at 22:09
  • 1
    Why would the application be sending a script like that to the database? – Jason Apr 26 '17 at 22:14
  • Yes - if your application is setting the DB to single user and you don't know why, then you need to sort that out in your application!! – Nick.Mc Apr 26 '17 at 22:20
  • @Nick.McDermaid I completely agree with your statement but just imagine that is US based HIPPA certified medicine platform which officially states it need sysadmin user to work properly. – vyarovoy Apr 27 '17 at 06:53
  • @Jason investigation is in parallel stream, now we need to prevent further cases to happen – vyarovoy Apr 27 '17 at 06:53
  • That seems backwards - I would've thought that an app that requires sysadmin to work would _not_ gain accreditation since it appears to indicate a design or security issue. For example if the app requires sysadmin, then through the app you could conceivably back up the database and save it somewhere, turn off encryption etc. If the app user is _not_ sysadmin then that attack area is removed. just my 2c - good luck in your question, but I don't think it's possible -you can't deny functions to sysadmin – Nick.Mc Apr 27 '17 at 07:39

1 Answers1

0

If you want to prevent the entire database from single user mode you can use the following code

exec sp_dboption 'your_database_name_goes_here', 'single user', 'FALSE'

For sql server version greater than 2005

ALTER DATABASE 'your_database_name_goes_here' SET MULTI_USER

If you want the database to be set to single server mode only by sysadmin you can use the below code

ALTER DATABASE 'your_database_name_goes_here'
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

If the tech user does not belongs to sysadmin role, then the above code will prevent him from setting the db to single user mode.

EDIT

To set a database to Multi-user mode using SSMS

  • In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  • Right-click the database to change, and then click Properties.
  • In the Database Properties dialog box, click the Options page.
  • From the Restrict Access option, select Multiple.
  • If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.
Karthik Venkatraman
  • 1,619
  • 4
  • 25
  • 55
  • sp_dboption is from SQL Server 2005 and has been removed since that version https://technet.microsoft.com/en-us/library/ms187310(v=sql.105).aspx . We are on SQL Server 2016, forgot to mention – vyarovoy Apr 27 '17 at 06:57
  • You can use ALTER DATABASE 'your_database_name_goes_here' SET MULTI_USER as alternative – Karthik Venkatraman Apr 27 '17 at 11:42
  • It's all clear how to go into single user mode and back. Original question was - how to prevent (for example by revoking permission) user from setting DB in a single mode. Special condition - user has sysadmin db role. – vyarovoy Apr 27 '17 at 11:59
  • 2
    You cant do that. You need to remove the user from the sysadmin role. – Karthik Venkatraman Apr 27 '17 at 14:46