CODEWITHSUNDEEP
sqlsql-serversql-server-2016sql-server-administration
vyarovoy
vyarovoy

Reputation: 62

Prevent user from set DB to a single user mode

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.

Upvotes: 0

Views: 1118

Answers (1)

Karthik Venkatraman
Karthik Venkatraman

Reputation: 1657

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.

Upvotes: 0

Related Questions