1

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

Yves Forget
  • 101
  • 3
  • 10

2 Answers2

1

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
qxg
  • 6,955
  • 1
  • 28
  • 36
0

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

BOFHRAF
  • 21
  • 3