azure sql server role assignment

• SQL Server training
• Write for us!

Fixed Server Roles in Azure SQL Database Server

This article is for discussing fixed server roles in Azure SQL Database Server.

Introduction

Azure SQL Database provides relational Microsoft SQL Server in the PAAS (platform-as-a-service) offering. The database functionality is similar to the on-premises SQL database (with limited features), but the administrators cannot control the database configurations entirely. In the on-premises environment, we do have SQL instances and underlying databases existing on that instance. You can control user permissions from the instance, database level.

For example, you have several roles in on-premises instances such as Sysadmin, Serveradmin, Setupadmin, Securityadmin.

Once we deploy an Azure SQL DB, it requires a logical Azure SQL Server. The logical server does not allow database administrators to manage permissions similar to the on-premises SQL Server. Users cannot do instance-level configurations for the logical server. You do not get access to security roles in the Azure SQL Server. Try connecting to Azure DB in SSMS, and it does not display the server roles in the security tab at the instance level.

If you connect to the master database in SSMS, the security shows the existing login accounts.

However, connecting directly to your specific Azure SQL DB displays the security controls inside a database.

You can manage the security, logins at the database level. However, for simplifying the permissions, Azure provides a few fixed server-level roles. These server-level roles help DBA to manage the user’s permissions on the logical server.

Requirements

To follow along with this article, you should deploy an Azure SQL Database. You also require the SQL Server admin user to connect to the master or azure database and execute specific queries. If you are not aware of Azure SQL, refer to the SQL Azure category .

Let’s explore the fixed server roles in Azure SQL Database.

Built-in server-level roles Azure SQL Server

Azure Server built-in server role has prefix ##MS and suffix ## so that users can be distinguished from these roles. These built-in server roles help you manage several logins, their permissions without providing server admin or AD admin privileges. It helps administrators to comply with the Principle of Least Privilege using role separation.

The following table lists these roles and their description.

The role allows users to read catalog views covered by the VIEW ANY DEFINITION, VIEW DEFINITION for Azure database in which the user exists.

• Server-level permissions: VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW ANY SECURITY DEFINITION
• Database-level permissions: VIEW DEFINITION, VIEW SECURITY DEFINITION

In this fixed server role, the user can execute all dynamic management views (DMV) and functions covered by the VIEW SERVER STATE and VIEW DATABASE STATE on the Azure database in which the role contains a user account.

• Server-level permissions: VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE
• Database-level permissions: VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

The ##MS_ServerStateManager## role contains the permissions of ##MS_ServerStateReader## and an additional permission for ALTER SERVER STATE. It allows users to execute DBCC commands – ), DBCC SQLPERF(),DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE (‘ALL’), DBCC SQLPERF();

• Server-level permissions: ALTER SERVER STATE, VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE

Note: Users (admins) cannot modify the permissions assigned to the fixed server roles.

The permissions granted to the fixed server roles cannot be changed, and these roles can’t have other fixed roles as members. The Azure server roles work similarly to the roles in the on-prem SQL instance. The user server role permissions can propagate to database permissions. However, the login must exist in the respective Azure SQL database.

Let’s say, a user [Demo1] has server level role ##MS_ServerStateReader## . The role ##MS_ServerStateReader## contains permission VIEW SERVER STATE. The user [Demo1] has a login in the Azure DB [azuresqldemo]. Therefore, the user [Demo1] will have database level permission -VIEW DATABASE STATE permission in both master and [azuresqldemo] database.

Note: You cannot work with the server-level roles in SSMS GUI. You can create, edit, or remove users from these groups using T-SQL scripts.

The following section creates a new SQL login user and provides permissions through the defined fixed server roles. It will give you a better understanding of how this built-in role works.

Create Azure SQL Database Logins and Users

To create a new SQL Database login in Azure SQL Database, connect to Azure SQL Server using administrator user credentials. The below script creates a new login [DemoLogin1] and user in the master database.

Now, add the user as a member of the ##MS_ServerStateReader## role using the following ALTER SERVER ROLE.

