I'm using the following query to scale up/down Azure SQL server programatically:
ALTER DATABASE [DB] MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S3')
And I used this StackExchange Answer to give the DB user enough permissions to run the query:
GRANT ALTER ON DATABASE:: [DB] TO [username]
However I don't want to open up this user to have more database permissions than necessary.
Is there a way to only give ALTER access to modify the SERVICE_OBJECTIVE and not have any other heightened permissions?
2条答案
按热度按时间plicqrtu1#
You can encapsulate the
ALTER DATABASE
command in a stored procedure and sign it with a certificate that confersALTER DATABASE
permissions. This will permit minimally privileged users, with only execute permissions on the procs, to scale the database up/down even withoutALTER DATABASE
permission.Example T-SQL script below. TL;DR, see Packaging Permissions in Stored Procedures .
unftdfkk2#
Is there a way to only give ALTER access to modify the SERVICE_OBJECTIVE and not have any other heightened permissions?
As siggemannen suggested when you want to give a minimum permission to user on database create a stored procedure and give only execute permission on that stored procedure.
In your case you need to grant two permissions to user:
First create a stored procedure to modify database.
Then grant permissions to user
The store procedure where user has permission to execute other than that it will throw an error: