azure sql database role assignment

This browser is no longer supported.

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

Create and Manage Role Assignments

A role assignment is a security policy that determines a user's or group's permissions. Permissions decide whether the user or group can access or modify a specific report server item, or do a task. A role assignment consists of a single user or group account name and one or more role definitions.

Role assignments are scoped to the item level or system level .

An item-level role assignment is created for a specific item or branch of the folder hierarchy on the report server. You navigate to a specific folder or item to create a role assignment for it.

System-level role assignments give selected users the capability to do tasks that affect the report server site as a whole. These tasks include:

System-level security doesn't convey access to items in the report server folder hierarchy.

Creating an Item-level Role Assignment

From here, you can create a separate role assignment for each user or group account that requires access to the report server. If the account is on a domain other than the one that contains the report server, include the domain name. After you specify an account, you choose one or more role definitions. The role definitions are additive. The combined set of all tasks from all definitions is supported in the assignment for a particular user or group.

To enable widespread access, you choose an item that is high in the folder hierarchy (for example, the root folder Home). Later, you can create role assignments to lock down specific areas of the folder hierarchy.

You must be a member of the local Administrator's group on the report server computer to create a role assignment. You can delegate that responsibility by assigning other users to the Content Manager role.

To create or manage role assignments, or for more information, see Grant User Access to a Report Server

Creating a System-level Role Assignment

System-level and item-level role assignments go together. You create a system-level role assignment for each user or group, that has an item-level role assignment.

System-level role assignments include a wide range of permissions, but they don't include permissions that are part of an item-level role assignment.

In contrast with system permissions on a computer, system roles in reporting servers don't convey overarching permissions that include all possible tasks. Instead, system-level role assignments are simply a set of tasks that are scoped to the report server site. System role assignments determine whether users can view application properties (such as the image or title of the Home page), view or manage shared schedules, or use Report Builder.

To create or manage a system-level role assignment or for more information, see Grant User Access to a Report Server and Predefined Roles .

Modifying a Role Assignment

You can modify a role assignment at any time. Your changes take effect when you save the role assignment. User sessions are not affected by role assignment changes. If a user has a report open, and you modify a role assignment to deny access, the user can continue using the report for that active session.

If you add a user account to a group that is already part of a role assignment, there will be a delay before the user account is able to access items from the change. This delay is caused by Internet Information Services (IIS) caching of authentication tokens. You can either wait for the tokens to refresh (typically 15 minutes), or you can reset IIS to update the cache immediately.

You can only modify one role assignment at a time. You can't perform a global search-and-replace operation to change role definition names, role assignment settings, or to find all the role assignments that include a specific user or group.

Deleting a Role Assignment

You can delete role assignments by selecting the checkbox by each assignment you want to delete, and then clicking Delete . You can also delete role assignments by clicking Revert to Parent Security . When you select this button, the existing role assignments for the item are deleted, and replaced with the assignments inherited from the parent item.

Grant User Access to a Report Server Role Assignments Role Definitions Predefined Roles Granting Permissions on a Native Mode Report Server

Additional resources

Fixed Server Roles in Azure SQL Database Server

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

Introduction

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

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

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

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

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

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

Requirements

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

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

Built-in server-level roles Azure SQL Server

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

The following table lists these roles and their description.

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

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

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

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

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

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

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

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

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

Create Azure SQL Database Logins and Users

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

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

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

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

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

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

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

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

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

Queries to check role memberships

Is_srvrolemember() function to check user access.

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

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

sys.fn_my_permissions()

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

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

Limitations of server-level roles

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

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

Recent Posts

Collectives™ on Stack Overflow

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

Q&A for work

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

How to view the roles and permissions granted to any database user in Azure SQL server instance?

Could you guide me on how to view the current roles/permissions granted to any database user in Azure SQL Database or in general for a MSSQL Server instance?

I have this below query:

I further need to know what are the permissions granted to these roles "loginmanager" and "dbmanager"?

Could you help me on this?

azure-sql-database