We can use the sys.server_role_members and sys.sql_logins catalog views to return SQL logins having memberships to any fixed server role.

As shown below, the login [DemoLogin1] is part of the built-in server role ##MS_ServerStateReader##.

The VIEW SERVER STATE allows users to execute Server-scoped dynamic management views and functions such as sys.dm_os_wait_stats.

Connect to the master database using SSMS and run the following query. You get the expected results.

We have not added this user to the Azure SQL Database [azuredemodatabase]. Therefore, if you try to access the database, it gives the following error.

To add the login as an Azure SQL Database user, run the following CREATE USER statement under the admin security context.

The user automatically gets the permissions assigned to the server level role ##MS_ServerStateReader##. Therefore, it can execute the query that requires VIEW DATABASE STATE permissions. For example, you can use database scoped database management view sys.dm_db_log_info to return the number of virtual log files in a database as below.

Queries to check role memberships

Is_srvrolemember() function to check user access.

Azure SQL Database supports the function IS_SRVROLEMEMBER() to check whether the SQL Server login is a member of the specified server role.

• Value 1: Yes, the user is a member of a specified role
• Value 0: No, the user is not a member of the specified role

Note: The master database in Azure SQL Server does not support the function IS_SRVROLEMEMBER().

sys.fn_my_permissions()

To return the fixed server role permissions at the server or database level, you can use the function – sys.fn_my_permissions().

For example, in my demo, the user [demologin] is a member of the fixed server role ##MS_ServerStateReader##. Therefore, the query returns the following output.

Limitations of server-level roles

• Azure might take up to 5 minutes for role assignment to be effective.
• If you are already connected to the Azure database, you need to reopen the connection to distribute server or database role permissions for users.
• To reduce the waiting period (up to 5 minutes), the Azure SQL administrator can run the command – DBCC FLUSHAUTHCACHE. It empties the SQL database authentication cache that contains information about logins, firewall rules for the current user database. However, you still need to reconnect Azure DB. You can refer to Microsoft docs for more details.

Note: You cannot execute DBCC FLUSHAUTHCACHE on the master database because the master database has information (physical storage) for the logins, firewall rules.

This article explored built-in server roles for the logical Azure SQL Server. The Database administrators can manage the permissions for the users using these roles. Once we add a login/user in the required role, it automatically assigns the server and database level permissions. However, you should create the database user in Azure SQL Database to access the database with assigned permissions.

• Recent Posts

• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023
• Learn the PostgreSQL COALESCE command - January 19, 2023

Related posts:

• How to prepare for the Exam DP-300: Administering Relational Databases on Microsoft Azure
• How to prepare for the Exam AZ-900: Microsoft Azure Fundamentals
• Top SQL Server Books
• How to prepare for the Exam AZ-104: Microsoft Azure Administrator
• Move or copy SQL Logins by assigning roles and permissions

SQL Server Database and Server Roles for Security and Permissions

By: Nivritti Suste   |   Updated: 2024-08-13   |   Comments   |   Related: > Security

SQL Server is one of the most used relational database management systems in many organizations. It is mainly used to store, manage, and retrieve data with ease. Apart from this, SQL Server is popular for data security, including encryption, data masking, and role-based access control.

Today, we will discuss role-based access control (RBAC) in SQL Server. Using RBAC, you can assign specific permissions to users according to their roles within the server. There are different types of roles in SQL Server, which can be confusing. Here, we will discuss the distinctions between SQL Server and Database roles, helping us to manage security more effectively.

Let's first understand the roles. There are two types of roles in SQL Server: 1) SQL Server Roles and 2) Database Roles.

What are SQL Server Roles?

SQL Server roles are predefined sets of permissions used to control access to server resources. They are created at the server level and typically assigned to logins or other server roles, which helps administrators manage permissions and security for the entire SQL Server instance. SQL Server roles are like Windows groups, allowing for easy management and assignment of permissions to multiple users.

Types of SQL Server Roles

There are three types of SQL Server roles: fixed server, user-defined server, and application.

Fixed SQL Server Roles -  Fixed server roles are predefined sets of server-level permissions that cannot be modified or deleted. These roles are created during the installation of SQL Server. This includes one of the important ' sysadmin ' roles, which has "God-level control" over the entire SQL Server instance, and other specialized roles like bulkadmin, dbcreator, diskadmin.

User-Defined SQL Server Roles - There are multiple instances when you need custom sets of permissions based on your business needs. Here, user-defined server roles come into the picture; these are not predefined roles. User-defined server roles will allow you to create custom sets of permissions based on your specific needs. These roles granted to logins or only other user-defined server roles provide more control over access to server-wide resources.

SQL Server Application Roles -  The above-mentioned roles are mostly assigned to individual users. This third type of role is like user-defined server roles called Application Roles. These roles are created for applications only and used by applications instead of any individual users. These special roles let applications borrow permissions for a short time to complete the task, keeping regular users and app users separate and safe.

Key Features of SQL Server Roles

• Scope : Server-wide
• Creation : Created at the server level
• Assignment : Assigned to logins or other roles
• Permissions : Control access to server resources (databases, logins, etc.)

Example: SQL Code to Create a SQL Server Role

• Create a SQL Server Role. Replace [role_name] with the desired name for your new server role.
• Assign the User to the Role. Replace [role_name] with the name you chose in Step 1 and [user_name] with the username you want to assign the role to.
• You need to have sufficient permissions (e.g., sysadmin server role) to create server roles and manage user memberships.
• This code snippet only creates the role and assigns the user. You'll need to grant specific permissions to the role itself to control user access within the server.

Example: Granting Permissions to the Role

You can use the GRANT statement.

This grants the "Connect to Server" permission to the newly created role. You can explore other permission options based on your needs.

How to Check Server Roles Using SSMS

• Open SSMS and connect to your SQL Server.
• In the Object Explorer , navigate to Security > Server Roles .
• Expand the Server Roles. You will see all the predefined and user-defined roles listed.

Alternatively, you can use SQL Query:

What are SQL Server Database Roles?

The Database Roles, as the name suggests, are specific to control databases and database objects. Unlike server roles, these roles are created and managed at the database level and can be assigned to database users and other roles within the same database they are created. These roles are a more controlled approach to managing permissions in a SQL Server instance as different users may have different levels of permissions.

Types of SQL Server Database Roles

There are also three types of database roles: fixed database, user-defined database, and application.

Fixed SQL Server Database Roles - Fixed database roles are like fixed server roles in that they cannot be modified or deleted. However, they are limited to the specific database in which they were created. The default fixed database role is ' db_owner' , which has full control over the entire database and other roles like db_accessadmin, db_backupoperator, and db_datareader.

User-Defined SQL Server Database Roles - User-defined database roles allow for the creation of custom sets of permissions within a specific database. These roles can be assigned to users or other user-defined database roles, allowing for more granular control over access to objects within that database.

SQL Server Application Roles - Like SQL Server roles, application roles at the database level are intended for use by applications rather than normal users. They enable applications to temporarily assume permissions and perform actions on behalf of the role, providing an added layer of security.

Key Features

• Scope : Database-specific
• Creation : Created at the database level
• Assignment : Assigned to database users or other roles
• Permissions : Control access to specific database objects (tables, views, etc.)

Example: SQL Code to Create a Database Role

• Create a Database Role
• [role_name]: The desired name for your new database role.
• [user_name]: The username who will own (own as in "be authorized by") the role. This user doesn't necessarily need to be the one assigned to the role.

This statement combines the CREATE ROLE and AUTHORIZATION clauses in a single line. The AUTHORIZATION clause specifies the user who will "own" the database role. This doesn't necessarily restrict who can be assigned to the role, but it determines who can manage the role's permissions later (e.g., adding/removing members and granting/revoking permissions to the role).

• Assigning a User to the Database Role
• [role_name]: The name of the database role you created.
• [user_name]: The username you want to assign to the database role.

This will grant the user the permissions associated with the database role.

