Permissions required to run 'ALTER DATABASE SET SINGLE_USER' statement on SQL Server 2008

Question

I've came across the case when the following statement throws an error saying it can't be executed because of the permission:

ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Couldn't find anywhere on the web any information about the permissions it needs.

Solution 1

ALTER DATABASE:

Requires ALTER permission on the database.

Some specific SET permissions are listed in ALTER DATABASE SET options:

• EMERGENCY: ALTER DATABASE permission for the subject database is required to change a database to the offline or emergency state. The server level ALTER ANY DATABASE permission is required to move a database from offline to online.
• DB_CHAINING: To set this option, requires CONTROL SERVER permission on the database.
• TRUSTWORTHY: To set this option, requires CONTROL SERVER permission on the database.

Solution 2

Try this from admin account:

USE [YOUR_DB]
GO
GRANT ALTER TO your_user
GO

But mind that the login must have a user in the specified DB.

Or, if you want to grant this permission on every database on the server, then you can grant the permission on the server level to the login:

USE master
GO
GRANT ALTER ANY DATABASE TO your_login
GO