sql server role assignments

SQL Server Roles: A Practical Guide

What are sql server roles.

SQL Server roles lets you group user logins together and manage server-level permissions. They play a central part in SQL Server security. SQL Server has two types of roles:

• Fixed server roles , which are built into SQL Server, and do not allow you to modify permissions or user-defined roles. We cover the main fixed server roles below.
• User-defined roles , which you can customize for your organization’s security requirements.

When managing a SQL Server database, it is important to maintain the least privileges rule, which ensures that users gain access only to the data they need. The goal is to enable users to access data needed for normal operations but nothing beyond that. You can restrict and control user access by setting up server roles.

In this article, you will learn:

Server-Level Roles

Database-level roles, application roles.

• Creating a Server Role Using SQL Server Management Studio
• SQL Server Roles with Satori

SQL Server Role Types

SQL Server provides three types of roles you can use to restrict access to data in your database: server-level roles, database-level roles, and application-level roles.

Server-level roles help manage permissions for the entire SQL Server instance. SQL Server provides several built in server roles, but you should add your own specific roles if possible. Fixed server roles cannot be changed.

You can also assign server-level principals (Windows groups and accounts, and SQL server logins) to these roles. Fixed server roles allow members to add other users to the same role, but this is not so for user-defined server roles.

SQL Server provides the following fixed server roles, starting with least privileged roles:

• public —default role for server principals who do not have specific securable object permissions. Only assign public permissions to objects that can be made available to all users. You cannot revoke public permission from any server role.
• dbcreator —can alter, create, drop, or restore databases.
• diskadmin —can manage disk files.
• bulkadmin —can execute BULK INSERT
• setupadmin —can add/remove linked servers and run Transact-SQL
• processadmin —can end running processes in the SQL server instance.
• securityadmin —can administer logins, can reset SQL server login passwords, and grant, deny or revoke server-level permissions or database-level permissions
• serveradmin —can alter server configuration and shut it down
• sysadmin —can perform all server activities.

SQL server defines roles that enable management of database-wide permissions. You can use the ALTER ROLE  statement to add and remove users to database roles.

Like server-level roles, there are fixed database-level roles built into SQL Server, and you can create additional roles, customizing them using the GRANT, DENY, and REVOKE statements.

Fixed roles exist independently for each database within your SQL Server instance. The db-owner server role is allowed to manage membership of fixed database roles.

Microsoft SQL Server provides the following fixed database roles:

• db_owner —allowed to perform all maintenance and configuration activities on the database, as well as dropping the database
• db_securityadmin —can modify custom role memberships and manage permissions. Monitor this role closely as it has the ability to escalate privileges.
• db_accessadmin —can add/remove database access for Windows groups and logins, as well as SQL Server logins
• db_backupoperator —can perform database backups
• db_ddladmin —can run data definition language (DDL) commands
• db_datawriter —can add, change, or delete any user table data.
• db_datareader —limited to reading data from user tables
• db_denydatawriter —are not allowed to add, modify or delete user table data
• db_denydatareader —cannot read any of the data in a user table

In addition to database-level roles, SQL Server also enables defining permissions at the row level.

Read our guide to SQL Server row level security (coming soon)

Application roles facilitate applications to run with dedicated user-like permissions. This allows all users, connected through a pre-specified application, to access specific data in a database. To distinguish them from regular database roles, they are inactive by default and have no members.

You can activate application roles by using the password-protected sp_setapprole command. Since these roles constitute a database-level principal, they require guest – level permissions to access other databases. If guest is disabled in a database, the database will be inaccessible to application roles from other databases.

Since they are not associated with a server-level principal, application roles are denied access by default to server-level metadata in SQL server (it is possible to remove this restriction if necessary).

This is the process an application should follow to grant access to the database for an application user:

• User logs into the application, and the application connects to SQL Server, identifying itself as the user
• The application executes the sp_setapprole  stored procedure using the application-specific password
• If role name and password are valid, the application role is enabled
• From this point onwards, the database grants application role permissions to the user. Permissions remain valid until the connection is closed.

Learn more:

• Blog: Role Explosion in Data Access
• Blog: Data Security Projects Keep Data Teams Away From Their Core Responsibilities
• Satori For SQL Server
• Set a demo meeting

Creating a Server Role Using Management Studio

The below walkthrough shows how to create a server role in SQL Server using Management Studio.

Step 1: In the Object Explorer, choose a SQL Server instance, find the Security folder and expand it. Right-click Server Roles > New Server Role.

Step 2: In the New Server Role screen, go to the General page. Next, find the -server_role_name  dialog box and type a name for the role.

Step 3: Under Owner , select a server principal to attach to the new role.

Step 4: Under Securables , you will find a list of server-level securables. Select one or more; you can grant or deny permissions to each securable for your server role.

Step 5: In the Permissions: Explicit box, choose the check box to “grant, grant with grant, or deny permission” to this server role for the selected securables. In some cases it will not be possible to set permission for all selected securables, and you will see a partial selection.

Step 6: In the Members page, click Add to add logins (groups or individuals) to your new server role.

Step 7: In the Memberships page, you can also select the appropriate checkbox to add a user-defined server role as a member of another server role.

Click OK , and you have successfully defined a new server role for your SQL Server instance.

SQL Server Access Control with Satori

Satori allows SQL Server admins to automate and streamline users access to data. With Satori’s Users Directory you can organize users into groups based on project or scope and with Satori’s Datasets you can organize SQL Server entities such as tables into logical datasets. Then you can provide groups and users entitlement to specific datasets as well as manage manual or automated data access approval workflows.

Last updated on

May 11, 2021

The information provided in this article and elsewhere on this website is meant purely for educational discussion and contains only general information about legal, commercial and other matters. It is not legal advice and should not be treated as such. Information on this website may not constitute the most up-to-date legal or other information. The information in this article is provided “as is” without any representations or warranties, express or implied. We make no representations or warranties in relation to the information in this article and all liability with respect to actions taken or not taken based on the contents of this article are hereby expressly disclaimed. You must not rely on the information in this article as an alternative to legal advice from your attorney or other professional legal services provider. If you have any specific questions about any legal matter you should consult your attorney or other professional legal services provider. This article may contain links to other third-party websites. Such links are only for the convenience of the reader, user or browser; we do not recommend or endorse the contents of any third-party sites.

• Access Control
• Audit & Monitoring
• Data Classification
• Data Masking
• Self-Service Access

COMPARISONS

• Satori vs Immuta
• Satori vs Privacera
• Immuta vs Privacera
• Cookies policy
• Privacy notice
• Terms of service
• Schedule A Demo!
• Solution Brief
• Product Documentation
• Partnership Opportunities

AWARDS & CERTIFICATIONS

• AWS Advanced Technology Partner
• AWS ISV Global Accelerate
• ISO/IEC 27001
• G2 High Performer Winter 2024
• G2 Leader Winter 2024
• Microsoft for Startups Pegasus Partner
• Snowflake Horizon Partner
• Snowflake Premier Technology Partner
• SOC 2 TYPE II

©2023 Satori Cyber Ltd. All rights reserved.

Home » SQL Server CREATE ROLE

SQL Server CREATE ROLE

Summary : in this tutorial, you’ll learn how to use the SQL Server CREATE ROLE statement to create a new role in the current database.

Introduction to the CREATE ROLE statement

A role is a database-level securable, which is a group of permissions. To create a new role, you use the CREATE ROLE statement:

In this syntax:

• First, specify the name of the role after the CREATE ROLE keywords.
• Second, specify the ower_name in the AUTHORIZATION clause. The owner_name is a database user or role that owns the new role. If you omit the AUTHORIZATION clause, the user who executes the CREATE ROLE statement will own the new role.

Note that the owner of the role and any member of an owning role can add or remove members of the role.

Typically, you create a new role, grant the permissions to it using the GRANT statement, and add members to the role using the ALTER ROLE statement.

SQL Server CREATE ROLE statement

The following example shows how to use the CREATE ROLE statement to create roles in the BikeStores sample database .

1) Creating a new role example

First, create the new login called james in the master database:

Next, create a new user for the login james :

Then, create a new role called sales :

After that, grant the SELECT , INSERT , DELETE , and UPDATE privileges on the sales schema to the sales role:

Finally, add the user james to the sales role:

2) Creating a new role owned by a fixed database role example

The following example uses the CREATE ROLE statement to create a new role owned by the db_securityadmin fixed database role:

3) Examining the roles

The roles and their members are visible in the sys.database_principals and sys.database_role_members views.

The following shows the information on the sales and sox_auditors roles:

• Use the SQL Server CREATE ROLE statement to create a new role in a database.

01 April 2021

30,849 views

SQL Server Security – Fixed server and database roles

Security roles can simplify permissions in SQL Server. In this article, Greg Larsen explains fixed server and database roles.

Managing Security for SQL Server is extremely important. As a DBA or security administrator, you need to provide access for logins and database users to resources within SQL Server. SQL Server has both server and database resources where access might need to be granted. Access to these resources can be granted to either individual logins or database users or can be granted to roles, for which logins or database users can be members. Granting access via a role is known as role-based security.

There are two types of roles: fixed or user-defined. In this article, I will discuss the different fixed server and database roles provided with SQL Server and how these roles can be used to support role-based security to simplify providing access to the different SQL Server resources. In a future article, I will discuss user-defined server and database roles.

What is role-based security?

Role-based security is the concept of providing logins and/or database users access to a SQL Server resource by being a member of a role. A role is an object in SQL Server that contains members, much like a Windows group contain members. When a login or database user is a member of a role, they inherit the role’s permissions.

When role-based security is used, the actual access permissions to SQL Server resources are granted to a role and not a specific login or user. Role-based security reduces the amount of administration work needed to grant and manage security when multiple logins or users require the same access to SQL Server resources. Once a role has been set up, and the appropriate permissions are granted to it, it is just a simple matter of adding logins or users to the role to provide them with the same access as the role. Without using roles, an administrator would need to grant the same permissions to each login or database user, thus causing additional administration work. There is also the possibility of making an error which would result in some logins and users getting the wrong set of permissions.

There are two types of fixed roles in SQL Server: Server and Database. The fixed server roles provide specific security access to server resources. In contrast, the fixed database roles provide access to database resources.

Fixed server roles

Fixed Server roles have server-wide scope. They come with a set of canned permissions tied to them. The permissions for server roles can’t be changed or extended.

There are nine different fixed server roles provided with SQL Server, which are shown in Table 1, along with a description. The information in the table is directly from the Microsoft SQL Server documentation .

Table 1: Fixed Server Roles

Each fixed server role provides a unique fixed set of permissions that can provide different kinds of access to server resources. The set of permissions associated with fixed server roles (with the exception of the public server role) cannot be modified.

The public role is a little different from all other fixed server roles in that you can grant permissions to this role. When permissions are granted to the public role, all logins with access to SQL Server will inherit the permissions of the public role. The public role is a great way to provide some default permissions to every login.

When a login, Windows account or Windows group becomes a member of one of these server roles, they inherit the rights associated with the role. If someone needs the same rights as a server role, it is much easier to make them a member of the role instead of granting them access to each of the permissions associated with a role. Additionally, when you want to grant the same server rights to multiple logins, placing each login in the same server roles makes it easy to accomplish this, ensuring they get exactly the same permissions. User-defined roles can also be added as members of a server role. I’ll leave the discussion about user-defined roles for another article.

There are a number of different stored procedures, views and functions that can be used to work with fixed server roles. If you are unsure of the permissions associated with a server role, you can use the system stored procedures sp_srvrolepermission to displays the permission assigned to a single fixed server role or all the fixed server roles. The code in Listing 1 shows two different examples of how to use this stored procedure.

Listing 1: Using sp_srvrolepermission

For a complete list of all stored procedures, commands, views and functions that work with fixed server roles, you can check out the list by using this link .

Adding a login to a fixed server role can be done using SQL Server Management Studio (SSMS) or TSQL. To use SSMS, follow these steps:

• Connect to an instance
• Expand the Security item
• Expand the Server Roles item
• Right-click on the fixed server role and then click on the properties option
• Click on the Add button on Server Roles Properties page
• Select the login or user-defined server role that you want to add to fixed server role
• Click on a series of Ok buttons to complete the addition of new member to the role

Before clicking to add the member, the dialog should look like Figure 1.

Figure 1: Add a new login to a fixed server role.

Alternatively, you can use the TSQL code to add a login to a fixed server role. The code in Listing 2 adds the Red-Gate login to the sysadmin fixed server role.

Listing 2: Adding a login to the sysadmin fixed server role.

Removing logins from a fixed server role can be done as easily as you added them by using SSMS or TSQL. The code in Listing 3 shows how to remove the Red-Gate login from the sysadmin fixed server role.

Listing 3: Removing login from sysadmin fixed server role.

Fixed server roles are a great way to provide DBAs, Security Admins, and operators access to the server resources they need to perform their job duties. By using server roles, you can simplify the granting of permissions to server resources. In addition to fixed server roles, there are also fixed database roles.

Database Roles

To help manage security at the database level, SQL Server has databases roles. Just like server roles, there are two different types of database roles: fixed and user-defined. Fixed database roles are just like fixed server roles, meaning they have a specific set of permissions associated with each one that cannot be altered. Fixed database roles only provide permissions to database resources in a specific database.

If a database user is a member of a fixed database role, they inherit the permissions that have been pre-defined for the fixed database role. Each database contains the same set of fixed database roles. Table 2 contains the names and definitions for each fixed database role definition, as found in the Microsoft documentation .

Table 2: Fixed Database Roles

A few additional special fixed database roles only apply to the msdb database or SQL Database on Azure. For more information on these special roles, see Microsoft documentation here .

There is also the public database role. Just like the public server role, rights can be granted to the public database role. When rights have been granted to the public database role in a database, those rights are inherited by each database user that has been defined as a user of the database. The public role is a great way to provide the same permissions to database resources for every database user in a database.

Not all organization will use each of these roles to provide access. Most shops use the db_datareader , and db_datawriter roles. If you make a database user a member of these roles, they will be able to read and/or update any user table in the database. Not only that, but they will also be able to update and/or read any data from any new user table that might be added in the future. This could be a good thing or a bad thing. It is a good thing if you want users to automatically gain read and/or update rights to all new user tables, regardless of the table. If you ever think you might want to add one or more tables to your database that only a few database users should have access to, then avoid using these two roles to provide blanket read and/or write access to database tables. Because these two roles automatically provide access to any new user table defined in the future, some shops prohibit the use of these roles to ensure database users only have access to the tables they need to perform their job function.

Just like fixed server roles, there are a number of system stored procedures, commands, views, and functions that can be used to display and manage fixed database roles. The code in Listing 4 shows how to use one of those system stored procedures sp_dbfixedrolepermission , to display all the permissions associated with each fixed database role in the AdventureWorks2019 database, as well as how to use this stored procedure to display just the permissions associated with the single fixed database role db_datareader .

Listing 4: Displaying permissions associated with fixed database roles

For a complete list of all stored procedures, commands, views and functions used to display and manage fixed database roles you can review the documentation found here .

In order for a database user to inherit the permissions of a fixed database role, they need to be a member of a fixed database role. To determine if a databases user is a member of a database role, you can review the role membership using SSMS by following the steps:

• Expand Databases
• Expand the database for which you want to review fixed database roles
• Expand the Roles item
• Expand the Database Roles Item
• Double click on the role in which you want to see members
• Review the properties window display to see the members of the role

Figure 2 shows the members of the db_datareader role:

Figure 2: Review fixed database role permissions

An application might need to programmatically determine if a database user is a member of roles. The IS_MEMBER function allows you to write TSQL code to do that. Using this function would allow you to build an application that displays different menu options for different database users, depending on the database roles that the current user is a member. To programmatically determine if the currently logged-on database user is a member of db_datawriter role, in the AdventureWorks2019 database, you could use the code in Listing 5.

Listing 5: Determining if current database user is a member of a role

The IS_MEMBER function returns a 1 if the current user is a member of the db_datawriter role or 0 if the user is not a member of this role.

Predefined Server or Database Roles

Several predefined server and database roles are provided in SQL Server. These predefined roles provide members with a fixed set of permissions based on the role. Using these predefined roles makes it easy to grant logins or database users access to a predefined set of permissions by just making them a member of a server or database role. One thing to keep in mind when using fixed server and database roles is that the permissions cannot be changed or expanded. Using predefined server and databases roles are a great way to provide a set of canned access to server and/or database resources with minimal administrative effort.

SQL Prompt is an add-in for SQL Server Management Studio (SSMS) and Visual Studio that strips away the repetition of coding. As well as offering advanced IntelliSense-style code completion, full formatting options, object renaming, and other productivity features, SQL Prompt also offers fast and comprehensive code analysis as you type.

Try it free

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Greg Larsen

Greg started working in the computer industry in 1982. In 1985, he got his first DBA job, and since then he has held six different DBA jobs and managed a number of different database management systems. Greg has moved on from being a full-time DBA and is now an adjunct professor at St. Martins University and does part-time consulting work. He has published numerous articles in SQL Server Magazine, and many online web sites dedicated to SQL Server. He is a former SQL Server MVP and holds a number of Microsoft Certification. Greg can be reached at [email protected].

Follow Greg Larsen via

View all articles by Greg Larsen

Load comments

Related articles

Do not be surprised

Snake draft sorting in SQL Server, part 4

• Database Administration
• T-SQL Programming

List or find roles assigned to a principal / user in SQL Server (MSSQL) Database

You use SQL Server. You to know which roles were granted to which users (database principals).

To find all the role assignments to users in SQL Server database, you can use the following query.

You can also limit the list of roles to only the roles, assigned to a particular user or principal by adding a filtering condition to the WHERE clause.

You can find this solution also as GitHub gist list-principal-roles.sql .

DataSunrise is sponsoring RSA Conference2024 in San Francisco, please visit us in DataSunrise's booth #6178

This website stores cookies to collect information about how you interact with our website. To find out more visit our Privacy Policy .

• Knowledge Center

SQL Server Roles

SQL Server roles are a critical component of database security and access control . To keep your database secure and well-managed, it’s crucial to understand how to use them effectively. As a database professional, you need to have a solid grasp of SQL Server roles and their proper utilizing.

In this article, we’ll cover the basics of SQL Server roles. We’ll discuss the different types of roles available. We’ll also explore best practices for implementing these roles. Finally, we’ll look at how the roles help create a strong security framework for your database.

What are SQL Server Roles?

Server roles are a mechanism for grouping user logins and managing their permissions at the server level.

You can give users the access rights they need by assigning them to specific roles. This follows the principle of least privilege. It means that users should only have access to the data and functions necessary for their job. They must not have any extra permissions.

In other words, users should only be able to access what they need to do their work. They must not have permissions to view or change anything else. This helps reduce the chances of unauthorized access or data breaches.

Types of Roles

SQL Server provides three main types of roles: server-level roles, database-level roles, and application roles. Each type serves a distinct purpose and offers different levels of control over database access and permissions.

Server-Level Roles in SQL Server

Server-level roles in SQL Server control permissions for the entire SQL Server instance, rather than just individual databases. These roles can be either fixed or user-defined.

SQL Server comes with built-in server roles like “sysadmin” and “security admin.” These roles, called fixed server roles, have a set of predefined permissions that can’t change.

Unlike fixed server roles, defined server roles provide flexibility to customize permissions according to your company’s specific security requirements. You can create custom roles and assign specific permissions based on your requirements.

To maintain a secure SQL Server environment, it’s important to assign users to the right server-level roles. This way, they’ll have the permissions they need to do their jobs, but nothing more. Be thoughtful about which roles you assign to each user to ensure they can work effectively without compromising security.

Database-Level Roles in SQL Server

Database-level roles in SQL Server work differently than server-level roles. They let you manage access to specific databases within your instance, rather than the entire server. This means you can control who has access to each individual database. Similar to server-level roles, database-level roles can be either fixed or user-defined.

SQL Server has built-in database roles, like “db_owner” and “db_datawriter,” which have a standard set of permissions. However, you can also create your own user-defined sql database roles. These custom roles allow you to set permissions that fit your specific security requirements for each database.

To minimize the risk of unauthorized access, it’s important to assign users to the right database-level roles. This ensures that they have the permissions needed to work with the database, but nothing more. By carefully choosing which roles to assign to the users, you can give them the access they need while keeping the database secure.

Application Roles in SQL Server

SQL Server has a special type of role called application roles. These roles allow applications to run with specific permissions, similar to a user. When an application uses an application role, it can access the database with the permissions assigned to that role. These roles allow all users connected through a specific application to access designated data within a database.

Application roles are inactive by default and require activation using the password-protected “sp_setapprole” command.

When you activate an application role, it gives the user the permissions of that role as long as they connect. This adds an extra level of security and control over who can access the database through the application. Once the connection ends, the user no longer has those permissions.

Best Practices for Implementing

To ensure a secure and efficient database environment, it’s crucial to follow best practices when implementing SQL Server roles.

One fundamental principle is to adhere to the concept of least privilege. Always assign users to roles that grant them the minimum permissions necessary to perform their tasks.

Regularly check and review the roles and permissions given to users to make sure they match their current duties.

Be careful when assigning users to roles with high privileges, like the “sysadmin” role. These roles provide substantial control over the SQL Server instance. You should only assign the roles to trusted individuals who genuinely require such permissions.

Another important consideration when implementing SQL Server roles is to maintain a clear separation of duties. This means that no single user should have complete control over the database environment.

Distributing responsibilities among different roles and user accounts helps minimize the risk of unauthorized actions. It also maintains a system of checks and balances.

Establishing a robust auditing and monitoring system is critical for tracking user activities and detecting any suspicious or unauthorized actions.

SQL Server offers different auditing features like SQL Server Audit and Extended Events. These tools enable you to record and analyze user activities, including role assignments and permission changes.

By regularly reviewing audit logs and monitoring user behavior, you can proactively identify and address potential security risks .

In addition to these best practices, it’s crucial to provide thorough training and education to database users and administrators

Highlighting the significance of database security is crucial. Explaining this role is essential. Underscoring the consequences of misusing permissions can significantly foster a culture of security awareness and responsible database management.

SQL Server roles are a powerful tool for managing database security and access control.

Understanding them is essential. This includes server-level, database-level, and application roles. It helps you efficiently control access to your databases. This ensures that users have the right permissions to carry out their tasks.

Ensure to consistently follow best practices. Regularly review and audit role assignments. Adhere to the principle of least privilege.

Uphold separation of duties, implement auditing and monitoring. Offer training and education to both database users and administrators.

These measures are essential for maintaining a secure and efficient database environment. These steps are crucial for ensuring the security and integrity of your database environment.

Having a solid understanding of SQL Server roles is essential. Committing to security best practices enables you to create a resilient and secure database environment. This ensures the protection of your valuable data and upholds the integrity of your database systems.

Contact our team for a demo session and explore DataSunrise’s wide variety of role assignments.

Pseudonymization

Need our support team help, our experts will be glad to answer your questions..

Netwrix Password Policy Enforcer Demo: Ensure Secure Passwords for Active Directory

5 June, 12pm MSK

DATA SECURITY

• Data Access Governance
• Data Governance
• Data Loss Prevention

IDENTITY SECURITY

• Privileged Access Management
• Active Directory Security
• Identity Governance and Administration
• Password Security
• Identity Threat Detection & Response
• Compliance Solutions
• Become a Partner
• Partner Portal
• Technology Partners
• Partner Locator
• Support Portal
• Knowledge Center
• Submit Ticket
• Support Program
• Supported Versions
• Customer Portal
• Customer Training
• Renew License
• Professional Services
• Customer Webinars
• Customer Case Stories

MISCELLANEOUS

• Security Center
• Attack Catalog
• How-to Guides
• eBooks & Guides
• SysAdmin Magazine
• Cyber Chief Magazine
• Customer Case Studies
• Management Team
• Analyst Coverage

1-949-407-5125

How to Check User Roles in SQL Server

Listing sql server roles for a user.

• Start  Microsoft SQL Server Management Studio (MSSMS) .
• On the  File  menu, click  Connect Object Explorer .
• In the  Connect to Server  dialog box, specify the following settings:
• In the  Server type  list box, select  Database Engine .
• In the  Server name  text box, type the name of the SQL cluster server.
• In the  Authentication  list box, choose your  SQL Server Authenticationmethod  and specify the credentials to use. If you do not want to re-type the password whenever you connect to the server, tick  Remember password .
• Click  Connect .
• Click  Execute  (or hit the  F5  key).
• Upon connection, click  New Query  and paste the following script into the query field:

select r.name as Role, m.name as Principal from     master.sys.server_role_members rm     inner join     master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = 'R'     inner join     master.sys.server_principals m on m.principal_id = rm.member_principal_id where m.name = 'MILKYWAY\TomSimpson'

• Review the list of server-level roles and principals (member names) in the query execution results:

Querying database roles in SQL Server for a user

• In the  Server type  list box, select Database Engine .
• In the  Authentication  list box, choose your  SQL Server Authentication method  and specify the credentials to use. If you do not want to re-type the password whenever you connect to the server, tick  Remember password .
• Upon connection, select the  Database  you need to query for user roles.
• Click  New Query  and paste the following script into the query field:

SELECT r.name role_principal_name, m.name AS member_principal_name FROM sys.database_role_members rm  JOIN sys.database_principals r      ON rm.role_principal_id = r.principal_id JOIN sys.database_principals m      ON rm.member_principal_id = m.principal_id where m.name = 'MILKYWAY\TomSimpson'

• Open Netwrix Auditor and navigate to Reports -> Predefined -> SQL Server - State-in-Time -> Account Permissions in SQL Server.
• Specify the following filters:
• In the  User account  filter, type the full user name (such as  MILKYWAY/TomSimpson ).
• In the  Object type  filter, choose  Server Instance, Database .
• Click  View  to create a clear report on the effective permissions for the user.

Implementing Security Measures with T-SQL User-Defined Roles

What are User-Defined Roles?

User-defined roles are custom roles that can be created to align with the specific needs of an application or a system. In contrast to fixed server roles or fixed database roles, which come predefined with SQL Server, user-defined roles allow for a granular approach to permissions and access control. They enable a more tailored security model that can reflect the unique structure and requirements of your business.

Creating User-Defined Roles

To start with user-defined roles, you'll first need to create them using the CREATE ROLE statement:

This creates a new role named 'SalesTeam' in the current database. Once you've established a role, you can grant permissions to this role based on what the members need access to.

Granting Permissions to Roles

After creating a role, assign the necessary permissions using the GRANT statement:

This grants the 'SalesTeam' role permission to perform SELECT operations on the 'Orders' table within the 'dbo' schema.

Adding Users to Roles

To make use of the role, you must add users to it. This can be done using the ALTER ROLE statement:

'JaneDoe' is now a member of 'SalesTeam' and has all permissions granted to that role.

Best Practices for Implementing Security with User-Defined Roles

• Principle of Least Privilege: Only grant permissions that are necessary for users to perform their tasks.
• Audit and Review: Regularly review roles and permissions to ensure they still align with current needs and policies.
• Use Schema-Based Permissions: Group objects into schemas and set permissions at the schema level when possible.
• Separation of Duties: Create separate roles for different tasks to avoid giving too much power to one role or user.

Monitoring Role Memberships

Maintaining an accurate record of who has access to what in your database is essential. You can query the system catalog views to monitor role memberships:

This query will list out all database roles and their members, providing you with a clear view of your database's security landscape.

In Conclusion

Implementing user-defined roles in T-SQL allows for precise control over who can do what within your databases. By carefully planning and executing a security strategy that includes custom roles, you're taking significant steps toward safeguarding your data against unauthorized access and potential breaches. For projects that require expertise in T-SQL, please consider expanding your team's capabilities. You might hire database T-SQL developers who can bring additional knowledge and experience to fortify your security measures further.

If you're interested in enhancing this article or becoming a contributing author, we'd love to hear from you.

Please contact Sasha at [email protected] to discuss the opportunity further or to inquire about adding a direct link to your resource. We welcome your collaboration and contributions!

Scale Your Engineering Team with Expert Remote Vue.js and Database Developers Skilled in DynamoDB

Streamline Your Software Development with Expert Remote .Net Developers Skilled in Databases and Serilog

Maximize Your Engineering Team's Potential with Expert Remote Developers in Databases, Node.js, and Android SDK

• NIST 800-53
• Common Controls Hub

Database privileged role assignments should be restricted to IAO-authorized DBMS accounts.

SQL Server Blog

What securityadmin Role Members Can and Cannot Do

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days . We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

Based on the name, you probably can guess that members of the securityadmin role can make dangerous changes to the permissions of other server principals. What many folks don’t realize is that this role is simultaneously less dangerous and more dangerous than you might think.

Allow me to explain, or better yet show you what that means.

As you can guess by the name, the securityadmin role is designed for server principals (logins and groups) who will administer security. Members of this role can not only create or drop nearly any logins, but they can also grant or deny nearly any permission to nearly any principal.

Noticed how I used “nearly any” a few times there. Here are the exceptions.

This is an instance-level role, so the exception of “nearly any permission” means members of this role can grant instance level permissions, but they can’t grant database level permissions without themselves belonging to a role within a database.

And the big exception of “nearly any principal” applies to members of the sysadmin role . A member of the securityadmin role cannot drop or alter any permissions for any member of the sysadmin role. Moreover, members of the securityadmin role are not able to add any members to the sysadmin role.

So, what makes the securityadmin role more dangerous than one might expect?

Well, having members of this role is still considered a big vulnerability for your SQL Server instances because they can create logins and assign them permissions greater than their own. Although securityadmin members cannot assign CONTROL SERVER permissions to themselves, they can assign them to another login. Because of this, members of this role can be leveraged for escalating privileges those with bad intentions.

Here’s how this would work in an attack. Assume you have a login who is a member of the securityadmin role, and their login has been compromised. The attacker could connect to the SQL SERVER instance with this login and create a new login.

Then, with one more command, they could assign the CONTROL SERVER permissions to that login.

Now this “EvilLogin” can be used to impersonate any member of the sysadmin role , and do anything in your SQL Server instance from creating backups to opening a command shell for downloading malware.

For this reason, we recommend restricting membership of the securityadmin role to only necessary members, or no one if possible. Quite frankly, there are very few situations where you might need a member of this role, as most of the time permissions are typically assigned by a member of the sysadmin role. In fact, when reviewing security for instances we often discover any members of this role are also members of the sysadmin role, so the permissions are redundant.

Check your instances today for any enabled members of the securityadmin role with the following T-SQL:

Subscribe for Updates

Leave a comment cancel reply.

This site uses Akismet to reduce spam. Learn how your comment data is processed .

• Search the community and support articles
• Devices and deployment
• Windows Server
• Search Community member

Ask a new question

Unable to connect to LocalDB or update SQL Server

I am encountering several issues with SQL Server and need assistance resolving them. Since I have a lot of content here I will first briefly summarize what I've done, and then list the details. Attempted SQL Server Update:

Used patches from Security Update for SQL Server 2022 RTM GDR (KB5021522) and SQL Server® 2022 for Microsoft® Windows Latest Cumulative Update

System File Check:

Ran sfc /scannow to verify system file integrity (no issues found).

Disk Check:

Executed chkdsk /f /r to check for and repair disk errors.

SQL Server Component Repairs:

Repaired Microsoft SQL Server 2022 LocalDB.

Repaired Microsoft SQL Server 2019 LocalDB.

Repaired Microsoft OLE DB Driver for SQL Server.

Repaired Microsoft System CLR Types for SQL Server 2019.

Repaired Microsoft ODBC Driver 17 for SQL Server.

Repaired Microsoft VSS Writer for SQL Server 2022.

Windows Update Troubleshooting:

Ran Windows Update troubleshooter to detect and resolve update issues.

Attempted to reset Windows Update components using the following commands:

DLL Re-Registration:

Re-registered several DLL files using regsvr32.exe commands (e.g., ole32.dll , shell32.dll , wuapi.dll , etc.).

Specific Error Handling:

Encountered specific Windows Update error: Security Update for SQL Server 2022 RTM GDR (KB5035432) with install error - 0x80070643.

Here are the details and logs:

Problem 1: Unable to Connect to LocalDB Photo 1

I am unable to connect to my LocalDB instance in SQL Server. The error message I receive is:

Problem 2: Errors Updating SQL Server

When attempting to update SQL Server, I encounter the following error messages:

Here is a summary of the failed instances:

Exit code: -2061893565

SQLEXPRESS01

MSSQLSERVER

Exit code: -2061893606

Here is the overall summary:

  Final result:                   The patch installer has failed to update the following instance: SQLEXPRESS SQLEXPRESS01 MSSQLSERVER. To determine the reason for failure, review the log files.

  Exit code (Decimal):            -2061893565

  Start time:                     2024-06-01 10:14:07

  End time:                       2024-06-01 10:16:06

  Requested action:               Patch

Instance SQLEXPRESS overall summary:

  Final result:                   The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.

  Start time:                     2024-06-01 10:14:32

  End time:                       2024-06-01 10:15:17

Instance SQLEXPRESS01 overall summary:

  Start time:                     2024-06-01 10:15:19

  End time:                       2024-06-01 10:15:32

Instance MSSQLSERVER overall summary:

  Exit code (Decimal):            -2061893606

  Start time:                     2024-06-01 10:15:33

  End time:                       2024-06-01 10:16:04

Machine Properties:

  Machine name:                   FRACTALG

  Machine processor count:        12

  OS version:                     Microsoft Windows 11 Home (10.0.22621)

  OS service pack:               

  OS region:                      United States

  OS language:                    English (United States)

  OS architecture:                x64

  Process architecture:           64 Bit

  OS clustered:                   No

Product features discovered:

  Product               Instance              Instance ID                     Feature                                   Language              Edition               Version          Clustered   Configured

  SQL Server 2022       SQLEXPRESS            MSSQL16.SQLEXPRESS              Database Engine Services                  1033                  Express Edition       16.0.1000.6      No          Yes       

  SQL Server 2022       SQLEXPRESS01          MSSQL16.SQLEXPRESS01            Database Engine Services                  1033                  Express Edition       16.0.1000.6      No          Yes       

  SQL Server 2022       MSSQLSERVER           MSSQL16.MSSQLSERVER             Database Engine Services                  1033                  Express Edition       16.0.1115.1      No          Yes       

  SQL Server 2022       MSSQLSERVER           MSSQL16.MSSQLSERVER             SQL Server Replication                    1033                  Express Edition       16.0.1115.1      No          Yes       

  SQL Server 2022       MSSQLSERVER           MSSQL16.MSSQLSERVER             Full-Text and Semantic Extractions for Search 1033                  Express Edition       16.0.1000.6      No          Yes       

  SQL Server 2022       MSSQLSERVER           MSSQL16.MSSQLSERVER             Machine Learning Services and Language Extensions 1033                  Express Edition       16.0.1115.1      No          Yes       

  SQL Server 2022                                                           LocalDB                                   1033                  Express Edition       16.0.1115.1      No          Yes       

Package properties:

  Description:                    Microsoft SQL Server 2022

  ProductName:                    SQL Server 2022

  Type:                           RTM

  Version:                        16

  SPLevel:                        0

  KBArticle:                      KB5036432

  KBArticleHyperlink:             https://support.microsoft.com/?kbid=5036432

  PatchType:                      QFE

  AssociatedHotfixBuild:          0

  Platform:                       x64

  PatchLevel:                     16.0.4125.3

  ProductVersion:                 16.0.1000.6

  GDRReservedRange:               16.0.1100.0:16.0.3999.0

  Installation location:          D:\7aa0aa4a3c96d0821a0b4e\x64\setup\

Updated product edition:

  Instance              Edition             

  SQLEXPRESS            Express             

  SQLEXPRESS01          Express             

  MSSQLSERVER           Express             

User Input Settings:

  ACTION:                         Patch

  ALLINSTANCES:                   false

  CLUSTERPASSIVE:                 false

  CONFIGURATIONFILE:             

  ENU:                            true

  HELP:                           false

  IACCEPTSQLSERVERLICENSETERMS:   true

  IACKNOWLEDGEENTCALLIMITS:       false

  INDICATEPROGRESS:               false

  INSTANCEID:                     <empty>

  INSTANCENAME:                   <empty>

  QUIET:                          false

  QUIETSIMPLE:                    false

  SUPPRESSPAIDEDITIONNOTICE:      false

  SUPPRESSPRIVACYSTATEMENTNOTICE: false

  UIMODE:                         Normal

Rules with failures or warnings:

Rules report file:                C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\20240601_101407\SystemConfigurationCheck_Report.htm

System Information:

Machine Name: FRACTALG

OS Version: Microsoft Windows 11 Home (10.0.22621)

OS Architecture: x64

SQL Server Instances and Features:

SQL Server 2022 Express Edition (multiple instances)

Steps Taken:

Attempted to update SQL Server using the patch from these URLs:

Security Update for SQL Server 2022 RTM GDR (KB5021522)

SQL Server® 2022 for Microsoft® Windows Latest Cumulative Update

Ran sfc /scannow to check for system file integrity, which found no issues. Results: C:\Windows\System32>sfc /scannow

Beginning system scan.   This process will take some time.

Beginning verification phase of system scan.

Verification 100% complete.

Windows Resource Protection did not find any integrity violations.

C:\Windows\System32>

Ran chkdsk /f /r , which completed without finding bad sectors. Here are the results of that operation: Checking file system on C:

The type of the file system is NTFS.

A disk check has been scheduled.

Windows will now check the disk.                         

Stage 1: Examining basic file system structure ...

Cleaning up instance tags for file 0x31460.

Cleaning up instance tags for file 0x39302.

  1548800 file records processed.                                                         

File verification completed.

  Phase duration (File record verification): 8.99 seconds.

  29258 large file records processed.                                    

  Phase duration (Orphan file record recovery): 9.68 milliseconds.

  0 bad file records processed.                                      

  Phase duration (Bad file record checking): 0.60 milliseconds.

Stage 2: Examining file name linkage ...

  89499 reparse records processed.                                       

  2129810 index entries processed.                                                        

Index verification completed.

  Phase duration (Index verification): 28.77 seconds.

  0 unindexed files scanned.                                         

  Phase duration (Orphan reconnection): 3.51 seconds.

  0 unindexed files recovered to lost and found.                     

  Phase duration (Orphan recovery to lost and found): 831.50 milliseconds.

  Phase duration (Reparse point and Object ID verification): 126.40 milliseconds.

Stage 3: Examining security descriptors ...

Cleaning up 4355 unused index entries from index $SII of file 0x9.

Cleaning up 4355 unused index entries from index $SDH of file 0x9.

Cleaning up 4355 unused security descriptors.

Security descriptor verification completed.

  Phase duration (Security descriptor verification): 53.09 milliseconds.

  290506 data files processed.                                            

  Phase duration (Data attribute verification): 0.63 milliseconds.

CHKDSK is verifying Usn Journal...

Usn Journal verification completed.

Stage 4: Looking for bad clusters in user file data ...

  1548784 files processed.                                                                

File data verification completed.

  Phase duration (User file recovery): 8.46 minutes.

Stage 5: Looking for bad, free clusters ...

  37466886 free clusters processed.                                                        

Free space verification is complete.

  Phase duration (Free space recovery): 15.45 seconds.

Windows has made corrections to the file system.

No further action is required.

  487625727 KB total disk space.

  335420612 KB in 1106092 files.

    713056 KB in 290507 indexes.

         0 KB in bad sectors.

   1624511 KB in use by the system.

     65536 KB occupied by the log file.

  149867548 KB available on disk.

      4096 bytes in each allocation unit.

  121906431 total allocation units on disk.

  37466887 allocation units available on disk.

Total duration: 9.43 minutes (565968 ms).

Internal Info:

00 a2 17 00 8a 3b 15 00 39 db 23 00 00 00 00 00   .....;..9.#.....

fe 07 00 00 9d 55 01 00 00 00 00 00 00 00 00 00   .....U..........

Results XML View:  

- <Event xmlns=" http://schemas.microsoft.com/win/2004/08/events/event ">

- <System>

  <Provider Name=" Microsoft-Windows-Wininit " Guid=" {206f6dea-d3c5-4d10-bc72-989f03c8b84b} " EventSourceName=" Wininit " />

  <EventID Qualifiers=" 16384 "> 1001 </EventID>

  <Version> 0 </Version>

  <Level> 4 </Level>

  <Task> 0 </Task>

  <Opcode> 0 </Opcode>

  <Keywords> 0x80000000000000 </Keywords>

  <TimeCreated SystemTime=" 2024-06-01T16:06:01.5125068Z " />

  <EventRecordID> 123152 </EventRecordID>

  <Correlation />

  <Execution ProcessID=" 1088 " ThreadID=" 0 " />

  <Channel> Application </Channel>

  <Computer> FractalG </Computer>

  <Security />

  </System>

- <EventData>

  <Data> Checking file system on C: The type of the file system is NTFS. A disk check has been scheduled. Windows will now check the disk. Stage 1: Examining basic file system structure ... Cleaning up instance tags for file 0x31460. Cleaning up instance tags for file 0x39302. 1548800 file records processed. File verification completed. Phase duration (File record verification): 8.99 seconds. 29258 large file records processed. Phase duration (Orphan file record recovery): 9.68 milliseconds. 0 bad file records processed. Phase duration (Bad file record checking): 0.60 milliseconds. Stage 2: Examining file name linkage ... 89499 reparse records processed. 2129810 index entries processed. Index verification completed. Phase duration (Index verification): 28.77 seconds. 0 unindexed files scanned. Phase duration (Orphan reconnection): 3.51 seconds. 0 unindexed files recovered to lost and found. Phase duration (Orphan recovery to lost and found): 831.50 milliseconds. 89499 reparse records processed. Phase duration (Reparse point and Object ID verification): 126.40 milliseconds. Stage 3: Examining security descriptors ... Cleaning up 4355 unused index entries from index $SII of file 0x9. Cleaning up 4355 unused index entries from index $SDH of file 0x9. Cleaning up 4355 unused security descriptors. Security descriptor verification completed. Phase duration (Security descriptor verification): 53.09 milliseconds. 290506 data files processed. Phase duration (Data attribute verification): 0.63 milliseconds. CHKDSK is verifying Usn Journal... Usn Journal verification completed. Stage 4: Looking for bad clusters in user file data ... 1548784 files processed. File data verification completed. Phase duration (User file recovery): 8.46 minutes. Stage 5: Looking for bad, free clusters ... 37466886 free clusters processed. Free space verification is complete. Phase duration (Free space recovery): 15.45 seconds. Windows has made corrections to the file system. No further action is required. 487625727 KB total disk space. 335420612 KB in 1106092 files. 713056 KB in 290507 indexes. 0 KB in bad sectors. 1624511 KB in use by the system. 65536 KB occupied by the log file. 149867548 KB available on disk. 4096 bytes in each allocation unit. 121906431 total allocation units on disk. 37466887 allocation units available on disk. Total duration: 9.43 minutes (565968 ms). Internal Info: 00 a2 17 00 8a 3b 15 00 39 db 23 00 00 00 00 00 .....;..9.#..... fe 07 00 00 9d 55 01 00 00 00 00 00 00 00 00 00 .....U.......... </Data>

  </EventData>

  </Event>

Repaired SQL Server components using the provided installers. These are what I repaired using the Application Manager: Repaired Microsoft SQL Server 2022 LocalDB Repaired Microsoft SQL Server 2019 LocalDB

Repaired Microsoft OLE DB Driver for SQL Server

Repaired Microsoft System CLR Types for SQL Server 2019 Repaired Microsoft ODBC Driver 17 for SQL Server

Repaired Microsoft VSS Writer for SQL Server 2022

Encountered issues with Windows Update not completing successfully, specifically:

Tried various commands and services to reset Windows Update components and re-register DLLs, but the issue persists. Here are the results of using the update troubleshooter:

Detects issues related to Windows Update.

7. I tried recovering to a recovery point before I started working on the assignment. No luck.

Windows Update Troubleshooter Report:

I have attached screenshots of the error messages and steps taken for reference. This problem originated while working on an assignment for my C# class in Visual Studio where I was creating a publish script to connect to localdb. I wasn't able to connect on my main computer and had to complete the assignment on my laptop (a less than ideal situation for me). This is a big problem for me, since I need to be able to use localdb for future assignments in school. I have tried these things listed as well as others that I can't remember at the moment. I have tried uninstalling and reinstalling SQL Server, as well as deleting every SQL Server related program and reinstalling. I have tried using the Visual Studio installer to repair, uninstall, and reinstall Visual Studio itself as well as the SQL extension. I've skimmed through these forums as well as Microsoft Documentation and have been unable to find a solution.

I have also done a full system scan using Windows Defender, which took a few hours and found 0 issues. Photos: (these are the same photos referenced earlier in the post) Publish Script

VS Connect Interface

Resource Integrity

Windows Update Troubleshooter

Windows Update Error Message

SQL Server 2022 Update

• Subscribe to RSS feed

Report abuse

Reported content has been submitted​

Replies (1) 

• Microsoft Agent |

Hello Justin Lietz ，

thank you for posting on the Microsoft Community Forums.

Based on the description, I understand that your issue is related to SQL Server .

Since there are no engineers dedicated to SQL Server in this forum. In order to be able to deal with your questions quickly and efficiently, I recommend that you repost your questions in the Q&A forum, where there will be a dedicated engineer to provide you with a professional and effective response.

Here is a link to the Q&A forum: https://learn.microsoft.com/en-us/answers/questions/

Have a nice day.

Best regards,

Was this reply helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

Question Info

• Installing Windows updates, features, or roles
• Norsk Bokmål
• Ελληνικά
• Русский
• עברית
• العربية
• ไทย
• 한국어
• 中文(简体)
• 中文(繁體)
• 日本語

What is Mirroring in Microsoft Fabric?

By: Koen Verbeeck   |   Updated: 2024-05-29   |   Comments   |   Related: > Microsoft Fabric

We have a couple of source databases hosted in Azure SQL Database (DB). We want to analyze data from several tables in our Microsoft Fabric environment. However, we don't want to load the data using batch jobs (with either pipelines or Spark notebooks) but rather have the data (near) real-time available. KQL databases don't seem to be the right option for us. Are there any alternatives?

A new feature was released in public preview at the inaugural Fabric Community Conference 2024: mirroring . The feature was already announced in November 2023 but is now available to the public to try out. At the time of writing, this feature is still in public preview.

Mirroring provides an easy-to-set-up data replication of a source database into Microsoft Fabric . The process is made as simple as possible, resulting in a near real-time duplication of your source tables into Fabric. Inside Fabric, the data will be stored in Parquet files with the delta format, as pretty much everything in OneLake .

This means you don't have to write complicated ETL; pipelines do the replication for you. An initial snapshot will be created, and once loaded, the data will be kept in sync using the source database's change data capture (CDC) technology. The process is also intelligent enough to detect when changes are made, so there's no wasted Fabric compute capacity . Currently, the following databases are supported as a source:

• Azure SQL Database
• Azure Cosmos DB

In the future, other databases will be added to this list. Don't confuse the new mirroring feature of Microsoft Fabric with database mirroring in SQL Server (now deprecated and replaced with Always-On Availability Groups) or with SQL Server replication . Both features are more intended for high-availability and disaster recovery scenarios, while mirroring in Microsoft Fabric intends to be a low-code, no-ETL solution to get source data into an analytical environment. The Fabric mirroring feature is similar to the Azure Synapse Link features but not identical.

To illustrate the concept, we will mirror an Azure SQL DB into Fabric in this tip.

How to Set Up Mirroring for Azure SQL Database

The tip, How to Install the AdventureWorks Sample Database in Azure SQL Database , explains how to configure a new instance of Azure SQL DB with some sample data. To make mirroring possible in Azure SQL DB, we need either a vCore purchasing model or a service tier of at least 100 DTUs (see the documentation for more information). Currently, the source database must also allow public network access, and the Allow Azure Services option must be enabled (located in the Networking tab of the logical SQL Server hosting the Azure SQL DB).

The system-assigned managed identity of the logical SQL Server needs to be enabled as well. This can be found in the Identity section.

Before we start configuring the mirroring, we need to make sure the mirroring feature is enabled in the tenant settings . In the top right corner, select the gear icon and choose Admin portal .

In the Admin portal, go to Tenant settings and then to the settings for Database Mirroring .

You can enable mirroring for the entire organization or give specific security groups access to the feature (recommended). The documentation also mentions the setting Allow service principals to user Power BI APIs needs to be enabled, but it is unclear what purpose this serves.

