SQLSERVER创建用户并授权

# SQL SERVER creates a user and authorizes

## 授权用户连接数据库

```sql
GRANT CONNECT TO YourUserName;
```

## 数据库对象的基本操作权限

- SELECT: 允许用户读取数据
- INSERT: 允许用户插入新数据
- UPDATE: 允许用户更新现有数据
- DELETE: 允许用户删除数据

```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.YourTable TO YourUserName;
```

## 执行存储过程的权限

- 如果用户需要调用存储过程，还需要授予执行权限

```sql
GRANT EXECUTE ON dbo.YourStoredProcedure TO YourUserName;
```

## 授权角色

- db_datareader 允许用户读取所有表的数据

```sql
ALTER ROLE db_datareader ADD MEMBER YourUserName;
```

- db_datawriter 允许用户插入、更新和删除所有表的数据

```sql
ALTER ROLE db_datawriter ADD MEMBER YourUserName;
```

- db_executor 允许用户执行所有存储过程。这个角色需要手动创建，因为 SQL Server 没有内置的 db_executor 角色

```sql
CREATE ROLE db_executor;
GRANT EXECUTE TO db_executor;
ALTER ROLE db_executor ADD MEMBER YourUserName;
```

## 示例脚本

```sql
-- 创建登录名（使用SQL Server身份验证）
CREATE LOGIN TestLogin WITH PASSWORD = 'SecurePassword!';

-- 创建数据库用户并映射到登录名
USE YourDatabaseName;
CREATE USER TestUser FOR LOGIN TestLogin;

-- 授予连接数据库的权限
GRANT CONNECT TO TestUser;

-- 授予数据库读取和写入权限
ALTER ROLE db_datareader ADD MEMBER TestUser;
ALTER ROLE db_datawriter ADD MEMBER TestUser;

-- 创建和授予执行存储过程的权限
CREATE ROLE db_executor;
GRANT EXECUTE TO db_executor;
ALTER ROLE db_executor ADD MEMBER TestUser;

-- 如果需要对特定表或存储过程授予权限
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.YourTable TO TestUser;
GRANT EXECUTE ON dbo.YourStoredProcedure TO TestUser;

```