Not really a full answer but check out this article for details about those roles: msdn.microsoft.com/en-us/library/azure/ee336235.aspx – cbattlegear Jun 29, 2015 at 16:36

5 Answers 5

Per the MSDN documentation for sys.database_permissions , this query lists all permissions explicitly granted or denied to principals in the database you're connected to:

Per Managing Databases and Logins in Azure SQL Database , the loginmanager and dbmanager roles are the two server-level security roles available in Azure SQL Database. The loginmanager role has permission to create logins, and the dbmanager role has permission to create databases. You can view which users belong to these roles by using the query you have above against the master database. You can also determine the role memberships of users on each of your user databases by using the same query (minus the filter predicate) while connected to them.

To view database roles assigned to users, you can use sys.database_role_members

The following query returns the members of the database roles.

Gold. Saved me. THank you – romanzdk Nov 4, 2021 at 13:26

Building on @tmullaney 's answer, you can also left join in the sys.objects view to get insight when explicit permissions have been granted on objects. Make sure to use the LEFT join:

3 This does not work as intended when the object to which permission is granted is a SCHEMA. You must also LEFT JOIN to sys.schemas when class_desc = "SCHEMA" – Pittsburgh DBA Aug 18, 2020 at 18:47

Further building on @brentlightsey 's answer, you can add left join to sys.schemas to also see permissions on a schema level:

if you want to find about object name e.g. table name and stored procedure on which particular user has permission, use the following query:

Based on the post you've made, I would recommend spending some time reading documentation in the Help Center so that you have the best chance at getting your question answered. – Brien Foss Mar 8, 2018 at 4:21

Your Answer

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service , privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged sql-server azure azure-sql-database azure-sql or ask your own question .

Hot Network Questions

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .

Adding Users to Azure SQL Databases

By: Joe Gavin | Updated: 2021-12-08 | Comments (17) | Related: > Azure SQL Database

You know how to manage logins and users on traditional on-premises SQL Servers with SQL Server Management Studio (SSMS), but now you’ve created an Azure SQL Database and are now ready to add users.

It’s presumed you are not and will not have users and / or applications using the server level "sa" like server admin credentials chosen when the Azure SQL Database server was built. What is the best way to manage users in Azure SQL Databases?

Let’s do a quick review. With traditional on-premises SQL Server, you have a login to the SQL Server. The login can be an Active Directory account or created in the SQL Server using local SQL authentication. The login gets you access to the SQL Server only. Then you have a user mapped to the login in individual databases that give you access to a databases(s) with permissions typically granted by putting it in a specific security group(s).

Security at a server / database level with on-premises SQL Server and Azure SQL Database are very similar but you will find some definite differences.

The first difference is the concept of a contained user, which is a user not mapped to a login and authentication is done in Azure Active Directory or in the database itself. Traditional logins to the SQL Server with a user in a database mapped to it still exists, but this breaks from the concept of the required login that gets you access to the server and the user gets you access to the database. Contained users make the database more easily portable.

And the traditional database level roles like db_datareader, db_datawriter, db_ddladmin, etc. are the same, but the traditional server level roles like sysadmin, serveradmin, etc. don’t exist in Azure SQL Database. However, there are two server admin roles, dbmanager (similar to dbcreator) that can create and drop databases, and loginmanager (similar to securityadmin) that can create new logins.

Also, you will immediately notice in SSMS that when you right click on Logins or Users from the Object Explorer and choose New it will open a new query window with the command syntax instead of greeting you with a familiar GUI.

Now that we have reviewed the basics, have some background, know the differences and what to expect, here are our options:

Following are examples of our options listed above:

Hopefully this tip has given you everything you need to know to manage logins and users in SQL Azure Database but you can find further info here:

About the author

Comments For This Article

Name already in use

Azure-content / articles / sql-database / sql-database-manage-logins.md.

Users who have contributed to this file

Open with Desktop

Managing databases and logins in Azure SQL Database