• You need to have the db_owner role or equivalent permissions on the database to create database roles and manage user memberships.
• Remember to grant specific permissions to the database role itself to control user access within the database. You can use the GRANT statement for this purpose.

How to Check Database Roles Using SSMS

• In SSMS , navigate to the specific database you want to check.
• Right-click on " Security " and select " Roles ".
• This will show you a list of all the roles defined within that database.

Another way to check database roles with a system view:

• Open a new query window in SSMS.
• Use the below query to check all 'Database_Role.'

Roles Key Differences Brief

When to Use Which Role

• SQL Server Roles: To manage overall user access to the SQL Server instance and its resources.
• Database Roles: To grant granular permissions within specific databases based on user needs.

Best Practices for Using SQL Server and Database Roles

Follow these tips to keep things safe and organized when setting up who can access what in SQL Server:

• Limit Sharing: Only give roles what they need. Don't give extra access.
• Keep Checking: As things change, update roles so access stays right.
• Give Just Enough: Roles and users should only have what they need to do their job.
• Make Your Own Roles: Don't use predefined roles. Create ones that fit your needs.
• Roles for Jobs: Use roles for different jobs to keep things organized.
• Write it Down: Keep track of all the roles, so you don't get confused.
• Double Check: Look at the roles regularly to make sure everything is safe.

Understanding the difference between SQL Server roles and database roles is important to keep your SQL Server secure. SQL Server roles provide server-wide control, while database roles offer more controlled permissions within specific databases. By leveraging these roles appropriately, database administrators and SQL developers can enhance security, streamline permission management, and ensure users have the necessary access without compromising security.

• Check out these MSSQLTips.com Security tips .

About the author

Comments For This Article

Related Content

Understanding SQL Server fixed database roles

SQL Server Database Users to Roles Mapping Report

The Power of the SQL Server Database Owner

Nesting Database Roles in SQL Server

Implicit Permissions Due to SQL Server Database Roles

Retrieving SQL Server Fixed Database Roles for Disaster Recovery

List SQL Server Login and User Permissions with fn_my_permissions

Free Learning Guides

Learn Power BI

What is SQL Server?

Download Links

Become a DBA

What is SSIS?

Related Categories

Auditing and Compliance

SQL Injection

Surface Area Configuration Manager

Development

Date Functions

System Functions

JOIN Tables

SQL Server Management Studio

Database Administration

Performance

Performance Tuning

Locking and Blocking

Data Analytics \ ETL

Microsoft Fabric

Azure Data Factory

Integration Services

Popular Articles

Date and Time Conversions Using SQL Server

Format SQL Server Dates with FORMAT Function

SQL Server CROSS APPLY and OUTER APPLY

SQL Server Cursor Example

SQL CASE Statement in Where Clause to Filter Based on a Condition or Expression

DROP TABLE IF EXISTS Examples for SQL Server

SQL NOT IN Operator

SQL Convert Date to YYYYMMDD

Rolling up multiple rows into a single row and column for SQL Server data

Format numbers in SQL Server

Script to retrieve SQL Server database backup history and no backups

Resolving could not open a connection to SQL Server errors

How to install SQL Server 2022 step by step

SQL Server PIVOT and UNPIVOT Examples

How to monitor backup and restore progress in SQL Server

An Introduction to SQL Triggers

SQL Server Management Studio Dark Mode

Using MERGE in SQL Server to insert, update and delete at the same time

SQL Server Loop through Table Rows without Cursor

Azure DWH part 12: Permissions, roles, logins and users

Daniel Calbimonte , 2017-07-25

Introduction

By default, when you create an Azure SQL Server, a login with administrative privileges is created. How can we create  Azure SQL Data Warehouse (ASDW) logins with lower privileges?,  which roles are available in ASDW?, how can I verify the roles, users and logins created in ASDW?

In this new chapter, we will learn how to:

Create a new SQL login

Verify that the user was created using ssms and system views, create a database user linked to a login, verify if the user was created successfully using ssms system views.

• Contained users in Azure
• Special Azure Database Roles
• Create roles, users in ASDW and how to verify the creation. 

We will talk about special database roles, like the dbmanager role, and show how to assign users to logins, how to create contained users, and how to assign users to different roles.

Requirements

• SQL Server Management Studio (SSMS)
• An Azure SQL Server Subscription.
• An ASDW database with the firewall rule enabled to run queries from SSMS.

Getting started

Connect to your ASDW using SSMS:

To create a new login, you can do it using the following code in the master database:

The code creates a login named securityadmin with the password specified.

You can verify that the login securityadmin was created successfully in SSMS in the security folder:

You can also verify that the login was created by doing a select statement to the table sys.sql_logins:

As you can see the securityadmin was created and the principal id is 4. The type is S which is a SQL login . The account is not disabled (is_disabled is equal to 0) and you also have the creation data. Note that the sp_helplogins stored procedure and other stored procedures related to get user, roles and login information are only available in SQL Server on-premises and not in ASDW.

In Azure SQL, you do not have the server roles that you have in SQL Server on-premises:

The login allows you to have access to several databases with the same login. The following example shows how to link a new database user created with the login:

If the database user was created successfully you will be able to see it in the Database>Security>User folder:

You will be able to see the new database user created:

Contained Users in Azure

If you do not want to have a login, you can create a user with access to one single database. This is a contained user. To create a database user that logins directly to the database use the following code:

The codes create a database user that con logins directly to the database.

To connect a contained user, you need to specify the database when you connect in SSMS. In the connect window, select Options:

In the connection Properties tab, specify the name of the database where you want to connect with the credentials:

Contained users can be migrated from one database to another. The logins instead are harder to migrate because they are in the master database.

Special Azure database roles

In Azure SQL and ASDW, you have the same database roles than SQL Server on-premises except two roles that are exclusive Azure:

• The loginmanager
• The dbmanager

The loginmanager role is a role in the master database that allows to create logins in the master database. To add a user to the loginmanager role you need to run the following code in the master database:

The dbmanager is another role that can be assigned in the master database. This role allows to create new databases. If you create a database with this role, you will be the database owner. To add someone to this role, run the following code:

In Azure is not so easy to verify if a user is member of a database role. To have a list of roles and users, you can run the following query:

The result of the query will be the database role and the user names. You can verify in this query if a user is member of a specified role:

In this article, we learn how to create logins, contained users, add users to roles, we learn some Azure database roles and we checked some system table to verify the creation of logins, users and role membership.

• Controlling and granting database access
• Database-Level Roles
• Server-Level Roles

Log in or register to rate

You rated this post out of 5. Change rating

Join the discussion and add your comment

Related content

Azure dwh part 20: create external and internal tables as select.

• by Daniel Calbimonte
• SQLServerCentral.com

CTAS and CETAS are very important T-SQL features in ASDW. These features allow to create a table and fill with data based on a query.

2019-07-12 (first published: 2017-12-12 )

4,210 reads

Azure DWH part 19: Backup and Restore SSAS

In the part 18, we created a Cube based on the Azure Data Warehouse. In this new chapter, we will work with the cube, create backups and show some tips to restore.

2020-05-29 (first published: 2017-10-23 )

2,951 reads

Azure DWH part 18: Working with SQL Server Analysis Services

In this new article, we will learn how to create a cube extracting data from ADWH.

2020-05-22 (first published: 2017-10-09 )

2,402 reads

Azure DWH part 17: ADF:Import Data from SQL Server on-premises to ASDW

In this article we will learn to use Data Factory to import table from SQL Server to Azure SQL Data Warehouse.

Azure DWH part 15: PolyBase and Azure Data Lake

In this article, we will learn how to query a csv file stored in the Data Lake using PolyBase.

2,523 reads

Azure SQL, create users and assign permissions (Manual)

This simple manual has been created to create an user in Azure SQL and assign appropriate permissions. First connect to your SQL server. Either use and AAD admin account or the SQL Admin account.

Once connected, open a New Query window and run the following command on the Master database to create the user on the server in the Master database:

Now open again a New Query window, and select the database where you want to provision permissions to the just created user. Make sure to match the Username from the command above.

