在 SQL Server 中，Login 和 User 是两个不同层次的安全主体，它们的职责和应用范围不同，因此权限的授予方式也不同。

1. Login 与 User 的区别

• Login（登录）：

• 作用范围：Login 是服务器级别的安全主体，用于管理对 SQL Server 实例的访问权限。
• 作用对象：Login 负责身份验证（如通过用户名和密码登录）和管理服务器级别的权限，如管理数据库、服务器配置、SQL Server Agent 等。
• 示例：授予 ALTER ANY LOGIN 或 VIEW SERVER STATE 等服务器权限。

• User（用户）：

• 作用范围：User 是数据库级别的安全主体，用于管理对特定数据库的访问和操作权限。
• 作用对象：User 负责管理数据库中的权限，如访问特定的表、视图、存储过程，以及执行查询、更新等操作。
• 示例：授予 SELECT、INSERT、UPDATE 等数据操作权限。

2. 权限授予的层次结构

• 服务器级别权限：

• 服务器级别的操作需要较高的权限，例如创建数据库、备份数据库、配置服务器设置等。这些权限是通过 Login 授予的，因为 Login 是连接到 SQL Server 实例的入口点。
• 例子：GRANT ALTER ANY DATABASE TO [YourLogin];

• 数据库级别权限：

• 数据库级别的操作涉及到具体数据库内的对象，如表、视图、存储过程等。为了更精细地控制权限，SQL Server 使用 User 来管理这些权限，因为 User 仅在其所在的数据库内有效。
• 例子：GRANT SELECT ON [dbo].[YourTable] TO [YourUser];

3. Login 和 User 之间的关系

• 映射关系：每个 Login 可以在多个数据库中映射为一个 User。当一个 Login 连接到 SQL Server 并访问特定数据库时，SQL Server 会检查该 Login 在该数据库中的 User 角色，并依据该 User 的权限执行操作。
• 独立管理：Login 管理的是对 SQL Server 实例的整体访问，而 User 管理的是对数据库对象的访问权限。这样可以更灵活地配置安全策略。

4. 举例说明

• Login 权限：你可能希望某个 Login 能够创建新的数据库，但不允许它直接访问数据库中的数据。因此，你会为该 Login 授予服务器级别的 CREATE DATABASE 权限，而不会直接授予数据库级别的 SELECT 权限。
• User 权限：相应地，当 Login 创建了一个数据库后，你可能希望它能够访问该数据库内的某些表，但只能读取数据而不能修改。因此，你会为其在特定数据库内映射的 User 授予 SELECT 权限。

5. SQL举例

-- 查询所有login
SELECT 
    name AS LoginName,
    type_desc AS LoginType,
    create_date AS CreatedDate,
    modify_date AS ModifiedDate
FROM 
    sys.server_principals
WHERE 
    type IN ('S', 'U', 'G')  -- 'S': SQL Login, 'U': Windows Login, 'G': Windows Group
    AND name NOT LIKE '##%' -- Exclude system logins
ORDER BY 
    name;

-- 查询所有user
USE [YourDatabaseName]; -- Replace with your database name

SELECT 
    name AS UserName,
    type_desc AS UserType,
    create_date AS CreatedDate,
    modify_date AS ModifiedDate
FROM 
    sys.database_principals
WHERE 
    type IN ('S', 'U', 'G', 'A')  -- 'S': SQL User, 'U': Windows User, 'G': Windows Group, 'A': Application Role
    AND name NOT LIKE 'db_%' -- Exclude system users
ORDER BY 
    name;

-- 查询login与user的绑定关系
USE [YourDatabaseName]; -- Replace with your database name

SELECT 
    sp.name AS LoginName,
    dp.name AS UserName
FROM 
    sys.database_principals dp
INNER JOIN 
    sys.server_principals sp
    ON dp.sid = sp.sid
WHERE 
    dp.type IN ('S', 'U', 'G')  -- 'S': SQL User, 'U': Windows User, 'G': Windows Group
ORDER BY 
    LoginName, UserName;

-- 创建login
USE [master]
GO
CREATE LOGIN [aaa] WITH PASSWORD=N'aaaaaa', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- 创建用户 （在具体库下创建用户并映射到某个login）
USE [test4]
GO
CREATE USER [aaa] FOR LOGIN [user1]
GO

总结

服务器级别的权限是授予 Login 的，因为它们涉及到对整个 SQL Server 实例的控制和访问，而数据库级别的权限是授予 User 的，因为它们涉及到对特定数据库对象的访问和操作。这种分层的权限管理机制有助于更细致地控制访问权限，提高系统的安全性。