In Microsoft Azure SQL Database, when you sign up for the service, the provisioning process creates an Azure SQL Database server, a database named master , and a login that is the server-level principal of your Azure SQL Database server. That login is similar to the server-level principal ( sa ), for an on-premises instance of SQL Server.

The Azure SQL Database server-level principal account always has permission to manage all server-level and database-level security. This topic describes how you can use the server-level principal and other accounts to manage logins and databases in SQL Database.

[AZURE.IMPORTANT] SQL Database V12 allows users to authenticate at the database by using contained database users. Contained database users do not require logins. This makes databases more portable but reduces the ability of the server-level principal to control access to the database. Enabling contained database users has important security impacts. For more information, see Contained Database Users - Making Your Database Portable , Contained Databases , CREATE USER (Transact-SQL) , Connecting to SQL Database By Using Azure Active Directory Authentication .

Overview of SQL Database security administration

Security administration in SQL Database is similar to security administration for an on-premises instance of SQL Server. Managing security at the database-level is almost identical, with differences only in the parameters available. Because SQL Databases can scale to one or more physical computers, Azure SQL Database uses a different strategy for server-level administration. The following table summarizes how security administration for an on-premises SQL Server is different than in Azure SQL Database.

Server-level administration and the master database

Your Azure SQL Database server is an abstraction that defines a grouping of databases. Databases associated with your Azure SQL Database server may reside on separate physical computers at the Microsoft data center. Perform server-level administration for all of them by using a single database named master .

The master database keeps track of logins, and which logins have permission to create databases or other logins. You must be connected to the master database whenever you create, alter, or drop logins or databases. The master database also has the sys.sql_logins and sys.databases views that you can use to view logins and databases.

[AZURE.NOTE] The USE command is not supported for switching between databases. Establish a connection directly to the target database.

You can manage database-level security for users and objects in Azure SQL Database the same way you do for an on-premises instance of SQL Server. There are differences only in the parameters available to the corresponding commands. For more information, see Azure SQL Database Security Guidelines and Limitations .

Managing contained database users

Create the first contained database user in a database by connecting to the database with the server-level principal. Use the CREATE USER , ALTER USER , or DROP USER statements. The following example creates a user named user1.

[AZURE.NOTE] You must use a strong password when creating a contained database user. For more information, see Strong Passwords .

Additional contained database users can be created by any user with the ALTER ANY USER permission.

SQL Database V12 supports Azure Active Directory identities as contained database users, as a preview feature. For more information, see Connecting to SQL Database By Using Azure Active Directory Authentication .

Microsoft recommends using contained database users with SQL Database. For more information, see Contained Database Users - Making Your Database Portable .

Managing logins

Manage logins with the server-level principal login by connecting to the master database. You can use the CREATE LOGIN , ALTER LOGIN , or DROP LOGIN statements. The following example creates a login named login1 :

[AZURE.NOTE] You must use a strong password when creating a login. For more information, see Strong Passwords .

Using new logins

In order to connect to Microsoft Azure SQL Database using the logins you create, you must first grant each login database-level permissions by using the CREATE USER command. For more information, see the Granting database access to a login section below.

Because some tools implement tabular data stream (TDS) differently, you may need to append the Azure SQL Database server name to the login in the connection string using the <login>@<server> notation. In these cases, separate the login and Azure SQL Database server name with the @ symbol. For example, if your login was named login1 and the fully qualified name of your Azure SQL Database server is servername.database.windows.net , the username parameter of your connection string should be: [email protected] . This restriction places limitations on the text you can choose for the login name. For more information, see CREATE LOGIN (Transact-SQL) .

Granting server-level permissions to a login

In order for logins other than the server-level principal to manage server-level security, Azure SQL Database offers two security roles: loginmanager for creating logins and dbmanager for creating databases. Only users in the master database can be added to these database roles.

[AZURE.NOTE] To create logins or databases, you must be connected to the master database (which is a logical representation of master ).

The loginmanager role

Like the securityadmin fixed server role for an on-premises instance of SQL Server, the loginmanager database role in Azure SQL Database is has permission to create logins. Only the server-level principal login (created by the provisioning process) or members of the loginmanager database role can create new logins.