The last step is to assign the desired role to the user. Change the value of the role, and match again the Username.

If you want to view the current permissions on Azure SQL database you can run the following command.

If you want to add a user from Azure AD, you can use the following command:

Related Posts

How to deploy fonts to windows clients using intune. (manual), masterclass: azure basics, automatiseer je builds & deployments met azure devops, about the author.

Cor den Boer

I’ve been struggling trying to get a ‘user’ into SqlAzure. I followed the above and still hit the same problem – when you try to log in with the new user account in SSMS it says “The server principal is not able to access the database “master” under the current security context. Cannot open user default database. Login failed. Error 916. Nomatter how much I try to google an answer, I have been unsuccessful. (I’ve unchecked collation in SSMS). Any help would be most welcome.

In SSMS go to the tab Connection Properties, and change the Connect to database to the database that you want to manage. That should solve your problem!

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

• NIST 800-53
• Common Controls Hub

Azure SQL Database must allow only the ISSM (or individuals or roles appointed by the ISSM) to select which auditable events are to be audited.

• Announcements
• Best Practices
• Thought Leadership
• SQL Server 2022
• SQL Server 2019
• SQL Server Management
• SQL Server on Azure VMs
• SQL Server on Linux
• Azure Data Studio
• Azure SQL Database
• Azure Synapse Analytics
• Machine Learning Server
• Data analytics
• Data Security
• Data warehousing
• Hybrid data solutions

Modernize Microsoft SQL Server 2014 workloads with Azure

• By Debbi Lyons, Director, Product Marketing, Azure Relational Databases
• Content type

We take pride in delivering innovation with each new version of Microsoft SQL Server. However, there comes a time when product lifecycles must conclude. As of July 9, 2024, SQL Server 2014 has reached its end of support. Many of our customers, including Scandinavian Airlines, have begun transitioning their SQL workloads to Microsoft Azure or are updating to SQL Server 2022. Their objective is straightforward: to modernize their databases and applications while accelerating innovation through using cloud technologies. 

“With our migration to PaaS, we got what we wanted: greater scalability, reliability, security, agility in managing our IT infrastructure—and greater peace of mind—all without the cost and hassle of doing this ourselves,”  Daniel Engberg, Head of AI, Data, and Platforms at Scandinavian Airlines System  

Migrate to Microsoft Azure

Boost productivity and enable innovation.

This blog post will guide you through several best practices our customers employed when faced with the SQL Server end-of-support moment. Customers have three choices for handling their out-of-support SQL Server workloads: moving or updating to Azure, upgrading to SQL Server 2022, or getting Extended Security Updates (ESUs) for additional preparation time. 

Migrate and modernize to Azure, a smooth path, a more powerful destination 

Migrating to a cloud platform is an essential step on the journey to modernization, and there are many choices. What makes SQL Server and Microsoft Azure SQL unique is that it’s built on the same engine, no matter where you deploy, which means you can build on your existing SQL experience while gaining the layered security, intelligent threat detection, and data encryption that Azure provides. 

Modernizing to Microsoft Azure SQL Managed Instance offers cost savings, scalability, security, seamless migration, productivity, and always up-to-date features. Some of the recent product highlights include Azure SQL Managed Instance Next-gen General Purpose , now in public preview, which supports twice as many Azure VMs configurations, making migration and modernization faster and easier than ever before for a larger number of customer scenarios. Customers can experience the full capabilities of managed SQL Server in the cloud at no cost for the initial 12 months with access to a General Purpose instance capable of accommodating up to 100 databases, along with 720 vCore hours of compute per month (non-accumulative) and 64 GB of storage through Azure SQL Managed Instance Free Tier,  now in public preview. 

Modernizing your SQL Server workloads to Azure also presents a chance to utilize cutting-edge innovation like Microsoft Copilot. Microsoft Copilot in Azure has extended its capabilities to Microsoft Azure SQL Database with new skills designed to enhance the management and operation of SQL-based applications.  

Extending end-of-support time

If you are ready to move to the cloud but feel challenged to upgrade or modernize before the end of the support timeline, Extended Security Updates are available for free in Azure for SQL Server 2014 and 2012 and Windows Server 2012. Secure your workloads for up to three more years after the end of the support deadline by migrating applications and SQL Server databases to  Microsoft Azure Virtual Machines . Free Extended Security Updates are available for Azure Virtual Machines including Microsoft Azure Dedicated Host, Microsoft Azure VMWare Solution, Nutanix Cloud Clusters on Azure, and Microsoft Azure Stack (Microsoft Azure Stack Hub, Microsoft Azure Stack Edge, and Microsoft Azure Stack HCI). Combining Extended Security Updates in Azure with Azure Hybrid Benefit further reduces your costs. With these pricing advantages, AWS is up to five times more expensive  than Azure for SQL Server and Windows Server end-of-support workloads. 

In-place upgrade to SQL Server 2022 

Another way to stay protected is to upgrade your SQL Server to  SQL Server 2022 , the most Azure-enabled release yet. Get more out of your data with enhanced security, industry-leading performance and availability, and business continuity through Azure. 

SQL Server 2022 is the most Azure-enabled release of SQL Server, with continued innovation across performance, security, and availability. Gain deeper insights, predictions, and governance from your data at scale. Take advantage of enhanced performance and scalability with built-in query intelligence. 

Stay protected on-premises or in multi-cloud environments with Azure Arc 

Just as with SQL Server 2012, Extended Security Updates for SQL Server 2014 offers an enhanced cloud experience through Microsoft Azure Arc . First year coverage from Extended Security Updates started on July 10, 2024. With this more customer-centric approach, security updates will be natively available in the Microsoft Azure portal through Azure Arc. This also provides Azure benefits and flexible subscription billing for SQL Server 2014 workloads on-premises or in multi-cloud environments. 

We’re continuing to enhance the capabilities Azure Arc offers to Extended Security Updates. Just recently, physical-core licensing with unlimited virtualization was released for SQL Server 2012 and 2014 ESUs. For customers who need to maximize database performance or require security isolation and better resource management, physical core licensing provides a more cost-effective way to leverage Extended Security Updates via Azure Arc. 

Also, if you enabled ESU subscription in your production environment managed through Azure Arc, you can enable SQL Server ESU subscription in the non-production environment for free, through SQL Server Developer Edition or an Azure dev/test subscription. 

We encourage all our customers running SQL Server 2014, Windows Server 2012, and Windows Server 2012 R2 to start planning for the end of support. We have migration resources, best practices, and more, as well as a rich ecosystem of partners ready to help. To get started, please visit the following pages to learn more. 

Learn More 

Why migrate windows server and sql server to azure: roi, innovation, and free offers  .

• Get the full ROI and TCO reports from IDC:  The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workload  and  The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads  
• Previous announcement on SQL Server 2014 End-of-Support moment: Protect SQL Server 2014 workloads with Azure flexibility – Microsoft SQL Server Blog

Related Posts

Announcing the retirement of SQL Server Stretch Database  

Getting started with delivering generative AI capabilities in SQL Server and Azure SQL  

Update on the support of dbcc clonedatabase for production use  .

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Custom roles for SQL Server to Azure SQL Database migrations in Azure Data Studio

• 2 contributors

This article explains how to set up a custom role in Azure for SQL Server database migrations. The custom role only has the permissions necessary to create and run an instance of Azure Database Migration Service with Azure SQL Database as a target.

Use the AssignableScopes section of the role definition JSON string to control where the permissions appear in the Add role assignment UI in the Azure portal. To avoid cluttering the UI with extra roles, you might want to define the role at the level of the resource group, or even the level of the resource. The resource that the custom role applies to doesn't perform the actual role assignment.

You can use either the Azure portal, Azure PowerShell, Azure CLI, or Azure REST API to create the roles.

For more information, see Create or update Azure custom roles using the Azure portal and Azure custom roles .

Permissions required to migrate to Azure SQL Database

You can get a sample script to create a login and provision it with the necessary permissions, for VMware , Hyper-V , or physical servers , using Windows authentication or SQL Server authentication.

