I have the user Managed identity <myusermanagedidentity> , granted it carte blanche alter with GRANT ALTER TO <myusermanagedidentity> successfully at the application database level with the intent to give the user permissions to alter all tables of all schemas.
But before the index maintenance job runs, I have to auto scale up using ALTER DATABASE xxx MODIFY command.
So I am trying to grant ALTER DATABASE but no joy. Below are the errors received one seems to indicate a syntax error, the other an Azure SQL version restriction. I do not know the syntax to GRANT ALTER DATABASE just for one specific database. Any advice or suggestions are greatly appreciated.
GRANT ALTER ANY DATABASE TO mymanageduseridentity
Msg 40521, Level 16, State 1, Line 8
Securable class 'server' not supported in the server scope in this version of SQL Server.
GRANT ALTER DATABASE TO mymanageduseridentity
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'ALTER'.reporting
May 12, 2024 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 13, 2024 at 9:41 am
Are you working with Azure SQL databases (not managed instance)? Adding your user as DB_OWNER for each of the databases of interest should get you over the line.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
I have the user Managed identity <myusermanagedidentity> , granted it carte blanche alter with GRANT ALTER TO <myusermanagedidentity> successfully at the application database level with the intent to give the user permissions to alter all tables of all schemas.
But before the index maintenance job runs, I have to auto scale up using ALTER DATABASE xxx MODIFY command. So I am trying to grant ALTER DATABASE but no joy. Below are the errors received one seems to indicate a syntax error, the other an Azure SQL version restriction. I do not know the syntax to GRANT ALTER DATABASE just for one specific database. Any advice or suggestions are greatly appreciated.
GRANT ALTER ANY DATABASE TO mymanageduseridentity
Msg 40521, Level 16, State 1, Line 8
Securable class 'server' not supported in the server scope in this version of SQL Server.
It's important to understand the relationship between the virtual sql server and the azure sqldbs. The virtual server is just a connection point to any sqldbs it houses, server securables are limited and in this case the alter any database command is out of scope for azure sqldb
You can grant alter database to the db user by opening a new query against the sqldb and running the following
GRANT alter on DATABASE::[somedb] TO myuserbob
This link details the server securables that may be granted and to what sql server types
This details db level securables and the sql server types supported
GRANT ALTER DATABASE TO mymanageduseridentity
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'ALTER'.reporting
This occurred as you did not switch to the sqldb before executing the query
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 13, 2024 at 2:08 pm
It is just Azure SQL, not a SQL managed instance so it is not close to mssql on-prem server at all. Thanks!
May 13, 2024 at 3:02 pm
It is just Azure SQL, not a SQL managed instance so it is not close to mssql on-prem server at all. Thanks!
Then either one of the responses given above should work for you. Perry Whittle's is clearly more in line with your initial post than mine.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 13, 2024 at 3:10 pm
Yes it was clear from the description that this was azure sqldb rather MI, server securables are not available in SQLdb but supported under MI.
With sqldb they are managed at the database level, the virtual server is primarily just the connection point into the db(s)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 13, 2024 at 3:43 pm
Thank you both, Perry and Phil for the help.
My ALTER syntax was incorrect GRANT ALTER DATABASE TO mymanageduseridentity
Perry gave the correct syntax GRANT alter on DATABASE::[somedb] TO myuserbob
And the correct command worked. Many thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply