sql server role assignments

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.

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.

01 April 2021

30395 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.

Rate this article

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 2

• Database Administration
• T-SQL Programming

• SQL Server training
• Write for us!

Move or copy SQL Logins by assigning roles and permissions

This article speaks to, how to copy SQL Logins on the new server with Server Level roles and database-level permissions as existing Source to Destination SQL Server instance using a script.

Every rendition of SQL Server has distinctive security highlights, as does every form of Windows, with later forms having improved usefulness over before ones. Understand that security includes alone can’t ensure a protected database application. Database Administrator plays out the errand to Move or Copy SQL Logins when a new server is going to an arrangement on any environment (Development\ Testing \Staging or Production) or presenting the new database on any of the environment.

Microsoft SQL Server gives the user and role-based security. Server predefined role with a group of permissions will be appointed to the user rather than individual permission and this arrangement of permissions of SQL Server roles can’t be changed. Every role will have recognized authorizations (permissions) and extent of access to the SQL Server and its databases.

Each SQL login of SQL Server can be doled out to SQL Server roles in database security. indeed, even we can utilize user characterized roles also with the number of principle consents as required. fundamentally, we have three sorts of login in SQL Server, for example, Windows Login, SQL Logins and Windows Group Login (Active Directory).

A user could have Grant or Deny for various object types, thusly we can create a script to give permissions to access objects to the users on another server. As a feature of database security, we ought not to give full access to everybody. along these lines, we bifurcate the user’s permissions with the SQL Logins to the assigned individual. For every database, we ought to have several SQL logins with various object-level authorizations.

In a schedule, we can enable the current users to have permissions to access on recently made database or we can adjust the authorizations or permission on the existing arrangement with the utilization of GRANT and DENNY articulations. In any case, if there should be an occurrence of server relocation or Hardware refreshment action, the permissions set ought to be foreordained with the number of permissions as best rehearses.

How to Copy or Move Logins?

Generate script at Source Server and execute at Destination with 3 errands.

• Create SQL Logins
• Assign Server role to the SQL login
• Create User at Database Level with object permissions

CREATE SQL Logins

Below query will create all logins with a password that exist on SQL Server Instance. It ought to be executed on the source server.

sys.sql_logins incorporates SQL Server inside service level users and System Administrator. So we can bar those users in the script while setting it up for SQL Server migrate logins.

See here, you can see the number of logins with hash organized password. T-SQL query will create the script for Windows logins and SQL Logins for SQL Server migrate logins. To reject the Windows Login, we can apply the filter on sp.type section by including SP.type <> ‘U’ in the above SQL explanation.

Execute the outcome set of above articulation at goal server and check those new logins are readied or not utilizing sys.sql_logins DMV. Now, logins are copied however roles that are mapped in the source server that won’t be integrated with SQL logins at the Destination Server.

Assign Server role to the login

Server roles are kept up by the Database Engineer or Administrator and apply to the whole server, not an individual database document. The public role sets the fundamental permission for all users. Each user that is added to SQL Server is consequently relegated to the public role, you don’t have to do anything. Database roles are connected to an individual database as part of security.

Each login can have a distinctive roles and permissions.

On the off chance that the user has not been assigned to any role, it is doled out to ‘Public’ role of course. In the above result, “test_test” client isn’t visible, the fact that they are not allocated to any roles. We can see in below picture.

We have enabled users to associate the SQL Server Instance utilizing the above script. Notwithstanding, for database-level permission, we have to make the user at the database level and relegate the permissions.

Create User and permissions on Database Level

As to control security imperative or to maintain a strategic distance from information harming Attacks and SQL Injection, been appointed to the role isn’t satisfactory at SQL Server. Subsequently, we apply second-level access to the database. Presently to keep it in underline with allotting permissions contingent upon sort of objects. The user ought to be allowed with important benefits just at database level by necessary privileges.

Different kind of Article or Object-level permissions can be incorporated with the users or roles relying upon the prerequisites, which could be DDL and DML level authorizations on schema objects suchlike EXECUTE, SELECT, DELETE, INSERT, ALTER, CONNECT, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, and so forth. SQL Server characterized roles have some pre-set permissions sets; be that as it may, the worth probably won’t get populated for every one of the roles. The permission gets impacted over the object can be stated by consent types GRANT, DENY, and so on.

Indeed, even inside the schema, we can access controls on different sorts of SQL Server objects, for example, the Tables (System table and User Table), Functions (Scalar Function, Table Valued Function), Views, Procedure and so on. Presently for the Financial information which should be classified, the client ought to have the entrance control on columns of the table also. All member from SQL Server login shouldn’t see the columns of the table where column level permission isn’t permitted. In this manner, when SQL Logins are being moved or copied to the next server for the same database here a similar kind of security get to permissions must be replicated and connected with logins. See here, every one of the cases we saw, we have quite recently replicated Logins and its role to SQL Instances, anyway, those ought to exist on database level too.

Underneath query will create a list of users which are existing in the database. With the end goal of the movement, the query can be executed in a LOOP to get it for all client databases.

With the expanded the size and complex nature of a database, the methodology ends up being increasingly more toilsome to deal with, specifically if attempting to allocate object-level permission to individual logins. The level of difficulty increases, and it is expanding that hitches show up during deployment, the access-control error happens, and security evades show up. This leads unavoidably to the way of least conflict where, for instance, all user accounts end up as members of the db_datawriter and db_datareader roles or, far more atrocious, reasonable access control is completely relinquished for ‘getting the application working’.

The initial phase in ensuring your customer’s information is figuring out which users need to see which information and after that enabling access to just those users. For instance, a finance agent presumably sees compensation figures for everybody in your organization while group directors approach pays rates for colleagues. Singular representatives have no compelling reason to see compensations by any means.

Now, we can generate the number of schema and object permissions for the user using the below query:

The query needs to be executed in each database using a loop. It returns user with individual permission in a separate statement.

This article is designated to help and structure a legitimate database access control framework and SQL Logins Relocation or Migration utilizing script. counting single content to play out all undertaking, it would assist a great deal with making it simple. For automation, dynamic SQL script keeps running on a remote server to make it brisk.

• Recent Posts

• Page Life Expectancy (PLE) in SQL Server - July 17, 2020
• How to automate Table Partitioning in SQL Server - July 7, 2020
• Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020

Related posts:

• How to drop a role in a SQL Server Database
• How to drop a SQL Server Login and all its dependencies
• Top SQL Server Books
• Introduction to Row-Level Security in Power BI
• Transferring SQL Logins to the secondary replica of AG using sp_help_revlogin and SSIS Transfer Login tasks

• Questions? 877-634-9222

Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

Author: JP Chen | | October 1, 2015

As we move into new locations such as offices, apartments, or houses, we need to have the new keys given to us. Obviously, we do not want the keys for our old locations to work on the new ones. Unlike relocations in real life, we will want the SQL Servers authenticated logins and Windows authenticated logins to work on the new servers as we migrate the databases. Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. The repetitive task of scripting each one out and re-creating them one by one on the new server and re-adding the logins to the server roles and granting them server level permissions will be a royal pain. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

Yes! It is and it can be done.

First, let’s review the fundamentals of logins. Access to the server is granted via logins. There are two main categories of logins:

Windows authenticated logins: These logins can either be logins mapped to Windows users or groups. These logins are integrated with the Active Directory. Here’s the example script to create a Windows authenticated login:

SQL Server authenticated logins: These logins are not based on Windows. They are maintained within SQL Server. Here’s the example script to create a SQL Server authenticated login:

Here are the system functions, stored procedures, tables and case expressions that you need to know to get started:

• SUSER_ID(): Returns the login identification number of the user.
• CONVERT() : Converts an expression of one data type to another. In this case, we will use this function to convert the SQL Server login password from binary data type to variable-length Unicode string data.
• QUOTENAME(): Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
• SERVER_PRINCIPALS: Contains a row for every server-level principal. It contains the important data columns such as the login name, login type, default database, and default language.
• SQL_LOGINS: Returns one row for every SQL Server authentication login. It contains the critical database columns such as the password_hash, is_expiration_checked, and is_policy_checked.
• SERVER_ROLE_MEMBERS: Returns one row for each member of each fixed and user-defined server role. Note that SQL Server 2012 and later versions have the user-defined server roles.
• SERVER_PERMISSIONS: Returns one row for each server-level permission. It contains important data columns such as state_desc for the description of permission state and permission_name for the permission name.
• COLLATE: This clause can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast. A collation specifies the rules for proper use of characters for either a language or an alphabet. Here’s we will change the column collation to SQL_Latin1_General_CP1_CI_AS.
• CASE EXPRESSION: Evaluates a list of conditions and returns one of the possible result expressions. In this case, we will use the search CASE expression.

Before running the script, you need to be aware that some the SQL Server authenticated logins with their password scripted out will exceed the default data column characters limitation of 256. You will need to change your results to display more characters in the query result to prevent the results being truncated. You can change it by going to the “Tools menu > Options > Query Results > SQL Server > Results to Text” to increase the maximum number or characters returned to 8192 – the maximum.

Here’s the script:

After you have migrated all the required logins over to the destination server, you should check for orphaned users – the database users without associated logins. You can resolve them by mapping them to valid logins. You can report and fix them by using the sp_change_users_login stored proc.

Blog Author

Global Practice Lead of SQL Server

Related Posts

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

• content_search

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

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 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..

REQUEST COMPLIMENTARY SQLS*PLUS LICENCE

Assigning Permissions and Roles in SQL Server

SQL Server applies role-based security rights delimitation. A role is a certain set of rights that can be assigned to a certain user or group of users .

In SQL Server there are default server and database level roles, which have a predefined set of permissions assigned to them.

You can also create your own roles for which the administrator can define a list of permissions and bans. Default Server and Database Level Roles have a predefined set of permissions, each at its own level and they cannot be changed.

The article below describes the default Server and Database Level Roles that can be assigned to users and groups and how to assign them.

Built-in SQL Server server level roles

The table below shows the default server level roles and a brief description of them:

The roles listed in the table are based on the 34 standard server level permissions that are allowed or denied for the built-in role, below is the list of server level permissions:

BULK OPERATIONS ANY AVAILABILITY GROUP ANY CONNECTION ANY CREDENTIAL ANY DATABASE ANY ENDPOINT ANY EVENT NOTIFICATION ANY EVENT SESSION ANY CONNECTED SERVER ANY LOGIN ANY SERVER AUDIT ANY SERVER ROLE ADDITIONAL RESOURCES SERVER STATE ALTER SETTINGS ALTER TRACE AUTHENTICATE SERVER CONNECT ANY DATABASE CONNECT SQL CONTROL SERVER CREATE ANY DATABASE CREATE AVAILABILITY GROUP CREATE DDL EVENT NOTIFICATION CREATE ENDPOINT CREATE SERVER ROLE CREATE TRACE EVENT NOTIFICATION EXTERNAL ACCESS ASSEMBLY IMPERSONATE ANY LOGIN SELECT ALL USER SECURABLES SHUTDOWN UNSAFE ASSEMBLY VIEW ANY DATABASE VIEW ANY DEFINITION VIEW SERVER STATE

Built-in SQL Server Database Level Roles

The table below describes the database level roles that exist in all databases and summarizes the rights that these roles provide.

All the database level roles listed below are built-in, and their rights cannot be changed.

The role of public was described in the table above, it is on every damage, including the database level . All default created roles, whether database level or server roles have invariable permissions, you can not add new permissions or remove them, the only exception is the public role.

At the database level, there is also a list of 77 default permissions, some of which are already included in the server level roles and permissions:

ALTER ANY APPLICATION ROLE ANY ASSEMBLY ANY ASYMMETRIC KEY ANY CERTIFICATE ANY COLUMN ENCRYPTION KEY ANY COLUMN MASTER KEY ANY CONTRACT ANY DATABASE AUDIT ANY DATABASE DDL TRIGGER ANY DATABASE EVENT NOTIFICATION ANY DATABASE EVENT SESSION ANY DATABASE SCOPED CONFIGURATION ANY DATASPACE ANY EXTERNAL DATA SOURCE ANY EXTERNAL FILE FORMAT ANY FULL TEXT CATALOG ALTER ANY MASK ANY MESSAGE TYPE ANY REMOTE SERVICE BINDING ALTER ANY ROLE ANY ROUTE ANY SCHEMA ANY SECURITY POLICY ANY SERVICE ANY SYMMETRIC KEY ALTER ANY USER AUTHENTICATE BACKUP DATABASE BACKUP LOG CHECKPOINT CONNECT CONNECT REPLICATION CONTROL CREATE AGGREGATE CREATE ASSEMBLY CREATE ASYMMETRIC KEY CREATE CERTIFICATE CREATE CONTRACT CREATE DATABASE CREATE DATABASE DDL EVENT NOTIFICATION CREATE DEFAULT CREATE A FULLTEXT CATALOG CREATE FUNCTION CREATE MESSAGE TYPE CREATE PROCEDURE CREATE QUEUE CREATE REMOTE SERVICE BINDING CREATE ROLE CREATE ROUTE CREATE RULE CREATE SCHEMA CREATE SERVICE CREATE SYMMETRIC KEY CREATE SYNONYM CREATE TABLE CREATE TYPE CREATE VIEW CREATE XML SCHEMA COLLECTION DELETE EXECUTE EXECUTE ANY EXTERNAL SCRIPT INSERT KILL DATABASE CONNECTION REFERENCES SELECT SHOWPLAN SUBSCRIBE QUERY NOTIFICATIONS TAKE OWNERSHIP UNMASK UPDATE VIEW ANY COLUMN ENCRYPTION KEY DEFINITION VIEW ANY COLUMN MASTER KEY DEFINITION VIEW DATABASE STATE VIEW DEFINITION

You can get a list of all permissions on the SQL server by executing the command:

SELECT * FROM sys.fn_builtin_permissions('');

The result will look like this:

NoSQL: life without a schema

Preamble​​NoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...

MongoDB Sample operators

Preamble​​MongoDB Conditional operators specify a condition to which the value of the document field shall correspond.Comparison Query Operators $eq...

5 Database management trends impacting database administration

5 Database management trends impacting database administrationIn the realm of database management systems, moreover half (52%) of your competitors feel...

SQL Server data types

The data type is defined as the type of data that any column or variable can store in MS SQL Server. What is the data type? When you create any table or...

SQL Server Architecture

Preamble​​MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a query.SQL Server accepts,...

Get a better understanding of the MongoDB master slave configuration

First the basics: what is the master/slave?One database server (“master”) responds and can do anything. A lot of other database servers store copies of all...

Run a MongoDB data adapter for Atom Hopper with Netbeans

Preamble​​Atom Hopper (based on Apache Abdera) for those who may not know is an open-source project sponsored by Rackspace. Today we will figure out how to...

Improve MongoDB aggregation structure

Preamble​​MongoDB recently introduced its new aggregation structure. This structure provides a simpler solution for calculating aggregated values rather...

Passing MongoDB data modeling

FlexibilityOne of the most advertised features of MongoDB is its flexibility.  Flexibility, however, is a double-edged sword. More flexibility means more...

SQLShell: SQL tool for multiple databases with NoSQL potential

Preamble​​SQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.Why use it?If you...

Use Mongosniff to clarify what your MongoDB hears and says

Preamble​​Writing an application on top of the framework on top of the driver on top of the database is a bit like a game on the phone: you say “insert...

Notes on Oracle Coherence

Preamble​​Oracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...

FrankenQueries: when SQL and NoSQL collide

Preamble​​IBM pureXML, a proprietary XML database built on a relational mechanism (designed for puns) that offers both relational ( SQL / XML ) and...

PostgreSQL array

  What is PostgreSQL array? In PostgreSQL we can define a column as an array of valid data types. The data type can be built-in, custom or enumerated....

7 steps to create a new Oracle database from the command line

Preamble​​If you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.In this...

Restoring access to the SQL Server instance without restarting

Preamble​​Starting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...

Read Data Stored in a Lake Database using Azure Synapse Analytics

By: John Miner   |   Updated: 2024-04-25   |   Comments   |   Related: > Azure Synapse Analytics

Azure Synapse supports the concept of a lake database, which can be defined by either Spark Hive Tables or Common Data Model (CDM) exports. There is support for an active link from Data Verse to Synapse. This link allows Power Apps and/or Dynamics 365 to export data continuously to the lake database in CDM format.

Having a SPARK cluster run all the time to view read-only data can be costly. Because data is written once and read many times, how can we reduce the cost of our company's reading activities?

Azure Synapse Analytics Serverless SQL pools allow the architect to replace the interactive SPARK cluster with a lower-cost service. Below is a high-level architecture diagram of Synapse Serverless Pools. Queries submitted to the Serverless SQL pool can view data stored in any lake database format once security is set up correctly.

