May 31, 2024 at 1:16 pm
Advice on how to script out existing Azure (logical) SQL Server logins and mapped database users (with permissions/group memberships).
Context: We have an Azure (logical) SQL server with a number of logins. Each login has been assigned specific permissions and is mapped as a user for databases hosted on the Azure (logical) SQL server.
What I am aiming to do is script out each of the logins and their mapped database users, along with assigned permissions at server and database level.
Previously when managing SQL Server hosted on VM's, I was able to do this with the 'sp_Help_RevLogin' procedure, which worked perfectly...
However, this seemingly does not work for Azure (logical) SQL servers/Azure databases.
I've also looked in to the DBA-Tools cmdlet DBA-CopyLogin
https://docs.dbatools.io/Copy-DbaLogin.html
Which seems to do what I need, but I'm encountering some issues with testing due to our Azure Entra ID muti factor authentication policy.
Does any one know of a method that can achieve the same outcome that sp_Help_RevLogin has for SQL Server instances on VM's??
May 31, 2024 at 1:16 pm
Context: We have an Azure (logical) SQL server with a number of logins. Each login has been assigned specific permissions and is mapped as a user for databases hosted on the Azure (logical) SQL server.
What I am aiming to do is script out each of the logins and their mapped database users, along with assigned permissions at server and database level.
Previously when managing SQL Server hosted on VM's, I was able to do this with the 'sp_Help_RevLogin' procedure, which worked perfectly...
However, this seemingly does not work for Azure (logical) SQL servers/Azure databases.
I've also looked in to the DBA-Tools cmdlet DBA-CopyLogin
https://docs.dbatools.io/Copy-DbaLogin.html
Which seems to do what I need, but I'm encountering some issues with testing due to our Azure Entra ID muti factor authentication policy.
Does any one know of a method that can achieve the same outcome that sp_Help_RevLogin has for SQL Server instances on VM's??
June 2, 2024 at 7:29 pm
Azure SQLDB or Azure SQL Managed Instance?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 4, 2024 at 11:40 am
I'll assume Azure SQLDB since you cite "logical server".
Important to remember with Azure SQLDB there is no concept of server logins and permissions, indeed, you shouldn't be (and shouldn't need to) create server level principals.
Users are created and authenticated at the database level, the logical server is just a "connection point" management asset.
The catalog that would usually track server level permissions "sys.server_permissions" does not exist and even though the server role catalog exists it's not much use, as if you create a server principal and try to add it to a server role it fails
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply