SQL Server 创建新用户并设置权限

创建登录名和用户

-- 创建服务器登录名
USE master;
GO
CREATE LOGIN [User1] WITH PASSWORD = 'User1123456';
GO
-- 在目标数据库中创建用户并将当前用户赋予给刚刚创建的登录名(目标数据用户名可以不与登录名一致)
USE [U9];
GO
CREATE USER [User1] FOR LOGIN [User1];
GO

角色权限管理(表级别权限)

创建角色

CREATE ROLE [DBFind];
GO

将用户添加到角色

EXEC sp_addrolemember 'DBFind', 'User1';
GO

授予角色对用户表的操作权限

DECLARE @sql NVARCHAR(MAX) = '';
SELECT
    -- 赋权查询权限语句
    @sql = CONCAT(@sql, N'GRANT SELECT ON ', QUOTENAME(SCHEMA_NAME(SCHEMA_ID)), N'.',QUOTENAME(name), N' TO [DBFind]; ')
    -- 拒绝所有权限语句
    -- @sql = CONCAT(@sql, N'DENY SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ', QUOTENAME(SCHEMA_NAME(SCHEMA_ID)), N'.',QUOTENAME(name), N' TO [DBFind]; ')

FROM
    sys.tables -- 系统表
WHERE
    name IN ('Base_User','Base_User_Trl') -- 要授权的表名
    AND type = 'U'; -- 表类型
-- 执行授权语句
EXEC sp_executesql @sql;
GO

验证权限设置

查看用户权限

EXEC sp_helprotect NULL, 'User1';
GO

查看用户的表权限

SELECT 
    perm.permission_name AS 赋予的权限,
    perm.state_desc AS 权限状态,
    obj.name AS 表名,
    sch.name AS 架构
FROM sys.database_permissions perm
    JOIN sys.objects obj ON perm.major_id = obj.object_id
    JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
    JOIN sys.database_principals prin ON perm.grantee_principal_id = prin.principal_id
WHERE
    prin.name = 'User1';
GO

查看角色的表权限

SELECT 
    r.name AS 角色,
    o.name AS 表名,
    o.type_desc AS 表类型,
    permission_name AS 赋予的权限,
    state_desc AS 权限状态
FROM
    sys.database_permissions dp
    INNER JOIN sys.database_principals r ON dp.grantee_principal_id = r.principal_id
    LEFT JOIN sys.objects o ON dp.major_id = o.object_id
WHERE
    r.name = 'DBFind' -- 角色名
ORDER BY
    o.name,
    permission_name;

注意事项:

1. 执行这些操作需要足够的权限（通常是sysadmin或db_owner）
2. ALTER TRACE是一个强大的权限，应谨慎授予