Business Problem

Our manager has asked us to investigate how to read data stored in a lake database using Azure Synapse Analytics to reduce our overall cost. We will focus on tables created by the Apache Spark cluster. Here is a list of tasks that we need to investigate and solve.

There are many concepts to review and explain during this investigation. I've executed a word cloud Spark notebook in Azure Synapse to highlight the keywords. Database , security , table , and storage are very common words.

At the end of the investigation, we will understand how to manage SQL Serverless pools for Azure Synapse lake databases.

Create Environment

For our experiments, we need a storage account configured for Azure Data Lake Storage and Azure Synapse Workspace . Of course, we need to deploy and configure the Azure Synapse service. Additionally, we will want to test access to the lake database using a virtual machine with SQL Server Management Studio ( SSMS ) installed as an application. This test can be repeated with other user tools such as Azure Data Studio, Power BI Desktop, or Anaconda Spyder.

The above image shows my dashboard, which I use when teaching an Azure Synapse class. Why are there two storage accounts? The Synapse service requires a data lake storage account upon deployment. This account is named sa4synapse and stores the managed tables. The unmanaged tables are stored in the generic storage account, sa4adls2030 .

Managed Tables - Lake Database

The first step is to create a Spark notebook that reads all the CSV files in a sub-directory into one data frame. The code below reads all S&P stock files for 2013 into a dataframe named df_2013 .

Copy this code four more times and adjust for 2014 to 2017. The next step is to combine the data frames into one. The unionAll method allows us to complete this technical task.

Now, we need to create a new database called stocks if it does not exist. It is important to note that our magic command has changed from %pyspark (Spark Python code) to %sql (Spark SQL code).

To make the code restart-able, we should always drop an existing table before creating a new one.

The last step is to save our data as a hive table. When creating managed tables, the delta file format is used by default. The first time I ran this code, I left off the repartition command and ended up with 81 separate files. This is not a problem unless you must manually assign file and folder rights to a user.

The query below shows Microsoft's stock data sorted by trade date. Unfortunately, we have unexpected results. The st_date column is a string, not a date. This can be fixed by adding a new column using the withColumn method. The definition of this column should use the to_date method to cast the string to a date. I'll leave this exercise up to the reader to complete.

The query below shows the total number of records in all 2,533 CSV files is 638,355. That is a lot of files!

This example used the comma separated values (CSV) format, which is considered weak in nature. To make loading faster, it is important to supply a schema definition with this type of format. In short, creating managed tables in Azure Synapse using the Spark engine can be easily done. Just remember that dropping a managed table also deletes the underlying data files.

Unmanaged Tables - Lake Database

The AdventureWorks database is a sample database that could be used to learn about Microsoft SQL Server databases. The LT version of this database was a paired-down version of the OLTP database. I have loaded the delimited files into the data lake and converted them to Apache Parquet format. This format is considered strong in nature. The superior properties of Parquet are its binary format, known column names, known data types, and ability to be partitioned.

Creating a new schema (database) allows the developer to segregate the two data sets: stocks and bikes. Please see the code below that drops the database. Again, we are using Spark notebooks with the appropriate magic commands.

The next step is to create a database named saleslt . Let's create our first table after completing this step.

The code below creates the table named dim_currency . Because Apache Parquet is a strong file format, we only need to supply the location of the directory containing the parquet files. Please see the code bundle at the end of the article for creating all tables in the saleslt schema.

The query below shows the details of the currency dimension table. The notebook is using the Serverless SQL pool to query the data stored in the unmanaged hive table.

This example uses the Apache Spark file format, which is considered strong in nature. We do not have to supply a schema definition since it is built into the binary file. In short, creating unmanaged tables in Azure Synapse using the Spark engine is a piece of cake. Don't worry about dropping the table; the data will not be removed.

Limitations - Lake Database

Synapse Serverless pools are used to present data to the user in a read-only format. How can we determine if a hive table is managed (local) or unmanaged (external)? The describe table command can be used to find out the nitty-gritty details of the hive table.

Place the above code in a Spark notebook. Execute the code to see the following output. We can see that the currency dimension is stored in our general storage account.

Let's repeat this task for the stocks hive table.

Execute the code above in a Spark notebook to see the output below. We can see that the currency dimension is stored in our service storage account.

Lake databases have one major limitation. We can create views using the Spark engine, but views cannot be queried using the Serverless SQL pool. The image below shows that the system catalog named sys.views does exist, but the view named rpt_prepared_data is not listed. However, if we run a Spark SQL query, we can list the contents of the view.

To recap, the Serverless SQL pool can access both managed and unmanaged tables. Spark views are not currently supported. So far, we have no problems with security. That is because the [email protected] account I'm using has full access to the Azure Subscription and full rights to both data lake storage accounts. In the next section, we will talk about security patterns.

Security Patterns

Many of the security patterns in Azure have two or more levels of security. The first level is access to the service. The second level is fine-grained access to what the service provides. Let's start with the Azure Data Lake Storage. The role-based access controls (IAM) have two roles required for access to the data lake. The folders and files with the data lake can be given an access control list (ACL). Both IAM and ACL must be configured correctly so that a user can gain access to the lake database. Please see the table below for details.

The Azure Synapse service has the following security layers:

The image below shows that [email protected] is a contributor to the Azure Synapse workspace.

This active directory account has Synapse SQL administrator rights. The screenshot below was taken before I added [email protected] to the access control list.

Additionally, this user has been given full rights to both data lake storage accounts. Let's try accessing both the managed and unmanaged tables using SSMS, which is installed on the virtual machine called vm4sql19 .

Please note that I chose to use Azure Active Directory password authentication. See the above image. Since two-factor authentication is set up, I am prompted on my authenticator application to enter a code.

The above query shows the S&P 500 stock data in the managed hive table, while the query below shows the currency dimension in the unmanaged hive table.

So far, we have discussed rights using Microsoft Entra ID, formerly known as Active Directory Security . However, Azure Synapse supports Standard SQL Security using logins , users , database roles , and database scoped credentials . The table below shows the four topics we will explore later.

Today, we showed how to set up security for a single user. In real life, we would use groups when possible and add users to groups. In the next section, we will show the issues you might encounter when adding a new active directory user to Azure Synapse to get access to the lake database.

New Active Directory User

I have created some users in my domain based on DC Comics cartoon characters. A new company user called [email protected] wants access to the Synapse Lake Databases. Unfortunately, the Azure Administrator has only given him contributor rights (IAM) at the service level. If you see error 403, the user has not been given rights with the Synapse service.

This can be easily fixed by assigning rights to [email protected] via the access control form. The image below shows that "joker" has been given SQL administration rights.

The next common error is not having access to the serverless SQL pool. Please see the image below for the error message. This was generated by reducing the rights of the user to Synapse SQL User.

This can be fixed by adding the user to the sysadmin group for the server if they need access to all databases or giving them the Synapse SQL Administrator role. See this Microsoft Learn web page to set up access. Please note that the T-SQL queries for server roles and database roles show the details.

The next error is very common. We cannot list the contents of the ADLS directory since we were not given IAM and ACL rights. Please see the image below for details.

The first task is to assign IAM rights to the user named "joker." The image below shows the two roles given to the user account.

One might try assigning rights in Azure Synapse Workspace. The image below shows the path to the managed hive table called stocks . This is not the best tool for the job since the user needs access to every folder and file.

The best tool for the job is Azure Storage Explorer. The image below shows the user being assigned permissions for access – the current folders and/or files and default – inherited rights for future folders and/or files.

The above action gives the user "joker" rights to the container named sc4synapse . Right-click and select the propagate access control list option. This will replicate the rights at the container level to all child objects.

We can re-execute the query in Azure Synapse Workspace to display stock data for Microsoft.

Please log into SSMS using the "joker" account and Azure Active Directory Password authentication. The screenshot below shows we have access to the managed table.

Finally, if we try querying the currency table in the saleslt database, we get the listing error again.

The unmanaged table resides in a different storage account and storage container. To make this error disappear, grant "joker" access to general Azure Data Lake Storage named sc4adls2030 . In a nutshell, granting access to Active Directory accounts is relatively easy.

New Standard SQL User

The design pattern for Standard SQL security leverages existing T-SQL commands. Let's start by creating a new login called Dogbert . Execute the code below in a SQL query notebook.

The next step is to create a user for each database and grant access to the database. The code below grants Dogbert access to the saleslt database.

We get a login issue if we try to access the stocks database right now. Execute the above code for the stocks database.

The next error is that the CREDENTIAL for the parquet files that comprise the managed table is missing.

The code below creates a wild card credential for the snp500 directory. This T-SQL command uses a Shared Access Signature (SAS). Please use the hyperlink to learn more about how to create one. Unlike storage account access keys , a SAS can be limited in scope and duration. Make sure to set the expiration date for at least six months out. Remember to rotate the key before it expires.

Once the credential is in place, we can select data from the table named snp500. Please see the image below for details.

There are two problems with the current credential design. First, what happens if we have many files in many different directories? Second, how can we easily drop the credential, create the credential, and give the user access to the credential?

I will use a metadata-driven approach to create a view with all the T-SQL statements we need. This solution is tailored to the saleslt tables but can be expanded to different storage accounts, storage containers, user-defined folders, and final data files.

I used a simple OPENROWSET query to show the contents of the control file.

The code below assumes the access control file has been exposed as an external table. This must be done on the Serverless SQL pool-side of the architecture. I ended up slipping the database objects into a database ( mssqltips ) I used in a prior article. The view has a drop, create, and grant statements, which we need. To make this code generic, add the full data lake path and file type to the control file.

The last thing to do is to execute a while loop to process the T-SQL statements. Remember, cursors are not supported in the SQL pool syntax.

The next step is to validate that the credentials were applied to tables in the saleslt database. The screenshot below shows a select top one thousand query generated by SSMS.

Granting access to standard security users is more complex. It involves server logins, database users, database rights, and file credentials. The hardest part is assigning a credential to each directory and/or file. However, we demonstrated that it could be done for both managed and unmanaged tables created by the Apache Spark Engine.

Many companies are creating data lakes in the Azure Cloud. It can be expensive to leave a Spark cluster running 24 hours a day for reporting needs. One way to reduce this cost is to use Azure Synapse Serverless Pools.

Azure Synapse supports the concept of a lake database, which is defined by either Spark Hive Tables or Common Data Model exports. We focused on both managed tables placed in the service storage and unmanaged tables kept in general storage. The Apache Spark engine was used to create tables using both weak and strong file types. Regardless of table type, access to both the Synapse Service and Data Lake Storage is required to query data.

Data Lake Storage requires two levels of access. The IAM layer has both a general role and a specific role related to storage. The ACL layer allows the architect to apply read, write, and execute permissions to both folders and files. Storage is key to any data lake!

The Azure Synapse Service has two layers of security. The first is the IAM layer that allows you into the workspace. The second gives you the right to work within the environment. There are two flavors of security that you can offer your end users. Microsoft Entra ID, formally Active Directory (AD) with Password, is common nowadays. However, older applications might require Standard SQL Security, meaning the administrator must manage server logins, database users, database rights, and file credentials. Regardless of the security you use, please use AD groups and/or database roles to reduce the total management cost.

Enclosed are the following artifacts to start your journey with Azure Synapse for Lake Databases: CSV files , Parquet files , SQL scripts , and Spark notebooks .

• Learn how to read delta tables from Azure Data Factory

About the author

Comments For This Article

Related Content

Raw Data Ingestion into Delta Lake Bronze tables using Azure Synapse Mapping Data Flow

Implement Surrogate Keys Using Lakehouse and Azure Synapse Analytics

Implementing Slowly Changing Dimensions on Lakehouse with Synapse Mapping Data Flow

Enforce data integrity rules on your Delta tables using Synapse Mapping Data Flow

Incremental Ingestion of Fact Tables on Lakehouse using Azure Synapse Analytics Mapping Data Flow - Part 1

Data Ingestion Into Landing Zone Using Azure Synapse Analytics

Implement Time Dimensions on Lakehouse using Azure Synapse Analytics Mapping Data Flow

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

SQL Date Format Options with SQL CONVERT Function

SQL Date Format examples using SQL FORMAT Function

SQL Server CROSS APPLY and OUTER APPLY

DROP TABLE IF EXISTS Examples for SQL Server

SQL Server Cursor Example

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

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

SQL Convert Date to YYYYMMDD

SQL NOT IN Operator

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

List SQL Server Login and User Permissions with fn_my_permissions

SQL Server Loop through Table Rows without Cursor

How to monitor backup and restore progress in SQL Server

SQL Server Database Stuck in Restoring State

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