非DB Owner时应用用户的权限配置及对象映射方法咨询

1. What Permissions Should the App User Have Instead of db_owner? #

Sticking to the principle of least privilege (critical for security), the core permissions your app user needs are:

• db_datareader: Grants read access to all user tables, views, and functions in the database.
• db_datawriter: Grants insert, update, and delete access to all user tables.

Depending on your app’s specific functionality, you might need to add targeted additional permissions:

• If your app relies on stored procedures, grant EXECUTE permission on those specific procedures (avoid global EXECUTE access unless absolutely necessary).
• If your app creates temporary tables, grant CREATE TABLE permission in tempdb (this is often allowed by default for most logins, but worth verifying).
• If your app needs to modify schema (rare for a standard app user), assign granular permissions like ALTER on specific tables—never grant broad schema access unless unavoidable.

The key takeaway: db_owner gives full, unrestricted control over the database, which is a massive security risk if app credentials are compromised. Stick to minimal, purpose-built permissions.

2. How to Map the User to Database Objects, and Can They Do This Themselves? #

First, critical clarification: Mapping a user to a database (and assigning roles/permissions) requires administrative-level access—your app user (with only db_datareader/db_datawriter) cannot perform these actions themselves. This task must be done by a DBA or someone with elevated permissions (like the securityadmin server role or db_owner on the target database).

Here’s a step-by-step breakdown with example SQL scripts:

Step 1: Create a Server Login (if it doesn’t exist)

First, create a login at the SQL Server level:

CREATE LOGIN [AppUser_Login] 
WITH PASSWORD = 'YourSecurePassword123!', -- Use a strong, unique password
CHECK_EXPIRATION = ON, -- Enforce password expiration policies
CHECK_POLICY = ON; -- Enforce Windows-style password rules

Step 2: Map the Login to a Database User

Switch to your target database and create a user linked to the server login:

USE [YourTargetDatabase];
GO

CREATE USER [AppUser] 
FOR LOGIN [AppUser_Login];

Step 3: Assign Core Roles

Grant the db_datareader and db_datawriter roles to the database user:

ALTER ROLE db_datareader ADD MEMBER [AppUser];
ALTER ROLE db_datawriter ADD MEMBER [AppUser];
GO

Step 4: Add Targeted Permissions (If Needed)

For example, if your app needs to execute a specific stored procedure:

GRANT EXECUTE ON OBJECT::[dbo].[GetUserOrders] TO [AppUser];
GO

If you need to grant execute access to all stored procedures in a schema:

GRANT EXECUTE ON SCHEMA::[dbo] TO [AppUser];
GO

Quick Notes #

• Always test the app with these permissions to confirm it can perform all required actions without extra access.
• Never let app users manage permissions or user mappings—this should be restricted to trusted admins only.

内容的提问来源于stack exchange，提问作者BuRaiR