Summary of several methods for setting SQL Server permissions

1. Basic concepts of permission setting

The permission system of SQL Server mainly includes the following levels:

Database user (User): The login name is mapped to the specific user (database level) in the database.

Role: A collection of permissions used to simplify user management.

Permission types: including connection, query, modify, create objects, execute stored procedures, etc.

2. Create a login name (Login)

The login name is used to authorize the user to connect to the SQL Server instance. There are two common ways:

1. Use SQL Server authentication method

CREATE LOGIN user1 WITH PASSWORD = 'StrongPassword123!';

2. Use Windows authentication method

CREATE LOGIN [DOMAIN\user1] FROM WINDOWS;

3. Create a database user (User)

After the login name is created, the corresponding user needs to be created in the target database:

USE YourDatabase;
CREATE USER user1 FOR LOGIN user1;

4. Assign role permissions (recommended practices)

Using built-in role management permissions for databases is a safer and more standardized way. Common roles include:

db_owner: Have all permissions in the database;

db_datareader: All data can be read;

db_datawriter: can write to all tables;

db_ddladmin: can create and modify tables/views and other object structures;

db_executor: Executable stored procedure (need to be created manually).

Example: Grant read permissions

EXEC sp_addrolemember 'db_datareader', 'user1';

Custom roles (such as db_executor)

-- Create a role
CREATE ROLE db_executor;
-- Grant execution permissions
GRANT EXECUTE TO db_executor;
-- Add users to roles
EXEC sp_addrolemember 'db_executor', 'user1';

5. Fine-grained permission control (object level)

If you need to control access rights to individual tables, views, stored procedures, etc., you can use the GRANT, DENY, REVOKE statement:

Example: Grant query permissions

GRANT SELECT ON  TO user1;

Example: Disable table data deletion

DENY DELETE ON  TO user1;

6. Check the permission status

1. Check that a user has authorized permissions

EXEC sp_helprotect @username = 'user1';

2. View the role member list

EXEC sp_helpuser;

7. Best Practice Suggestions

Avoid adding users directly to sysadmin or db_owner, with too much permission;

Use roles to unify management permissions for easy auditing and maintenance;

Create independent login and database users for each system/service;

Regularly audit user permissions and delete useless users;

Try to avoid using sa accounts, especially in production environments;

This is the end of this article about several methods for setting SQL Server permissions. For more related content on SQL Server permissions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!