sql server 2019 user rights assignment

This browser is no longer supported.

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

User Rights Assignment

• 13 contributors

Provides an overview and links to information about the User Rights Assignment security policy settings user rights that are available in Windows. User rights govern the methods by which a user can log on to a system. User rights are applied at the local device level, and they allow users to perform tasks on a device or in a domain. User rights include logon rights and permissions. Logon rights control who is authorized to log on to a device and how they can log on. User rights permissions control access to computer and domain resources, and they can override permissions that have been set on specific objects. User rights are managed in Group Policy under the User Rights Assignment item.

Each user right has a constant name and a Group Policy name associated with it. The constant names are used when referring to the user right in log events. You can configure the user rights assignment settings in the following location within the Group Policy Management Console (GPMC) under Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment , or on the local device by using the Local Group Policy Editor (gpedit.msc).

For information about setting security policies, see Configure security policy settings .

The following table links to each security policy setting and provides the constant name for each. Setting descriptions contain reference information, best practices for configuring the policy setting, default values, differences between operating system versions, and considerations for policy management and security.

Related topics

• Security policy settings reference

Submit and view feedback for

Additional resources

Assign System Administrator Privileges for SQL Server 2019 - Intergraph Spoolgen - Installation & Upgrade - Hexagon PPM

Spoolgen web api installation and configuration.

Perform the following steps on the Spoolgen Web API database server.

Open Microsoft SQL Server Management Studio , and then expand the listing for your database server in the left tree view.

In the tree view, expand the listing under Security , right-click Logins , and then select New Login to open the Login - New dialog.

Type the user account to be assigned administrative privileges in the Login name text box. For example, type AdminUser1 .

Select Server Roles under the Select a page heading to display the Server Roles list.

In the Server Roles list, select the sysadmin check box.

Administrators need the sysadmin role. Standard users need public , db_datareader , and db_datawriter database role membership mapping to each one of the Spoolgen Web API databases. Standard users also need Execute permission to each database.

We do not recommend giving standard users db_owner permissions as it gives the users a very high privilege to the database.

Configure Windows Service Accounts and Permissions exemplary on SQL Server 2019

Current windows operating systems provide a wide choice of accounts you can use to run local services. Today I want to pay attention to the individual accounts you can use as service accounts to run the different SQL Server 2019 services.

Detailed documentation about local user accounts in Windows you will also find in my post below.

Local user accounts in Windows

Configure Windows Service Accounts and Permissions https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions

You can choose between the following accounts that can be configured to start SQL Server services:

• domain user accounts
• local user accounts
• managed service accounts (group managed service accounts)
• virtual accounts
• built-in system accounts

Under https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#Default_Accts you will see the recommended accounts by Microsoft to use for.

When resources external to the SQL Server computer are needed, Microsoft recommends using a Managed Service Account (MSA) , configured with the minimum privileges necessary. When installed on a Domain Controller, a virtual account as the service account is not supported.

Changing Account Properties Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services , or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine . Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings. For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for Power Pivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration. To change Reporting Services options, use the Reporting Services Configuration Tool.

Managed Service Accounts, Group Managed Service Accounts, and Virtual Accounts

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#New_Accounts

Managed service accounts , group managed service accounts , and virtual accounts are designed to provide crucial applications such as SQL Server with the isolation of their own accounts, while eliminating the need for an administrator to manually administer the Service Principal Name (SPN) and credentials for these accounts. These make long term management of service account users, passwords and SPNs much easier.

• Managed Service Accounts A Managed Service Account (MSA) is a type of domain account created and managed by the domain controller. It is assigned to a single member computer for use running a service. The password is managed automatically by the domain controller. You cannot use a MSA to log into a computer, but a computer can use a MSA to start a Windows service. An MSA has the ability to register a Service Principal Name (SPN) within Active Directory when given read and write servicePrincipalName permissions. A MSA is named with a  $  suffix, for example  DOMAIN\ACCOUNTNAME$ . When specifying a MSA, leave the password blank. Because a MSA is assigned to a single computer, it cannot be used on different nodes of a Windows cluster.
• Group Managed Service Accounts A Group Managed Service Account (gMSA) is an MSA for multiple servers. Windows manages a service account for services running on a group of servers. Active Directory automatically updates the group managed service account password without restarting services. You can configure SQL Server services to use a group managed service account principal. Beginning with SQL Server 2014, SQL Server supports group managed service accounts for standalone instances, and SQL Server 2016 and later for failover cluster instances, and availability groups. To use a gMSA for SQL Server 2014 or later, the operating system must be Windows Server 2012 R2 or later. Servers with Windows Server 2012 R2 require  KB 2998082  applied so that the services can log in without disruption immediately after a password change.
• Virtual Accounts Virtual accounts (beginning with Windows Server 2008 R2 and Windows 7) are  managed local accounts  that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment . If the default value is used for the service accounts during SQL Server setup, a virtual account using the instance name as the service name is used, in the format  NT SERVICE\ <SERVICENAME> . Services that run as virtual accounts access network resources by using the credentials of the computer account in the format  <domain_name> \ <computer_name> $ . When specifying a virtual account to start SQL Server, leave the password blank. If the virtual account fails to register the Service Principal Name (SPN), register the SPN manually. For more information on registering a SPN manually, see  Manual SPN Registration . Virtual accounts cannot be used for SQL Server Failover Cluster Instance , because the virtual account would not have the same SID on each node of the cluster.

Security Note:  Always run SQL Server services by using the lowest possible user rights. Use a  MSA ,  gMSA  or  virtual account  when possible. When MSA, gMSA and virtual accounts are not possible, use a specific low-privilege user account or domain account instead of a shared account for SQL Server services. Use separate accounts for different SQL Server services. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported.

A detailed guide about setting up Managed Service Accounts (MSA) , you will find in my following post.

Set up Group Managed Service Accounts (gMSA) vs. Standalone Managed Service Accounts (sMSA)

Set up SQL Server 2019 and configure the service accounts

So now we will run the SQL Server 2019 setup and go through the steps with the service accounts configuration.

First we install a default instance , later we also add a named instance to see the difference regarding naming of the service accounts .

Here you can see as mentioned above with

If the default value is used for the service accounts during SQL Server setup, a virtual account using the instance name as the service name is used, in the format NT SERVICE\SERVICENAME .

Here you can see very nice, that the virtual account is using the instance name as the service name NT Service\MSSQLSERVER

Important note Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services , or to change the password for the account . In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.f https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#Changing_Accounts

Service Configuration and Access Control

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#Serv_SID

SQL Server 2019 (15.x) enables per-service SID for each of its services to provide service isolation and defense in depth. The per-service SID is derived from the service name and is unique to that service. For example, a service SID name for a named instance of the Database Engine service might be  NT Service\MSSQL$ <InstanceName> . Service isolation enables access to specific objects without the need to run a high-privilege account or weaken the security protection of the object. By using an access control entry that contains a service SID, a SQL Server service can restrict access to its resources.

On Windows 7 and Windows Server 2008 R2 ( and later ) the per-service SID can be the virtual account used by the service .

For most components SQL Server configures the ACL for the per-service account directly, so changing the service account can be done without having to repeat the resource ACL process.

Windows Privileges and Rights

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#Windows

The account assigned to start a service needs the  Start, stop and pause permission  for the service. The SQL Server Setup program automatically assigns this . First install Remote Server Administration Tools (RSAT). See  Remote Server Administration Tools for Windows 10 .

File System Permissions Granted to SQL Server Per-service SIDs or Local Windows Groups

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#Reviewing_ACLs

SQL Server service accounts must have access to resources. Access control lists are set for the per-service SID or the local Windows group.

For failover cluster installations , resources on shared disks must be set to an ACL for a local account.

Now as mentioned above, we will also install a named instance , to see the default naming of the virtual accounts , SQL Server Setup will create for us.

Here you can see as mentioned above

The per-service SID is derived from the service name and is unique to that service. For example, a service SID name for a named instance of the Database Engine service might be NT Service\MSSQL$<InstanceName> .

Related Posts

Set up domainkeys identified mail (dkim) on exchange on-premise, installing sap data intelligence on azure kubernetes service – part 3, sso single-sign-on to your onpremise rds remote desktop services 2016/2019 environment, latest posts, configure expires http headers and cache-control on apache, software defined data center (sddc) vs. private cloud, explore the microsoft defender family.

We’re an affilliate We hope you love the products we recommend! Just so you know, we may collect a share of sales or other compensation from the links on this page. Thank you very much if you use our links, we really appreciate it.

