CODEWITHSUNDEEP
sql-serverdatabaserenamedatabase-permissions
Yves Forget
Yves Forget

Reputation: 139

How to give permission to rename a database sql-server

I have a user who needs to rename a database. I could give dbcreator privileges, but this would allow the user to rename any database, and even create new ones.

So I tried to create a stored procedure that the user would call to do the job.

CREATE PROCEDURE SPMyRenameDB
WITH EXECUTE AS 'MySuperUser'  -- MySuperUser is a SQL user with dbcreator permission
AS
ALTER DATABASE A MODIFY NAME = B
GO

I get an error : The server principal "MySuperUser" is not able to access the database "A" under the current security context.

I tried with sp_renamedb, I get : User does not have permission to perform this action.

Even a simple SELECT statement to a table in database A is not allowed : The server principal "MySuperUser" is not able to access the database "A" under the current security context.

When I connect as MySuperUser and query the database A, it works as expected. (MySuperUser is a SQL user with dbCreator and sysAdmin privileges on the server).

I suspect that the "WITH EXECUTE AS" statement has some security restrictions that do not allow to use it outside of the current database.

The Stored Procedure is in a database (other than A and B) where the user has db_owner permissions.

Any suggestions ? I do not need to stick with my "WITH EXECUTE AS" approach. Anything that would do the trick is welcome.

Thanks,

Yves

Upvotes: 1

Views: 7223

Answers (2)

BOFHRAF
BOFHRAF

Reputation: 21

User must be member of dbcreator server role. (MSDN documentation is wrong!).

Upvotes: 0

qxg
qxg

Reputation: 7036

Check ALTER DATABASE in MSDN -> Permissions

Requires ALTER permission on the database.

So just query as following

USE A
GO
GRANT ALTER TO 'someuser'
GO

Upvotes: 1

Related Questions