The dbmanager role

The Azure SQL Database dbmanager database role is similar to the dbcreator fixed server role for an on-premises instance of SQL Server. Only the server-level principal login (created by the provisioning process) or members of the dbmanager database role can create databases. Once a user is a member of the dbmanager database role, it can create a database with the Azure SQL Database CREATE DATABASE command, but that command must be executed in the master database. For more information, see CREATE DATABASE (Transact-SQL) .

How to assign SQL Database server-level roles

To create a login and associated user that can create databases or other logins, perform the following steps:

The following code example shows how to create a login named login1 , and a corresponding database user named login1User that is able to create databases or other logins while connected to the master database:

Granting database access to a login

All logins must be created in the master database. After a login has been created, you can create a user account in another database for that login. Azure SQL Database also supports database roles in the same way that an on-premises instance of SQL Server does.

To create a user account in another database, assuming you have not created a login or a database, perform the following steps:

The following code example shows how to create a login named login1 and a database named database1 :

This next example shows how to create a database user named login1User in the database database1 that corresponds to the login login1 . To execute the following example, you must first make a new connection to database1, using a login with the ALTER ANY USER permission in that database. Any user connecting as a member of the db_owner role will have that permission, such as the login which created the database.

This database-level permission model in Azure SQL Database is same as an on-premise instance of SQL Server. For information, see the following topics in SQL Server Books Online references.

[AZURE.NOTE] Security-related Transact-SQL statements in Azure SQL Database may differ slightly in the parameters that are available. For more information, see Books Online syntax for specific statements.

Viewing logins and databases

To view logins and databases on your Azure SQL Database server, use the master database's sys.sql_logins and sys.databases views, respectively. The following example shows how to display a list of all the logins and databases on your Azure SQL Database server.

Azure SQL Database Security Guidelines and Limitations Connecting to SQL Database By Using Azure Active Directory Authentication

IMAGES

Resize an Azure SQL Database with Powershell
Azure SQL
Introduction to Azure SQL Database [Full Course]
How to clone an Azure SQL Database
A summary of new features in Azure SQL Database
Understanding Azure SQL Database

VIDEO

CNN anchor Ana Cabrera jumps ship for gig at MSNBC: sources
Healthy eating and glaucoma
LEBLANC vs ZED (MID)
Fireside Chat with Rippling SVP of Product, Jeremy Henrickson
Azure SQL Database
Role-play assignment 1

COMMENTS

Server roles
Role assignments may take up to 5 minutes to become effective. Also for existing sessions, changes to server role assignments don't take effect
Database-Level Roles
Special roles for SQL Database and Azure Synapse ; dbmanager, Can create and delete databases. A member of the dbmanager role that creates a
Azure SQL Database & SQL Managed Instance & Azure Synapse
A custom role enables you to create your own user-defined database roles and carefully grant each role the least permissions necessary for the
Create and Manage Role Assignments
A role assignment is a security policy that determines a user's or group's permissions. Permissions decide whether the user or group can
Database level roles in Azure SQL Database
The dbmanager role allows the user to create a database, delete a database as a database owner. It allows users to connect the Azure database as
Fixed Server Roles in Azure SQL Database Server
The Azure server roles work similarly to the roles in the on-prem SQL instance. The user server role permissions can propagate to database
How to view the roles and permissions granted to any database user
Per Managing Databases and Logins in Azure SQL Database, the loginmanager and dbmanager roles are the two server-level security roles available
Adding Users to Azure SQL Databases
Connect to your Azure SQL Database server with SSMS as an admin and choose the database you want to add a user to in the dropdown. Add Azure
azure-docs/resource-custom-roles-sql-db-managed-instance.md at
Note that this doesn't perform the actual role assignment. Minimum number of roles. We currently recommend creating a minimum of two custom roles for the APP ID
azure-content/sql-database-manage-logins.md at master
In order for logins other than the server-level principal to manage server-level security, Azure SQL Database offers two security roles: loginmanager for