Role assignment

To assign a role to a user or an app ID:

In the Azure portal, go to the resource.

In the left menu, select Access control (IAM) , and then scroll to find the custom roles you created.

Select the roles to assign, select the user or app ID, and then save the changes.

The user or app ID now appears on the Role assignments tab.

Related content

• Database Migration Guide

Was this page helpful?

Additional resources

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

ARM Template - Set dependency on RBAC Role existing

I am able to grant my SQL Server Identity membership of the Storage Blob Contributor role on my storage account using an ARM template.

However, when creating my Audit resource (which has dependsOn references to both the Storage Account and to the SQL Server) it looks like it is trying to create this before the RBAC permission has been granted as I get

Oddly, I have this for Vulnerability Scans from the same server to another Storage Account and this succeeded. I don't know if that was simply a quirk of the way that Azure decided to order the deployment.

Anyway, is there a way to add a dependsOn reference to the RBAC permission so that I don't try to create the Audit destination to Storage until it has permissions?

To be clear, the RBAC assignment is a sub-resource of the Storage Account:

My assumption was that having it as a sub-resource would ensure that it was in place for anything that then had a dependsOn to the Storage Account

• azure-resource-manager
• azure-rm-template

• add a dependsOn if rbac deployment is in the same template –  4c74356b41 Commented Jul 23, 2020 at 12:59
• I've updated with more details. The RBAC is already there but seems as if the Audit is going ahead before the Storage Account permission is set. I assumed the Storage Account wouldn't complete until all sub-resources had deployed too? –  Martin Cairney Commented Jul 23, 2020 at 13:05

According to the error, I think when you deploy the SQL audit log resource, the assigned role action does not complete successfully.So We need to define in the template that the audit log resource depends on the role assignment actions

For example

• My template.json

For more details, please refer to the sample

• I thought for a minute you had it. I had a small variation with dependencies in my latest attempt. Reworked it along your lines but still get the issue. I have a support case raised with Microsoft now as this does seem to be buggy. I'll update this post when I get an outcome from them –  Martin Cairney Commented Jul 24, 2020 at 11:59

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged azure azure-resource-manager azure-rm-template or ask your own question .

• The Overflow Blog
• Ryan Dahl explains why Deno had to evolve with version 2.0
• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Bringing clarity to status tag usage on meta sites
• Feedback requested: How do you use tag hover descriptions for curating and do...
• What does a new user need in a homepage experience on Stack Overflow?

Hot Network Questions

• Why was the uncut gem stored in a fish?
• Hardware interrupt for PC from GPIO signal
• Home water pressure higher than city water pressure?
• Seven different digits are placed in a row. The products of the first 3, middle 3 and last 3 are all equal. What is the middle digit?
• Everyone hates this Key Account Manager, but company won’t act
• Calling get_GeodesicArea from ogr2ogr
• Is sudoku only one puzzle?
• Retroactively specifying `-only` or `-or-later` for GPLv2 in an adopted project
• I can't select a certain record with like %value%
• How to raise a vector to powers contained in a vector, change the list into a product, and do this for all the lines of a matrix, efficiently?
• Pairing and structuring elements from a JSON array, with jq
• How to add a segment to an Excel radar plot
• The Reforger NPC is stuck underground. How do I get him to move?
• In Moon, why does Sam ask GERTY to activate a third clone before the rescue team arrives?
• What prevents applications from misusing private keys?
• Ethics application: secondary analysis of anonymous data without "future use" consent
• If you get pulled for secondary inspection at immigration, missing flight, will the airline rebook you?
• SF novel where the story, or part of it, is narrated by two linked brains taking turns
• How soon to fire rude and chaotic PhD student?
• What's the highest starting ECL for a Small/Medium Humanoid in 3.5?
• What is the difference between an `.iso` OS for a network and an `.iso` OS for CD?
• Is this misleading "convenience fee" legal?
• What other goals could a space project with the primary goal of experience building with heavy lift rockets perform?
• How many people could we get off of the planet in a month?