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 login in SQL Server
- Create a new user using T-SQL
- 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
CREATE LOGIN login_name
FROM WINDOWS
[ WITH DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name ];SQL Server authentication
CREATE LOGIN login_name
WITH PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , SID = sid_value
| DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name
| CHECK_EXPIRATION = { ON | OFF }
| CHECK_POLICY = { ON | OFF }
| CREDENTIAL = credential_name ];Certificate
CREATE LOGIN login_name
FROM CERTIFICATE certificate_name;Asymmetric key
CREATE LOGIN login_name
FROM ASYMMETRIC KEY asym_key_name;The table describes the arguments used in the CREATE LOGIN statements.
| Argument | Description |
| login_name | Name of the login connected to the server. |
| database_name | Name of the default database to which the login will be assigned. |
| language_name | Default language for the login you create. |
| password | Password for the login you create. |
| hashed_password | Hashed value of the password for the login you create. |
| MUST_CHANGE | Prompts to change the password upon the connection. |
| sid_value | Value used to recreate a login. It can be used only for logins with SQL Server authentication. If sid_value is not set, SQL Server will assign a new SID. |
| CHECK_EXPIRATION | Defines whether the password expiration policy is applied. It should be set to ON if you use the MUST_CHANGE option. |
| CHECK_POLICY | When the argument is set to ON, it indicates that Windows password policy of the computer on which SQL Server is running should be applied to the login as well. |
| credential_name | Name of the credential to be assigned to the SQL Server login. |
| certificate_name | Name of the certificate to be mapped to the SQL Server login. |
| asym_key_name | Name of the asymmetric key to be mapped to the SQL Server login. |
CREATE LOGIN statement using Windows Authentication
To add a SQL Server user based on Windows authentication, run the CREATE LOGIN statement with the following arguments:
CREATE LOGIN JordanS
FROM WINDOWS;This will create a new login JordanS on a SQL Server instance using Windows authentication.
Create a SQL Server Authentication login
For example, let’s create a login JordanS with the password ‘pass123’ using SQL Server authentication. For this, run the following command:
CREATE LOGIN JordanS
WITH PASSWORD = 'pass123';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:
CREATE LOGIN JordanS
WITH PASSWORD = 'pass123' MUST_CHANGE,
CHECK_EXPIRATION = ON;Create a login from a certificate
Now, let’s see how to create a login JordanS using a SQL Server certificate certificate123.
CREATE LOGIN JordanS
FROM CERTIFICATE certificate123;Create a login from an asymmetric key
Finally, add a new login JordanS that uses an asymmetric key key_123 in SQL Server.
CREATE LOGIN JordanS
FROM ASYMMETRIC KEY key_123;Create a new user using T-SQL
After the login was created, it is time to add a new user. The basic syntax is as follows:
CREATE USER <username> for login <login_name>To create a new user, for example, Manager, for the login JordanM, execute the CREATE USER statement:
CREATE USER Manager FOR LOGIN JordanM;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.
SELECT *
FROM AdventureWorks2019.sys.database_principals;The output should be as follows:
After the user is created, we can proceed with assigning permissions and privileges.
Grant permissions using T-SQL
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:
GRANT privileges
ON database_name.object
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION]; The table describes the arguments used in the GRANT statements.
| Argument | Description |
| privileges | Permissions you want to grant. They include the SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL statements. |
| database_name | Name of the database to which the database object belongs. |
| object | Database object on which the specified privileges will be assigned. |
| user_name | Name of the user whom the privileges will be granted. |
| PUBLIC | Used to grant permissions to all users. |
| role_name | Set of privileges grouped in one category. |
| WITH GRANT OPTION | Used to grant permissions to other users. |
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.
GRANT SELECT, INSERT, ALTER ON HumanResources.Employee TO Manager;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:
EXECUTE AS USER = 'Manager';
GO
USE AdventureWorks2019
GO
SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
GOThe output is as follows:
Revoke all privileges using T-SQL
If you want to remove the privileges on the database object from the user, you can use the REVOKE command.
REVOKE privileges
ON object
FROM {user_name |PUBLIC |role_name}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:
REVOKE INSERT, ALTER ON HumanResources.Employee TO Manager;To check the result, execute the following command using the fn_my_permissions view:
EXECUTE AS USER = 'Manager';
GO
USE AdventureWorks2019
GO
SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
GOAs you can see, the INSERT and ALTER permissions were removed. Only the SELECT permission is still assigned.
Deny permissions using T-SQL
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:
DENY INSERT ON HumanResources.Employee TO Manager;In this case, the INSERT permission will be blocked for the Manager user.
Assign roles in the SQL Server
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:
CREATE ROLE role_name;where role_name is the name of the role you want to create.
For example, we want to create the role Managers:
CREATE 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.
GRANT SELECT, UPDATE, ALTER, INSERT, DELETE ON Person.Address TO 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:
ALTER ROLE role_name
ADD MEMBER user_name;where
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:
ALTER ROLE role_name DROP MEMBER user_name;Note: user_name cannot be a fixed database role or a server principal.
To change the name of the role, run the following statement:
ALTER ROLE role_name WITH NAME = new_name;where new_name is the new name of the role. Keep in mind that the name of a fixed database role cannot be modified.
Create a user in dbForge Studio for SQL Server
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.
Assign permissions in dbForge Studio for SQL Server
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.
Check user and login permissions in dbForge Studio for SQL Server
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.
Conclusion
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!