Assign Permissions to User in SQL Server

In the previous chapter, you learned to create a new user in the database. Here, you will learn to grant permissions to a user in SQL Server.

You can GRANT and REVOKE permissions on various database objects in SQL Server. User permissions are at the database level.

You can grant any or a combination of the following types of permissions:

• Select: Grants user the ability to perform Select operations on the table.
• Insert: Grants user the ability to perform the insert operations on the table.
• Update : Grants user the ability to perform the update operations on the table.
• Delete: Grants user the ability to perform the delete operations on the table.
• Alter: Grants user permission to alter the table definitions.
• References : References permission is needed to create a Foreign key constraint on a table. It is also needed to create a Function or View WITH SCHEMABINDING clause that references that object
• Control : Grants SELECT, INSERT, UPDATE, DELETE, and REFERENCES permission to the User on the table.

Similarly, you can revoke any of the already assigned permissions.

You can GRANT or REVOKE permissions by using SSMS or by executing T-SQL Script.

Grant or Revoke permissions using T-SQL

The following SQL grants the SELECT permission to a user Steve on Employee table in the HR database.

The following SQL grants the SELECT, INSERT, UPDATE, DELETE permissions to a user Steve on EmployeeAddress table in the HR database.

The following grants SELECT permission on the Employee table to all users by granting the privileges to the public role.

The following revokes DELETE permission to Steve user on the EmployeeAddress table.

Learn more on GRANT permissions and REVOKE permissions .

Grant or Revoke permissions using SSMS

Let's grant permissions to a user using SQL Server Management Studio.

Login to SQL Server Management Studio. In Object Explorer on the left pane, expand the Databases folder and select the concerned database and navigate to the by expanding Security and Users folders. Right-click the User to which you want to GRANT or REVOKE the permissions. Here, we will GRANT permissions to our new user 'Steve'. So right-click on a user Steve and click Properties , as shown below.

This will open Database User window, as shown below. Select Securable tab on the left pane to GRANT or REVOKE permissions to database objects. Click on the Search button to add database objects to which you want to GRANT or REVOKE permissions. This will open Add Objects popup, as shown below.

On the Add Objects popup, you have three options to select database objects (such as table, view, stored procedures, etc.) to which you want to grant permissions.

• Specific Objects.. option allows you to find and select specific database objects to grant or revoke permissions.
• All Objects of types.. option allows you to find and select a specific type of database object to grant or revoke permissions such as table, stored procedures, views, inline functions, etc. For example, selecting table type will display all the tables in the database to grant or revoke permissions.
• All Objects belonging to the schema option allows you to select a schema to grant permissions to all the database objects of that schema.

Here, we will select "All Objects belonging to the schema" radio button. Select a schema from the dropdown and click OK. This will display all the database objects under this schema, as shown below.

Now, select the table for which you have to give permissions in the upper pane. In the bottom pane, select Explicit tab and click on SELECT, UPDATE, and any other permission that you wish to grant to the user for the table. Click on the OK button. The selected permissions will be granted to the user. To revoke permission, uncheck the permission checkbox.

Thus, you can grant or revoke permissions to the specific user in SQL Server database.

.NET Tutorials

Database tutorials, javascript tutorials, programming tutorials.

Assign System Administrator Privileges for SQL Server 2019 - Intergraph Smart 3D - Installation & Upgrade - Hexagon

Intergraph smart 3d installation.

Perform the following steps on the Smart 3D database server.

Open Microsoft SQL Server Management Studio and expand the listing for your database server in the left tree view.

In the tree view, expand the listing under Security , right-click Logins , and select New Login .

The Login - New dialog box displays.

Type the user account to be assigned administrative privileges in the Login name text box. For example, type AdminUser1 .

Select Server Roles under the Select a page heading to display the Server Roles list.

In the Server Roles list, select the sysadmin check box.

Administrators need the sysadmin role. Standard users need public , db_datareader , and db_datawriter database role membership mapping to each one of the Smart 3D databases. Standard users also need Execute permission to each database.

We do not recommend granting standard users db_owner permissions as it gives the users a very high privilege to the database.

List SQL Server Login and User Permissions with fn_my_permissions

By: Manvendra Singh   |   Updated: 2021-04-13   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | > Security

