Azure SQL server get all users and their roles
Hello, what are the commands to get a list of all users that have access to an Azure SQL Server? We are using AAD - Universal with MFA to login. I have tried several different ways using Master and from the Database, but most of us are db_owners. So it only shows dbo for login. How do we get all the users that have this role? Thanks for any help. DeniseNew! Professional Azure SQL Database Administration Packt E-book
Hi folks! Check out our recently launched free e-book for Azure SQL Database from Packt! https://azure.microsoft.com/en-us/resources/professional-azure-sql-database-administration/ This comprehensive guide from Packt provides an in-depth look at Azure SQL Database and includes detailed guidance and activity plans on how to migrate to Azure SQL Database or provision a new SQL database, backup and restore Azure SQL Databases, implement high availability and disaster recovery, monitor and optimize the performance of your cloud database and automate common management tasks using PowerShell. For database administrators, architects, big data engineers, and anyone else looking to migrate or modernize their on-premises data estate and wants flexibility, efficiency, and high performance. Currently available in EN:US only. In this e-book, you’ll get detailed, easy-to-follow guidance and activity plans on how to: Migrate to Azure SQL Database or provision a new SQL database. Backup and restore Azure SQL Databases. Implement high availability and disaster recovery. Monitor and optimize the performance of your cloud database. Automate common management tasks using PowerShell. Here's the URL link to your free copy: https://azure.microsoft.com/en-us/resources/professional-azure-sql-database-administration/How Azure can help your company expand in multiple regions (2 of 5)
In my previous article, we created a proposal based on the company’s overview, goals, and technology, then highlighting the benefits of an architecture base on Azure resources and giving an overview of its flow. In today’s article, I am going to start guiding you by implementing the proposed architecture by creating an Azure SQL database resource in our main company’s database in Montana, replicating it in Europe, and then synchronizing it with the On-Premises database. Create an Azure SQL Database Sign in to the Azure portal. Select + Create a resource, filter the result for Databases, and click on SQL Database On the creation tab, select a resource group, insert a database name, create a new server in a Central US (primary location of the company), and select a database’s configuration. Click Review + create to create the Azure SQL database resource. Replicate the database Once the creation process is completed, select your SQL server, and click your SQL database from the list of available databases. Select the Geo-Replication tab and click North Europe (closest region to the company’s target audience). In the creation form, create a new server in North Europe, select the same configuration as the primary location, and click OK. Azure will then initialize the new instance and then seed the data from the primary database to your replica. Synchronize your On-Premise database to Azure SQL database Select your primary SQL database from the list of available databases. Click the Sync to other databases tab and click New Sync Group. On the creation tab, insert a group name, select the Existing database and enable the automatic synchronization. By doing so, you will set the frequency with which the primary instance of your Azure SQL database will synchronize with the group and resolve possible conflicts. The possible options are: Hub win: When conflicts occur, data in the hub database overwrite conflicting data in the member database. Member win: When conflicts occur, data in the member database overwrite conflicting data in the hub database. Once the synchronization for the group is created, you will see it in the status column, Not Ready. Select the synchronization group, click databases, and then click the Add an On-Premises Database button. To complete the configuration of an On-Premises database, we must set up an SQL Azure Data Sync agent on the machine where the SQL server is located and then Select which Database to sync. Set up the SQL Azure Data Sync Agent Click Choose the sync Agent Gateway. If you don’t have an agent already installed on your machine, select the Create a new agent radio button. Download the data sync agent from the following link https://www.microsoft.com/en-us/download/details.aspx?id=27693. Open the installer and start the installation wizard. Read and accept the License Agreement and Privacy Information, select Accept and click Next. Enter the credential of the account with Network Access to run Windows Service (for example, contoso\gtrekter) and click Next. Enter the location where you want to install the agent and click Next to start the installation. Once the installation is complete, launch the Microsoft SQL Data Sync Agent. Before continuing, go back to Azure, fill the agent name textbox, and click Create and Generate Key button. Once the key is generated, copy it to the clipboard. In the Microsoft SQL Data Sync, click the Submit Agent Key button. In the configuration window, add the Agent Key generated by Azure and the Login and Password fields. Enter the Azure SQL Database server’s credentials where the Hub database is located. Test that everything is working properly by clicking on the Test Connection button and then OK. Click the Register button, and in the SQL Server Configuration window, select the Authentication type, the Server, and the Database to synchronize. Once again, check that everything is fine by clicking on the Test Connection button and Save. Select the database To complete the configuration of your On-Premises database, follow the following steps: Click select the database. The tab that will appear, add a Sync Member Name, select the On-premises database that you have previously registered in the Microsoft SQL Data Sync and select the Sync Directions. The available options are: Bi-directional Sync: Data changes on either the on-premises SQL Server database or the hub database will be written to the other database. To the Hub: Changes in the on-premises SQL Server database are written to the hub database but not vice versa. From the Hub: Changes in the hub are written to the on-premises SQL Server, but not vice versa. Click OK Congratulations! You have successfully created an Azure SQL database resource, geo-replicated it in a secondary location, and synchronized it with your On-Premises database!
GetArrayElement -> integer AND multiple rows
Hi guys, I have a question. I have a variable that can have multiple rows, so it looks like this: "Count": ["5", "4", "12", "0"]. First and major problem: I would like to put a 'case when count > 0 then whatever', but I would like it to verify the case on every row it has (so in this case if any of these 5 numbers is greater than 0 it would give, idk, an '1'). How do I achieve that when GetArrayElement command requires me to point one of these elements, not the bundle? Second problem: I would like to operate on it as it would be an integer, so use max, >, < operators. How do I do that? Like if above mentioned bundle is > 10 then '1' or something. Thanks in advance!