After saving the settings, exit the Admin portal. In the bottom-left corner of the Power BI service , choose the Data Warehouse persona .

In the New section, you can select the option to create a Mirrored Azure SQL Database .

Give the new mirrored database a name:

The next step is to create the source connection. You can either create a new one or choose from existing connections.

When you create a new connection, enter the server and database name:

If someone in your organization has already created a connection to the same database, Fabric will detect this and reuse the credentials used by that connection. However, you can override this by using the dropdown to select Create new connection .

This might be useful if you want to use your own set of credentials: you might have more privileges on the database or want to use another authentication method. The different authentication methods are:

• Basic – This is SQL Server authentication with a username and password. You'll need to create a login on the master database and then create a user from that login on the database you want to mirror. The login needs to be assigned to the ##MS_ServerStateReader## server role (this is required in order to check if the managed identity exists). The minimum permissions needed for the user are CONTROL or db_owner , so the principle of least privilege doesn't really apply here.
• Organizational Account – Here, you use your Azure Entra ID to log in. You can use your own, but this is not recommended. You can create a specific Azure Entra ID user and give this user the necessary permissions.
• Service Principal – You'll need to create an app registration in Azure Entra ID and a secret for it.

The following script will add a login named [fabric-service-principal] to the master database and assign the necessary permissions. The user can either be a service principal or an actual Azure Entra ID user. It will also create a user called [fabric-user] in the source database (don't forget to switch context) and assign the CONTROL permission.

Once you've established the connection, you can advance to the next screen to configure the mirroring. Choose to either Mirror all data or Mirror only a subset of tables . When you disable Mirror all data , a list of tables will appear to choose from. If a table cannot be mirrored, an error indicator will be shown:

If a table contains columns that cannot be mirrored, a warning indicator will be shown. In this example, some columns have unsupported data types:

Unsupported columns will not be replicated to Fabric. The list of current limitations can be found here . Keep in mind that only a maximum of 500 tables can be replicated.

After selecting the items to mirror, start the replication.

Once replication starts, you can monitor its status by clicking Monitoring replication . It might take a while for the initial snapshot to load.

By default, Fabric has provisioned a semantic model and a SQL Analytics Endpoint for the mirrored database, just like in the lakehouse . With the SQL endpoint, you can query the replicated tables using T-SQL. You can switch to this endpoint by selecting it from the dropdown in the top right corner:

Another option is to select it from within the workspace:

In the SQL endpoint, we can write read-only queries:

You also have the option to create views, table-valued functions, and stored procedures and manage permissions on the tables in the endpoint.

If data is added or changed in the source table, it should be replicated with low latency to the mirrored table.

In the OneLake storage layer, you can see two folders are created: Files and Tables .

Data from the source database will first be copied to a landing zone in the Files section, where it is stored as Parquet files. Then, it will be copied to the Tables section as a delta table, where it will be optimized using V-Order and compaction (see Automatic Table Maintenance in Microsoft Fabric Warehouse part 1 and part 2 for more info).

Using the same menu as before, you can also stop the replication. When replication is stopped, the data is still available in OneLake. Restarting replication results in all tables being replicated from the start again.

If something goes wrong during replication, the following queries might be useful when troubleshooting:

If you want to completely remove mirroring from the source database, you can execute the following stored procedure:

Typically, stopping replication from the Fabric side should have the same effect.

• Replicate Data to Azure Synapse Analytics with Azure Synapse Link for SQL
• Getting Started with Azure Synapse Link for Cosmos DB
• Analyze Azure Cosmos DB data with Synapse Serverless SQL Pools
• More Microsoft Fabric tips can be found in this overview .
• Make sure to check the limitations of database mirroring in Fabric.

About the author

Comments For This Article

Related Content

Automatic Table Maintenance in Microsoft Fabric Warehouse - Checkpointing and Statistics - Part 2

How to Pause or Start Microsoft Fabric Capacity Automatically

Create a Notebook and Access Your Data in a Microsoft Fabric Lakehouse

Automate Delta Tables Maintenance in a Microsoft Fabric Warehouse

What are Warehouses in Microsoft Fabric?

What are Kusto Query Language (KQL) databases in Microsoft Fabric?

What are Capacities in Microsoft Fabric?

Free Learning Guides

Learn Power BI

What is SQL Server?

Download Links

Become a DBA

What is SSIS?

Related Categories

Apache Spark

Azure Data Factory

Azure Databricks

Azure Integration Services

Azure Synapse Analytics

Microsoft Fabric

Development

Date Functions

System Functions

JOIN Tables

SQL Server Management Studio

Database Administration

Performance

Performance Tuning

Locking and Blocking

Data Analytics \ ETL

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 CASE Statement in Where Clause to Filter Based on a Condition or Expression

SQL Server Cursor Example

SQL NOT IN Operator

DROP TABLE IF EXISTS Examples for SQL Server

SQL Convert Date to YYYYMMDD

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

Resolving could not open a connection to SQL Server errors

Format numbers in SQL Server

SQL Server PIVOT and UNPIVOT Examples

Script to retrieve SQL Server database backup history and no backups

How to install SQL Server 2022 step by step

An Introduction to SQL Triggers

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

How to monitor backup and restore progress in SQL Server

List SQL Server Login and User Permissions with fn_my_permissions

SQL Server Management Studio Dark Mode

SQL Server Loop through Table Rows without Cursor

Enhance business continuity for your on-premises SQL Server instance with Cloud SQL for SQL Server

Alex cârciu.

Solutions Architect, Google Cloud

Try Gemini 1.5 models

Google's most advanced multimodal models in Vertex AI

Database professionals managing SQL Server need to design their systems to be resilient against data loss in disaster scenarios. This often involves using a mix of full, differential and transaction log backups. The frequency of the transaction log backups can be tuned to fit your recovery point objective (RPO) definition, with some professionals recommending taking log backups as often as once per minute. 

One common model customers use for further reducing data loss is replicating these backups to another environment, so you have a standby copy of your primary SQL Server database in another service. For example, Cloud SQL uses cross-region read replicas as part of a disaster recovery (DR) procedure. You can promote a cross-region replica to fail over to another region should the primary instance's region become unavailable.

In this blog post, we'll explore how to easily set up a DR architecture using Cloud SQL for both on-premises and other public cloud providers' SQL Server instances, through seamless replication of regular backups and subsequent import into Cloud SQL for SQL Server.

Cloud SQL supports importing transaction log backups . This opens up multiple possibilities, including near real-time database migration of your database or having a Cloud SQL for SQL Server to act as DR for your existing database instances, offering a flexible, robust and reliable solution to ensure business continuity and data integrity.

Setting up a Cloud SQL for SQL Server DR architecture 

You start by restoring a full backup of your database to the designated Cloud SQL for SQL Server DR instance. Then, you upload sequentially your transaction log backups to Cloud Storage. With the help of an EventArc trigger , a Cloud Function that orchestrates the import operations is executed. The function makes a request to the Cloud SQL instance to import and restore the uploaded backup file.

The function checks the progress of the restore operation. If the restore operation succeeds, it deletes the file from the source cloud storage bucket, optionally making a copy of it on a second storage bucket. If the operation does not succeed, the function will act depending on the type of error encountered.

Google Cloud Database Migration Service (DMS) for SQL Server automates this process of restoring full and transaction log backups to Cloud SQL for SQL Server, however its primary focus is to perform streamlined SQL Server database migrations. You can use it to establish a continuous data flow between your source SQL Server instance and Cloud SQL for SQL Server, monitor it, and migrate to Cloud SQL. For a deeper understanding of SQL Server database migrations using Google Cloud Database Migration Service (DMS) for SQL Server, refer to the Google Cloud Database Migration Service for SQL Server documentation .

Setting up this solution

First, you need to provision and set up your Cloud SQL for SQL Server instance and a Cloud Storage bucket to store your SQL Server backups. Run the following commands in your Cloud Shell.

1. If you don’t already have a SQL Server Instance, create one using the following command:

export PROJECT_ID=$(gcloud config get-value project)

gcloud sql instances create <CLOUD_SQL_INSTANCE_NAME> \

--database-version=SQLSERVER_2022_STANDARD  --cpu=4 --memory=8GiB \

--zone=us-central1-a --root-password=<PASSWORD>  \

--project=${PROJECT_ID} \

--network=projects/$PROJECT_ID/global/networks/<VPC_NETWORK_NAME> \

--no-assign-ip

2. Use the gcloud describe command to get the service account information of your Cloud SQL Instance

gcloud sql instances describe <CLOUD_SQL_INSTANCE_NAME>

Note the serviceAccountEmailAddress field value. You need it later. It should be something in the form of p*******-****@******.iam.gserviceaccount.com .

3. You need a Cloud Storage bucket to upload your full and transaction log backup files:

gcloud storage buckets create gs://<BUCKET_NAME> \

--location=<BUCKET_LOCATION> \

--public-access-prevention

4. Grant objectViewer rights for the Cloud SQL service account on the bucket you just created:

gcloud projects add-iam-policy-binding ${PROJECT_ID} \

--member="serviceAccount:<service-account-email-address>" \

--role="roles/storage.objectViewer"

5. Now, you create a cloud function that triggers when a new object is uploaded to the bucket. First, create a service account for the function:

gcloud iam service-accounts create cloud-function-sql-restore-log --display-name "Service Account for Cloud Function and SQL Admin API"

6. Create a role called Cloud SQL import that has rights to perform imports on Cloud SQL instances and can also orchestrate files on the buckets.

gcloud iam roles create cloud.sql.importer \

 --project ${PROJECT_ID} \

 --title "Cloud SQL Importer Role" \

 --description "Grant permissions to import and synchronize data from a cloud storage bucket to a Cloud SQL instance" \

 --permissions "cloudsql.instances.get,cloudsql.instances.import,eventarc.events.receiveEvent,storage.buckets.get,storage.objects.create,storage.objects.delete,storage.objects.get"

7. Attach the Cloud SQL import role to the Cloud function service account.

--member="serviceAccount:cloud-function-sql-restore-log@${PROJECT_ID}.iam.gserviceaccount.com" \

--role="projects/${PROJECT_ID}/roles/cloud.sql.importer"

8. Deploy the cloud function that triggers when a new object is uploaded to the bucket. The function will restore the full and transaction log backup files and also handle the file synchronization on the bucket. Run the following instructions in the Cloud Shell or in an environment of your choice where you have installed gcloud CLI:

Clone the SQL Server restore cloud function repository :

git clone https://github.com/google/example-demos-for-msft-workloads.git

Navigate to the restore-sql-server-transaction-logs/Function folder

cd example-demos-for-msft-workloads/restore-sql-server-transaction-logs/Function

From the restore-sql-server-transaction-logs/Function folder, run the following gcloud command to deploy the cloud function:

gcloud functions deploy <CLOUD_FUNCTION_NAME> \

--region=<REGION_NAME> \

--runtime=python312 \

--source=. \

--timeout=540 \

--entry-point=fn_restore_log \

--set-env-vars USE_FIXED_FILE_NAME_FORMAT=False,PROCESSED_BUCKET_NAME=,MAX_OPERATION_FETCH_TIME_SECONDS=30 \

--no-allow-unauthenticated \

--trigger-bucket=" <BUCKET_NAME> " \

--service-account cloud-function-sql-restore-log@ ${PROJECT_ID} .iam.gserviceaccount.com

9. To invoke an authenticated function, the underlying principal must have the invoker IAM permission. Assign the Invoker role (roles/run.invoker) through Cloud Run for 2nd gen functions to the function’s service account:

gcloud functions add-invoker-policy-binding <CLOUD_FUNCTION_NAME> \

--region="<REGION_NAME>" \

--member="serviceAccount:cloud-function-sql-restore-log@${PROJECT_ID}.iam.gserviceaccount.com"

10. Upload a full backup of your SQL Server on-prem database to the bucket that you created earlier. 

The function needs certain information to make the proper request for restoring the uploaded backup file to the Cloud SQL for SQL Server instance. This information includes:

Target Cloud SQL Instance name

Database name

Backup type (full, differential or transaction log backup)

Information if the backup should be restored with the recovery option or not (leaving the database ready to perform subsequent restores in case of no recovery used)

There are two ways for the function to get this information: Either from the file name itself or from object metadata. To enable the function to use the file name functionality, set the USE_FIXED_FILE_NAME_FORMAT environment variable to "True". In this way, the function expects all the uploaded backup files to have a fixed name pattern from which it infers the needed information. If you are going with this option, the name of the file might be:

<CLOUD_SQL_INSTANCE_NAME>_<DB_NAME>_[Full/Diff]_[Recovery].BAK

The [Full/Diff] and [Recovery] groups are optional. If they are not specified, the TLOG backup type and NORECOVERY options are used.

Alternatively, set the USE_FIXED_FILE_NAME_FORMAT parameter to False and set the following metadata when you upload the backup files:

CloudSqlInstance - The name of the cloud sql for sql server instance where the restore request is made. Mandatory.

DatabaseName - The name of the database where the function executes the restore operation. Mandatory.

BackupType - This is the backup type. Can be only "FULL", "DIFF" or "TLOG". Mandatory.

Recovery - This is the recovery type. Can be only "True" or "False". Mandatory.

11. Upload the file to Cloud Storage:

gcloud storage cp <OBJECT_LOCATION> gs://<BUCKET_NAME>

Replace <OBJECT_LOCATION> with the full path to your full backup file.

12. Check if the function triggered successfully. To do that, you can inspect the logs of the function by using the following gcloud command:

gcloud functions logs read <CLOUD_FUNCTION_NAME > --region=<REGION_NAME>

13. You can also connect to your Cloud SQL for SQL Server instance using the SQL Server Management Studio. If the restore of the full backup was done successfully, you will see something similar to this:

Alternatively, you can manually import the full backup with the --no-recovery argument using the gcloud command.

In SQL Server, when the restore with no recovery option is used, the uncommitted transactions are kept, allowing roll forwards through subsequent backup restores. The downside is that users are prohibited from accessing that database. In our case, we want to apply further restores of transaction log backups, so we will use the no-recovery option.

For more information about importing full backups, see Import data from a BAK file to Cloud SQL for SQL Server documentation.

At this point, you have successfully deployed a Cloud Function that restores full, differential or transaction log backups whenever they get uploaded to Cloud Storage. However, to have your Cloud SQL for SQL Server serve as a DR instance, you need to set up an automated way of uploading the transaction logs to Cloud Storage. First, you need to create a service account that has rights to upload files to the bucket. You use service account impersonation:

1. Create a service account:

gcloud iam service-accounts create tx-log-backup-writer \

    --description="Account that writes transaction log backups to Cloud Storage" \

    --display-name="tx-log-backup-writer"

2. Grant rights on the service account to create and view objects on the bucket.

gcloud storage buckets add-iam-policy-binding gs://<BUCKET_NAME> --member=serviceAccount:tx-log-backup-writer@${PROJECT_ID}.iam.gserviceaccount.com --role=roles/storage.objectAdmin

3. Provide access for your local Google Cloud user (or service account) to impersonate the service account you just created earlier:

gcloud iam service-accounts add-iam-policy-binding tx-log-backup-writer@${PROJECT_ID}.iam.gserviceaccount.com \

--member="user:<YOUR_GOOGLE_CLOUD_USER>"  \

--role=roles/iam.serviceAccountTokenCreator

Alternatively you can create a new user specific for this purpose. For more information about service account impersonation, see Use service account impersonation .

Perform the following actions on a machine with read access to the transaction log backup files:

1. Create a folder on a machine with access to the transaction log backup files. Inside the folder, clone the contents from the scheduled-upload folder of the repository .

2. Navigate to the restore-sql-server-transaction-logs/scheduled-upload folder:

cd example-demos-for-msft-workloads/restore-sql-server-transaction-logs/scheduled-upload

3. Edit the upload-script.ps1 file and change the value of the constants as follows:

New-Variable -Name LocalPathForBackupFiles -Value "" -Option Constant

New-Variable -Name BucketName -Value "" -Option Constant

New-Variable -Name AccountName -Value "" -Option Constant

LocalPathForBackupFiles - is the folder which the script will look for new files for upload.

BucketName - is the name of the cloud storage bucket where the files will be uploaded.

AccountName - is the name of the service account that is impersonated.

4. Install the gcloud CLI if not already installed.

5. Acquire new user credentials for your Application Default Credentials by running the command

gcloud auth application-default login

Login with your Google Cloud user for which you set up impersonation.

6. Create a scheduled task so that the script is called regularly. For example, the scheduled task below script starts execution at 2:45 PM and runs every minute. Replace <username> with a local user account with privileges to read and edit the settings.json file files on your machine.

schtasks /create /sc minute /mo 1 /tn "Cloud Storage Upload script" /tr "powershell <full-path-to-the-upload-script>" /st 14:45 /ru <local_account_username> /rp 

Note: You will be prompted to provide the password for the local <local_account_username> when you create the task.

The powershell script uploads every minute only new transaction log backup files from your specified folder to Cloud Storage.

You can monitor the execution of the function and set up alerting based on execution count. For example, you can set up an alert if the function did not execute successfully in the last 10 minutes. For more information about monitoring, see Monitor your Cloud Function and Cloud functions metrics .

Note: You can also set up a Storage Transfer Service job to automate the transfer of your backup files from a local file system or from a compatible Cloud Storage location to your Google Cloud Storage bucket. For more information, see What is Storage Transfer Service?

When performing a DR switch, you want to have your database accessible. For that, you need to restore a valid backup file with the recovery option set to true. For example, you could use the last successfully restored transaction log backup. Depending on the fixed file name format setting used, rename the backup file so that it contains the string “_recovery” or set the value of the metadata key “Recovery” to “True” when uploading the file to the Cloud Storage bucket.

Once you've restored the health of your initial primary instance and determined it's suitable for a fallback, you'll need to synchronize data from your Cloud SQL DR instance. Then, during a low-workload period, switch your applications to connect to the restored primary instance.

To synchronize your DR instance with the former primary instance, consider these methods:

Restore from backups: Utilize full and differential database backups from your Cloud SQL for SQL Server instance.

Replication setup: Establish replication from your Cloud SQL for SQL Server DR instance to the former primary.

For more information about exporting or replicating data from Cloud SQL for SQL Server, see Export data from Cloud SQL for SQL Server and Replicating your data from Cloud SQL for SQL Server . In any fallback scenario, we advise you to conduct thorough testing to ensure that the fallback doesn't introduce any new issues or cause disruptions to operations.

In conclusion, implementing a DR instance of your existing SQL Server on Google Cloud is a strategic approach to enhance the resilience of your database infrastructure. It helps ensure business continuity, safeguards against data loss, and provides a scalable and secure environment for critical applications.

Data here, data there, look, there’s data everywhere! Replicating your data from Cloud SQL for SQL Server

This blog walks you through how to set up Cloud SQL for SQL Server as a transactional replication publisher, which lets you create an ongoing copy of your Cloud SQL instance to another Cloud SQL instance or an external SQL Server instance.

By Isabella Lubin • 6-minute read

• Developers & Practitioners

Related articles

Introducing Cloud SQL extended support for MySQL and PostgreSQL end-of-life versions

By Rahul Deshmukh • 4-minute read

Cloud SQL: Rapid prototyping of AI-powered apps with Vertex AI

By Gunjan Juyal • 5-minute read

Firestore integration with Eventarc reaches GA with Auth Context

By Josué Urbina • 3-minute read

AlloyDB vs. self-managed PostgreSQL: a price-performance comparison

By Sridhar Ranganathan • 7-minute read

Assign Azure Built-In Roles for Access to Resources

You can assign Azure built-in roles to the Azure app registration that you use for Commvault.

• Prerequisites

If you will use Azure CLI or Azure PowerShell for the steps on this page, use most recent version of the application.

Your Azure account must have the Role Based Access Control Administrator role

• Azure Portal

In the Azure portal, on the Access Control (IAM) tab, click Add , and then select Add role assignment .

The Add role assignment pane appears.

From the Role list, select the roles that are required for the workload:

From the Assign access to list, select User, group, or service principal .

For Members , do the following:

Click Select members .

The Select members blade appears.

In the Select box, start typing to select the application that you created in the preceding step.

Click Save .

To obtain the tenant ID (which is also the directory ID) from the public Azure cloud, go to Azure Active Directory > Properties > Directory .

To protect Azure resources with your own storage account, repeat the preceding steps to add the Storage Blob Data Contributor role.

Use the following command to assign roles:

az ad sp create-for-rbac -n Azure_app --scopes /subscriptions/${Azure_subscription_ID} --role “role” --output json --only-show-errors Where:

- Azure_app is the name of your Azure app.

- Azure_subscription_ID is the ID of your Azure subscription.

- role is the role to assign.

Required roles for Azure workloads are as follows:

• Azure PowerShell

Where role is the role to assign.

This browser is no longer supported.

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

Azure SQL Database server roles for permission management

• 7 contributors

This article describes fixed server-level roles in Azure SQL Database.

The fixed server-level roles in this article are in public preview for Azure SQL Database. These server-level roles are also part of the release for SQL Server 2022 .

In Azure SQL Database, the server is a logical concept and permissions can't be granted at the server level. To simplify permission management, Azure SQL Database provides a set of fixed server-level roles to help you manage the permissions on a logical server . Roles are security principals that group logins.

The roles concept in this article are like groups in the Windows operating system.

These special fixed server-level roles use the prefix ##MS_ and the suffix ## to distinguish from other regular user-created principals.

Like SQL Server on-premises, server permissions are organized hierarchically. The permissions that are held by these server-level roles can propagate to database permissions. For the permissions to be effectively useful at the database level, a login needs to either be a member of the server-level role ##MS_DatabaseConnector## , which grants CONNECT to all databases, or have a user account in individual databases. This also applies to the virtual master database.

For example, the server-level role ##MS_ServerStateReader## holds the permission VIEW SERVER STATE . If a login who is member of this role has a user account in the databases master and WideWorldImporters , this user has the permission VIEW DATABASE STATE in those two databases.

Any permission can be denied within user databases, in effect, overriding the server-wide grant via role membership. However, in the system database master , permissions can't be granted or denied.

Azure SQL Database currently provides seven fixed server roles. The permissions that are granted to the fixed server roles can't be changed and these roles can't have other fixed roles as members. You can add server-level logins as members to server-level roles.

Each member of a fixed server role can add other logins to that same role.

For more information on Azure SQL Database logins and users, see Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics .

Fixed server-level roles

The following table shows the fixed server-level roles and their capabilities.

Permissions of fixed server roles

Each fixed server-level role has certain permissions assigned to it. The following table shows the permissions assigned to the server-level roles. It also shows the database-level permissions, which are inherited as long as the user can connect to individual databases.

Permissions

Only the server admin account or the Microsoft Entra admin account (which can be a Microsoft Entra group) can add or remove other logins to or from server roles. This is specific to Azure SQL Database.

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

Work with server-level roles

The following table explains the system views, and functions that you can use to work with server-level roles in Azure SQL Database.

The examples in this section show how to work with server-level roles in Azure SQL Database.

A. Add a SQL login to a server-level role

The following example adds the SQL login Jiao to the server-level role ##MS_ServerStateReader## . This statement has to be run in the virtual master database.

B. List all principals (SQL authentication) which are members of a server-level role

The following statement returns all members of any fixed server-level role using the sys.server_role_members and sys.sql_logins catalog views. This statement has to be run in the virtual master database.

C. Complete example: Add a login to a server-level role, retrieve metadata for role membership and permissions, and run a test query

Part 1: preparing role membership and user account.

Run this command from the virtual master database.

Here is the result set.

Run this command from a user database.

Part 2: Testing role membership

Log in as login Jiao and connect to the user database used in the example.

D. Check server-level roles for Microsoft Entra logins

Run this command in the virtual master database to see all Microsoft Entra logins that are part of server-level roles in SQL Database. For more information on Microsoft Entra server logins, see Microsoft Entra server principals .

E. Check the virtual master database roles for specific logins

Run this command in the virtual master database to check with roles bob has, or change the value to match your principal.

Limitations of server-level roles

Role assignments might take up to 5 minutes to become effective. Also for existing sessions, changes to server role assignments don't take effect until the connection is closed and reopened. This is due to the distributed architecture between the master database and other databases on the same logical server.

• Partial workaround: to reduce the waiting period and ensure that server role assignments are current in a database, a server administrator, or a Microsoft Entra administrator can run DBCC FLUSHAUTHCACHE in the user databases on which the login has access. Current logged on users still have to reconnect after running DBCC FLUSHAUTHCACHE for the membership changes to take effect on them.

IS_SRVROLEMEMBER() isn't supported in the master database.

Related content

• Database-Level Roles
• Security Catalog Views (Transact-SQL)
• Security Functions (Transact-SQL)
• Permissions (Database Engine)
• DBCC FLUSHAUTHCACHE (Transact-SQL)

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

15.5 Prepared Statements

MySQL 8.4 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol. Using prepared statements with placeholders for parameter values has the following benefits:

Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to literal or variable values in clauses such as WHERE for queries and deletes, SET for updates, and VALUES for inserts.

Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

The following sections provide an overview of the characteristics of prepared statements:

Prepared Statements in Application Programs

Prepared statements in sql scripts, prepare, execute, and deallocate prepare statements, sql syntax permitted in prepared statements.

You can use server-side prepared statements through client programming interfaces, including the MySQL C API client library for C programs, MySQL Connector/J for Java programs, and MySQL Connector/NET for programs using .NET technologies. For example, the C API provides a set of function calls that make up its prepared statement API. See C API Prepared Statement Interface . Other language interfaces can provide support for prepared statements that use the binary protocol by linking in the C client library, one example being the mysqli extension , available in PHP 5.0 and later.

An alternative SQL interface to prepared statements is available. This interface is not as efficient as using the binary protocol through a prepared statement API, but requires no programming because it is available directly at the SQL level:

You can use it when no programming interface is available to you.

You can use it from any program that can send SQL statements to the server to be executed, such as the mysql client program.

You can use it even if the client is using an old version of the client library.

SQL syntax for prepared statements is intended to be used for situations such as these:

To test how prepared statements work in your application before coding it.

To use prepared statements when you do not have access to a programming API that supports them.

To interactively troubleshoot application issues with prepared statements.

To create a test case that reproduces a problem with prepared statements, so that you can file a bug report.

SQL syntax for prepared statements is based on three SQL statements:

PREPARE prepares a statement for execution (see Section 15.5.1, “PREPARE Statement” ).

EXECUTE executes a prepared statement (see Section 15.5.2, “EXECUTE Statement” ).

DEALLOCATE PREPARE releases a prepared statement (see Section 15.5.3, “DEALLOCATE PREPARE Statement” ).

The following examples show two equivalent ways of preparing a statement that computes the hypotenuse of a triangle given the lengths of the two sides.

The first example shows how to create a prepared statement by using a string literal to supply the text of the statement:

The second example is similar, but supplies the text of the statement as a user variable:

Here is an additional example that demonstrates how to choose the table on which to perform a query at runtime, by storing the name of the table as a user variable:

A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically.

A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends.

To guard against too many prepared statements being created simultaneously, set the max_prepared_stmt_count system variable. To prevent the use of prepared statements, set the value to 0.

The following SQL statements can be used as prepared statements:

Other statements are not supported.

For compliance with the SQL standard, which states that diagnostics statements are not preparable, MySQL does not support the following as prepared statements:

SHOW WARNINGS , SHOW COUNT(*) WARNINGS

SHOW ERRORS , SHOW COUNT(*) ERRORS

Statements containing any reference to the warning_count or error_count system variable.

Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. Exceptions are noted in Section 27.8, “Restrictions on Stored Programs” .

Metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed. For more information, see Section 10.10.3, “Caching of Prepared Statements and Stored Programs” .

Placeholders can be used for the arguments of the LIMIT clause when using prepared statements. See Section 15.2.13, “SELECT Statement” .

In prepared CALL statements used with PREPARE and EXECUTE , placeholder support for OUT and INOUT parameters is available beginning with MySQL 8.4. See Section 15.2.1, “CALL Statement” , for an example and a workaround for earlier versions. Placeholders can be used for IN parameters regardless of version.

SQL syntax for prepared statements cannot be used in nested fashion. That is, a statement passed to PREPARE cannot itself be a PREPARE , EXECUTE , or DEALLOCATE PREPARE statement.

SQL syntax for prepared statements is distinct from using prepared statement API calls. For example, you cannot use the mysql_stmt_prepare() C API function to prepare a PREPARE , EXECUTE , or DEALLOCATE PREPARE statement.

SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers. However, a cursor cannot be used for a dynamic statement that is prepared and executed with PREPARE and EXECUTE . The statement for a cursor is checked at cursor creation time, so the statement cannot be dynamic.

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

To write C programs that use the CALL SQL statement to execute stored procedures that contain prepared statements, the CLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure.

CLIENT_MULTI_RESULTS can be enabled when you call mysql_real_connect() , either explicitly by passing the CLIENT_MULTI_RESULTS flag itself, or implicitly by passing CLIENT_MULTI_STATEMENTS (which also enables CLIENT_MULTI_RESULTS ). For additional information, see Section 15.2.1, “CALL Statement” .