As a SQL Server DBA, we often get requests to check permissions on databases or database objects. Sometimes we need to check what permissions a user or login has on a specific object or check the permissions on a securable. In this article I will explain how to check permissions for a user or login or for your own login.

The permissions of a principal to a securable in a database consists of permissions explicitly granted to either the user or permissions derived from a group the user belongs to. I assume you are aware about the terms securable, principal and permissions because I am going to talk about these three terms throughout this article. These terms are not new, but some of the terms we use in our day to day DBA work like login, user, objects, etc.

Below is a list of security terms that you generally hear when you work with SQL Server security.

• Server – For example databases, logins, endpoints, availability groups and server roles
• Database – For example database role, application roles, schema, certificate, full text catalog, user
• Schema – For example table, view, procedure, function, synonym
• Permission: Every SQL Server securable has associated permissions like ALTER, CONTROL, CREATE that can be granted to a principal. Permissions are managed at the server level using logins and at the database level using users.
• Principal: The entity that receives permission to a securable is called a principal. The most common principals are logins and database users. Access to a securable is controlled by granting or denying permissions or by adding logins and users to roles which have access.

All three terms are needed to complete an access request. You need an object that comes under a securable, then you need a level of permissions and finally you need a login or user which will use the permission on the given securable. You cannot proceed with any access request if you are missing any value from the above three items.

Now, let’s cover the topic of checking the permission level of any principal on any securable using the SQL Server system function sys.fn_my_permissions .

Check Effective Permissions of Server or Database Principal

SQL Server includes a very useful system function sys.fn_my_permissions to list all the permissions of a particular principal (user or login) and this system function will help you list all permissions of a principal on a specific database object (securable). If you are not aware of all of the permissions you have on your SQL Server instance then you can use this system function to list all of your permission levels assigned on a specific securable.

Below is the syntax of this system function:

If you want to check all built in permissions for various securables in SQL Server to decide what level of permission you need to assign for a specific principal on a specific object then you can get this information by using another system function sys.fn_builtin_permissions . I am going to explain both system functions in this article to understand how to check permissions of a principal on a securable in SQL Server.

List All Built in Permissions in SQL Server

Before starting with checking the level of permissions for a principal on any of securable, I would like to explain a bit about all available permissions and securables in SQL Server. You can get this by using one of the system function sys.fn_builtin_permissions , which is a table-valued function that emits a copy of the predefined permission hierarchy.

Run the below statement to list all built in permissions and securables in SQL Server. The first column class_desc shows the securable class whereas the second column permission_name shows the type of permission that can be granted to any principal on the securable.

This is a very big list and cannot be captured in a screenshot. You can see securable "Database" has many types of permissions available that can be granted to a principal for various uses.

If you want to check how many types of securable classes are present in SQL Server, then you can run the below command to get this data. You can see there are 27 securable classes in SQL Server on which you can assign various levels of permissions (that is given in column permission_name) to a principal in SQL Server.

Here is the output of above T-SQL and lists all available securables in SQL Server.

• XML SCHEMA COLLECTION
• EXTERNAL LANGUAGE
• DATABASE SCOPED CREDENTIAL
• ASYMMETRIC KEY
• APPLICATION ROLE
• REMOTE SERVICE BINDING
• MESSAGE TYPE
• AVAILABILITY GROUP
• SERVER ROLE
• SYMMETRIC KEY
• CERTIFICATE
• SEARCH PROPERTY LIST
• FULLTEXT STOPLIST
• FULLTEXT CATALOG

List All Effective Permissions I have on Various Securables in SQL Server

Now, we get the permissions in SQL Server that can be granted to a principal to access a securable that is table, view or procedure. Here I will show you another system function sys.fn_my_permissions to check permissions for a user or yourself in a SQL Server instance.

You can see all server level permissions below for the current user.

Next, I will check my permissions in database AdventureWorks2019. We can use the same system function, but I will change the securable class from server to database.

 I am part of the sysadmin fixed server role, so you can see I have all permissions on the server as well as the database.

List All Effective Permissions of Server or Database Level Principals on Various Securable Classes in SQL Server

First, I will create a login and assign some permissions to this login at the database and object level and then see what the function returns.

Run the below command to create a login, create a user in database AdventureWorks2019 and finally assign read permissions to this login in database AdventureWorks2019.

You can see all commands have been executed successfully.

Now, we will run the below T-SQL statement in database AdventureWorks2019 to display all assigned permissions for user test. You can get similar details for any login. To do this, you need to change the execution context to target login before running the command. You can also use EXECUTE AS USER to switch the execution context to the test user.

Now, you can see the output and all effective permissions of the newly created user in the below screen. You can also compare this output with the image above in which I listed all my permissions at the database level. You can see I have all permissions whereas the below output shows only 4 rows for user test which are CONNECT, SELECT and VIEW permissions as we have assigned above.

Here, I will again check permissions for principal (login) test on the securable class server level. You can change the securable class in system function sys.fn_my_permissions from database to server as shown below.

The output shows only 3 rows whereas if you compare this output with image above where I listed all my permissions for securable class server, you can see a clear difference between permissions. Have a look at the output to get all effective permissions for securable class server for principal test.

Now, I will demonstrate one more example to list all permissions for principal (login) test on securable class server by assigning a fixed server role to this login.

You can see I have assigned the dbcreator fixed server role to login test by running the below T-SQL command.

Above running the above command we will run the below for this login to see the changes.

You can see now there are now 4 rows in the output and the additional row for CREATE ANY DATABASE has been added.

Similarly, you can again do more testing to verify the system function output. Let’s add this login to the securityadmin fixed server role and then check permissions.

The command executed successfully.

Below you can see the permissions have been increased and a new permission "ALTER ANY LOGIN" is now in the output.

We can also get all effective permissions for a server or database level principal (login or user) without switching the execution context using the EXECUTE AS command. Using the below commands.

Although the above command will display high level permissions. For example, the test login has ALTER permission, but it’s not showing ALTER on which object or entity.

This user test doesn’t have specific access to database master so the second result set does not show anything.

Now, I have changed the database context to AdventureWorks2019 and then executed same command. You can see user test has displayed the output with their permission levels because this user has access to database AdventureWorks2019.

List All Effective Permissions of a Server or Database Level Principal on a Specific Securable

This section will explain checking permissions for a server or database principal on a specific securable like table, views or stored procedures. Let’s take an example like a user came to you and asked what permissions he has on a table called "Person" in database AdventureWorks2019. You can get this using this system function by running the below T-SQL code. You can see here we have changed the securable class to object in our SQL code.

All permissions for database principal test on securable table person is displayed in the below screen by executing the above T-SQL. You can also see the permission level shows column level information.

You can also check your own permissions on this table by switching the execution context to yourself. You just need to open new query window and run the below command to get the details.

Here is the output.

Similarly, we can check the permissions of database principal test on a system view.

The principal test has only SELECT permission on this system view.

If I run it for my account, you can see I have various level of permission on this system view.

Here, we will check permissions on one more securable for both principals’ test and my own user id.

Run the below T-SQL statements to display all permissions I have on the full text catalog "AW2016FullTextCatalog" in database AdventureWorks2019 and what the test user has.

Here is the output for both principals and you can compare their permissions as well.

This system function is very useful to get effective permissions of any user or login on any SQL Serve entity. This function makes a DBA’s life easier in SQL Server security management.

Read more articles on SQL Server:

• You can also read more articles on SQL Server Management Studio
• Explore more knowledge on SQL Server Database Administration Tips
• Read more SQL Server security articles

About the author

Comments For This Article

Related articles.

• dbForge SQL Complete
• dbForge Studio for SQL Server
• dbForge SQL Tools
• dbForge Studio for MySQL
• Compare Bundle for MySQL
• Schema Compare for MySQL
• dbForge Studio for Oracle
• Compare Bundle for Oracle
• Data Compare for Oracle
• dbForge Studio for PostgreSQL
• Data Compare for PostgreSQL
• Multidatabase Solution
• dotConnect for Oracle
• dotConnect for PostgreSQL
• dotConnect for MySQL
• ODBC Driver for Salesforce
• ODBC Driver for Oracle
• ODBC Driver for MySQL
• SSIS Components for Salesforce
• SSIS Components for MySQL
• SSIS Integration Database Bundle
• Excel Add-in for SQL Server
• Excel Add-in for PostgreSQL
• Excel Add-in Database Pack
• Universal Data Access Components
• Oracle Data Access Components
• SQL Server Data Access Components
• SecureBridge
• Code Compare
• Review Assistant
• Code Review Bundle
• Entity Developer
• LinqConnect
• LINQ Insight
• Time Tracking App
• Data Integration Services
• Cloud to Cloud Backup
• Online SQL Tools
• Web API Server

In the article, we are going to examine how to create a new user account and grant/revoke permissions and roles on a database object, as well as how to check SQL Server user permissions on the database using T-SQL and dbForge Studio for SQL Server.

When it comes to protecting sensitive data and keeping the database environment secure, the management of database permissions and privileges takes center stage. For example, you want a specific person to be able to modify and manipulate personal data, while others can only view this data. In this case, database administrators will need to create a new user or role with specific permissions that can be assigned at the server, database, or schema levels. To implement this task, they can use T-SQL statements provided by SQL Server or third-party tools.

To move on, we are going to dig deeper into the following how-tos:

Create a new user using T-SQL

Create login statement using windows authentication, create a sql server authentication login, create a login from a certificate, create a login from an asymmetric key, grant permissions using t-sql, revoke all privileges using t-sql, deny permissions using t-sql, assign roles in the sql server, create a user in dbforge studio for sql server, assign permissions in dbforge studio for sql server, check user and login permissions in dbforge studio for sql server, create a new login in sql server.

Prior to creating a database user, you should first create a new login based on Windows Authentication, SQL Server authentication, a certificate, or an asymmetric key.

To add a new login, use the CREATE LOGIN statement. It creates a login connected to a SQL Server instance . The login will then be mapped to the specified user account. The syntax is as follows:

Windows Authentication

SQL Server authentication

Certificate

Asymmetric key

The table describes the arguments used in the CREATE LOGIN statements.

To add a SQL Server user based on Windows authentication, run the CREATE LOGIN statement with the following arguments:

This will create a new login JordanS on a SQL Server instance using Windows authentication.

For example, let’s create a login JordanS with the password ‘pass123’ using SQL Server authentication. For this, run the following command:

If you want to change the password at the first login, add the MUST_CHANGE argumen with enabled CHECK_EXPIRATION to the CREATE LOGIN statement:

Now, let’s see how to create a login JordanS using a SQL Server certificate certificate123 .

Finally, add a new login JordanS that uses an asymmetric key key_123 in SQL Server.

After the login was created, it is time to add a new user. The basic syntax is as follows:

To create a new user, for example, Manager , for the login JordanM , execute the CREATE USER statement:

The following system view sys.database_principals allows you to retrieve the list of all users created in the SQL Server database. Run the command to view that the user was created.

The output should be as follows:

After the user is created, we can proceed with assigning permissions and privileges.

Permissions and privileges control the access to SQL Server data and database objects. You can grant user privileges on different database objects in SQL Server.

Privileges can be of two types:

• System privileges that allow users to create, alter or drop database objects .
• Object privileges that allow users to execute, select, insert, update, or delete data on database objects to which the privileges were assigned.

It should be noted that only database administrators or owners of database objects can provide or revoke privileges.

The GRANT statement provides access and permissions on database objects to the user. The basic syntax is as follows:

The table describes the arguments used in the GRANT statements.

For example, let’s grant the SELECT, INSERT, and ALTER privileges on the HumanResources.Employee table to the user Manager that we created in the previous section.

Now, let’s view that the list of permissions on the HumanResources.Employee table includes the permissions we assigned to the user Manager . For this, we will use the system view – fn_my_permissions – and execute the following command:

The output is as follows:

If you want to remove the privileges on the database object from the user, you can use the REVOKE command.

You can replace privileges with the values such as SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.

To move on, remove the INSERT and ALTER permissions on the HumanResources.Employee table assigned to the user Manager by running the following command:

To check the result, execute the following command using the fn_my_permissions view:

As you can see, the INSERT and ALTER permissions were removed. Only the SELECT permission is still assigned.

The DENY permission blocks access to the database object. In case, the object has the GRANT and DENY permissions, the DENY permissions will prevail over the GRANT ones. For example, execute the following command:

In this case, the INSERT permission will be blocked for the Manager user.

What if you need to apply privileges to a group of users rather than to a single user? In this case, it would be better to define the role , a set of privileges and permissions. Thus, a user who is assigned to the role can access and manipulate the database objects with the same permissions as the role has.

It should be noted that to manipulate the role, you should have the ALTER permission on the role, ALTER ANY ROLE permission at a database level, and membership in the  db_securityadmin  fixed database role.

First, create the role with the CREATE ROLE statement:

where role_name is the name of the role you want to create.

For example, we want to create the role Managers :

Since the role does not have any assigned privileges yet, the next step will be to add privileges to the role. For this, the GRANT command should be executed that can assign privileges to roles on databases and database objects.

For example, apply the SELECT, UPDATE, ALTER, INSERT, DELETE privileges on the Person.Address table to the role Managers .

After that, add users to the role by running the ALTER ROLE statement.

It adds or removes users to or from a database role, or can be used to change the name of the role.

The syntax is as follows:

role_name is the name of the role you want to modify or drop.

user_name is the name of the existing user you want to add to the role.

If you want to remove the user from the role, use the ALTER ROLE statement with the following properties:

Note: user_name cannot be a fixed database role or a server principal.

To change the name of the role, run the following statement:

where new_name is the new name of the role. Keep in mind that the name of a fixed database role cannot be modified.

Now, we are going to see how comfortable it is to deal with users, roles, and privileges using a powerful SQL Server administration tool, Security Manager , available in dbForge Studio for SQL Server .

Security Manager is a reliable tool that ensures a secure and efficient way to handle access to database objects and data, to create and manipulate users and roles, as well as to grant and revoke privileges and permissions.

To get started, open Security Manager by using one of the following ways:

• On the Start Page, switch to the Administration tab and click Manager Server Security .
• On the Database menu, select Security Manager .

In the Security Manager, select Create User from the drop-down list. On the General tab, enter the user accounts parameters and click Save .

To grant permissions for the user, switch to the Object Permissions tab. In the Objects block, select the database object on which you want to grant privileges. In the Available Privileges block, select the permissions to be assigned and click Save .

If you need to revoke some changes, select the privilege, click Revoke Selected Privilege , and then click Save . To deny the permission, select the checkbox next to the corresponding privilege.

Alternatively, you can remove all the granted privileges by clicking Revoke All Privileges .

To get a list of object-level permissions assigned to the user, in Security Manager , select the user and switch to the Object Permissions tab. Then, select the object and view the available permissions.

In addition, you can check the list of permissions granted to the login at the server level. For this, in Security Manager , select the login for which you want to view the permissions and switch to the Server Permissions tab. There will be the selected checkbox next to the assigned permissions in the Granted column.

In the article, we have described how to create a SQL Server user account on database objects, and assign or revoke privileges applied to the user using T-SQL and dbForge Studio for SQL Server. As you can see, with Security Manager available in dbForge Studio for SQL Server, it is much faster to perform these tasks with several clicks, thus, saving your time and increasing productivity.

To evaluate other excellent features and capabilities dbForge Studio for SQL Server provides, download a 30-day free trial version of the tool. After it expires, you feel like purchasing a full version of dbForge Studio for SQL Server – no doubt!

• create login
• create user
• database administration
• sql server permissions
• SQL Server Tutorial
• sql statement

Attach SQL Server 2005 .mdf/.ldf Files to 2019 Using dbForge Studio

How to connect to postgresql in delphi with devart pgdac, how to create a pivot table in sql server.

Founded in 1997, Devart is currently one of the leading developers of database management software, ALM solutions and data providers for most popular database servers.

• SQL Server Tools
• MySQL Tools
• Oracle Tools
• PostgreSQL Tools
• Productivity Tools
• Discontinued Products
• ADO.NET Data Providers
• ORM Solutions
• ODBC Drivers
• SSIS Components
• Excel Add-ins
• Delphi Data Access Components
• dbExpress Drivers
• Submit Request
• View Forums
• Documentation Center
• Ordering FAQs
• Discount Programs
• Using Website
• MySQL Tutorial
• Oracle Tutorial
• PostgreSQL Tutorial
• Success Stories

SQL Errors on Install, Logged-in User's Rights/Privileges Required by SQL Server Express

Jun 23, 2021 • knowledge, information.

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Related Articles

• Number of Views 665
• Number of Views 2.04K
• Number of Views 410
• Number of Views 116
• Number of Views 531

Copyright © 2023 Progress Software Corporation and/or its subsidiaries or affiliates. All Rights Reserved.

Progress, Telerik, Ipswitch, Chef, Kemp, Flowmon and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.

Terms of Use | Privacy Center | Trademarks | License Agreements | Careers | Offices