无锡市住房与城乡建设局网站,最火爆的视频制作app,中国建筑集团领导名单,青岛旅游网站建设目录
一、用户管理常见方法
二、用户管理方法示例
2.1. 创建登录账户#xff1a;
2.1.1 检查是否创建账户成功#xff1a;
2.2. 创建数据库用户#xff1a; 2.2.1检查用户是否创建成功#xff1a;
2.3. 授予权限#xff1a;
2.3.1授予 SELECT、INSERT 和 U…目录
一、用户管理常见方法
二、用户管理方法示例
2.1. 创建登录账户
2.1.1 检查是否创建账户成功
2.2. 创建数据库用户 2.2.1检查用户是否创建成功
2.3. 授予权限
2.3.1授予 SELECT、INSERT 和 UPDATE 权限
2.3.1.1检查权限-使用系统函数
切换用户方法
2.3.1.2检查权限--使用系统视图
2.3.1.3. 使用 SQL Server Management Studio (SSMS)
2.3.1.4. 使用视图和查询
2.3.2授予 EXECUTE 权限
2.3.3授予所有权限
2.4. 收回权限
2.4.1撤回特定权限
2.4.2撤回所有权限
2.4.3撤回特定角色中的权限
2.5. 修改用户信息
2.5.1修改用户信息的步骤
2.5.2修改登录名
2.5.3修改密码
2.5.4修改默认数据库
2.5.5修改默认语言
2.5.6修改其他设置
2.6. 删除用户
2.6.1使用 SQL Server Management Studio (SSMS)
2.6.2使用 Transact-SQL 查询
2.7. 禁用用户
2.7.1禁用登录
2.7.2撤销禁用
2.7.3禁用用户在数据库中的操作权限
三、使用SSMS创建用户和账户补充
3.1创建账户
3.2创建用户 一、用户管理常见方法
在 Microsoft SQL Server 中用户管理是数据库管理员的一项重要任务。用户管理涉及到创建、修改、删除用户授予用户权限等操作。以下是一些在 SQL Server 中进行用户管理的常见方法
1.创建登录账户
在 SQL Server 中首先需要创建登录账户这是用户连接数据库的凭证。使用以下 T-SQL 命令创建登录账户
CREATE LOGIN [login_name] WITH PASSWORD password;
2. 创建数据库用户
登录账户只是用来登录 SQL Server要在数据库中创建用户需要将登录账户映射到数据库用户。使用以下 T-SQL 命令创建数据库用户 USE [database_name];CREATE USER [user_name] FOR LOGIN [login_name];
3. 授予权限
要让用户能够执行特定操作需要授予适当的权限。例如使用以下 T-SQL 命令授予 SELECT 权限 GRANT SELECT ON [schema_name].[table_name] TO [user_name];
授予一个数据库用户对特定表的 SELECT 权限。以下是对这条语句的解析
- GRANT: 这是 SQL 关键字表示你要授予权限。- SELECT: 这是权限类型表示你要授予用户对表的 SELECT 权限即允许用户查询表中的数据。- ON [schema_name].[table_name]: 这部分指定了你要授予权限的表使用数据库模式名称和表名称来标识表。[schema_name] 应该替换为实际的数据库模式名称[table_name] 应该替换为实际的表名称。- TO [NewUser]: 这部分指定你要将权限授予的用户即数据库用户。[NewUser] 应该替换为实际的数据库用户名称。
因此含义是将对特定数据库模式中的特定表的 SELECT 权限授予给指定的数据库用户。这将允许该用户能够查询该表中的数据但不会允许进行插入、更新或删除操作。 注意 在实际使用时需要替换方括号中的部分例如[schema_name]、[table_name] 和 [NewUser]为实际使用的名称。另外为了执行这条语句需要拥有足够的权限。 4. 修改用户信息
若要修改用户的属性或权限可以使用 ALTER USER 命令。例如修改用户密码
ALTER LOGIN [login_name] WITH PASSWORD new_password;
5. 删除用户
如果要删除用户首先需要从数据库中删除用户然后再删除登录账户。使用以下 T-SQL 命令删除用户 USE [database_name];DROP USER [user_name]; 然后使用以下 T-SQL 命令删除登录账户 DROP LOGIN [login_name];
6. 禁用用户
有时候不想删除用户而是想暂时禁用他们的访问权限。可以使用 ALTER LOGIN 命令来禁用登录账户
ALTER LOGIN [login_name] DISABLE; 这些是 SQL Server 中基本的用户管理方法。在实际操作中还可能涉及更多高级操作如角色分配、资源限制等。务必在执行这些操作之前备份数据库以防不必要的数据丢失。 二、用户管理方法示例
以下是一些示例操作来创建用户、删除用户以及授予权限的方法 首先要明白账户和用户的区别如下所述 在 SQL Server 中术语“登录账户”Login Account和“数据库用户”Database User是两个相关但不同的概念。它们在数据库系统中扮演不同的角色以下是它们之间的区别 登录账户Login Account - 登录账户是用于连接到 SQL Server 实例的凭证。它可以是 SQL Server 登录账户由 SQL Server 管理或 Windows 登录账户由 Windows 管理。 - 登录账户用于验证用户的身份允许用户访问 SQL Server 实例。 - 登录账户的范围是整个 SQL Server 实例它不限于特定数据库。 - 登录账户的创建和管理通常由系统管理员或具有适当权限的用户执行。 数据库用户Database User - 数据库用户是连接到数据库的凭证。每个登录账户可以在一个或多个数据库中具有一个或多个相关联的数据库用户。 - 数据库用户用于控制用户在特定数据库中的访问权限和操作。 - 数据库用户的范围仅限于特定的数据库。一个登录账户在不同的数据库中可以拥有不同的数据库用户。 - 数据库用户的创建和管理由数据库管理员或具有适当权限的用户执行。 总结来说登录账户是用于连接到 SQL Server 实例的凭证它允许用户访问整个实例而数据库用户是用于连接到数据库的凭证它控制用户在数据库中的访问权限和操作。登录账户是更高层次的概念用于实例级别的访问控制而数据库用户是更具体的概念用于数据库级别的访问控制。 2.1. 创建登录账户
CREATE LOGIN [NewLogin] WITH PASSWORD StrongPassword123;
2.1.1 检查是否创建账户成功
①使用 SQL Server Management Studio (SSMS)
使用 SQL Server Management Studio 连接到 SQL Server 实例可以在“Security”节点下的“Logins”文件夹中查看已创建的登录账户列表。这提供了一个图形化的界面可以轻松地查看和管理登录账户。如下操作找到左侧状态栏【安全性】-【登录名】存在即创建成功 ②使用系统视图
可以查询系统视图 sys.server_principals 来查找已创建的登录账户。这个视图包含了 SQL Server 实例的登录账户信息。
USE master;
SELECT name, type_desc
FROM sys.server_principals
WHERE type IN (S, U, G);在上面的查询中type 列代表不同类型的登录账户其中
S 表示 SQL Server 登录账户U 表示 Windows 用户登录账户G 表示 Windows 组登录账户
上述命令是查询出所有的账户执行命令结果如图所示 也可以如下命令查询
SELECT name, type_desc
FROM sys.server_principals
WHERE type S AND name NewLogin; 输出结果如下 2.2. 创建数据库用户
USE [YourDatabase];
CREATE USER [NewUser] FOR LOGIN [NewLogin]; 2.2.1检查用户是否创建成功
①使用系统视图
可以查询 sys.server_principals 或 sys.database_principals 系统视图来检查登录账户和数据库用户是否成功创建。
USE master;
SELECT name, type_desc
FROM sys.database_principals
WHERE type IN (S, U) AND name NewUser;
②使用 SQL Server Management Studio (SSMS)
通过连接到 SQL Server 实例并在 SSMS 的“对象资源管理器”中导航到“Security”文件夹下的“Logins”或“YourDatabase”文件夹下的“Users”来查看登录账户或数据库用户列表。如果用户成功创建将在列表中看到相应的登录账户或数据库用户。
按照如下操作查看新建用户 注意 刚开始学习的时候我遇到个问题就是SSMS一直找不到自己刚创建好的用户明明数据库中执行命令已经创建成功使用方法一系统视图查看是存在的但是SSMS却查不到用户。经过多方查找才知道原因由于我的数据库待机很久前一天晚上结束后没有退出登录电脑是开机状态一直持续到第二天下午 需要我退出重新登录数据库然后刷新SSMS的用户就找到了刚创建的用户。 如果你在 SQL Server 中创建了一个新的登录账户或数据库用户但无法在相应的地方找到它就考虑以下几个可能的原因和解决方法 1. 刷新对象资源管理器 在 SQL Server Management Studio (SSMS) 中对象资源管理器可能需要手动刷新才能显示最新的更改。右键单击相关的节点例如“Logins”或“Users”然后选择“刷新”选项看看新创建的账户或用户是否出现。 2. 确认使用正确的数据库 确保你在正确的数据库中查看数据库用户。如果你创建了一个数据库用户但切换到了不同的数据库你将无法看到该用户。 3. 检查登录账户和数据库用户的名称 确保你在查询或查看时使用了正确的登录账户或数据库用户的名称。大小写敏感可能会影响查询结果。 4. 重新登录到 SSMS 有时候重新登录到 SQL Server Management Studio 可能会解决一些缓存问题使新创建的账户或用户能够显示。 5. 权限问题 如果你不具备足够的权限可能无法看到特定的登录账户或数据库用户。确保你拥有足够的权限来查看相关信息。 6. 检查创建语句是否成功执行 确保在创建账户或用户时没有遇到任何错误。如果创建语句执行失败账户或用户可能并未成功创建。 7. 系统视图查询 使用系统视图如 sys.server_principals 和 sys.database_principals 来查询登录账户和数据库用户的列表确认是否成功创建。 8. 检查错误日志 如果在创建账户或用户时遇到错误错误日志可能会提供有关发生了什么问题的信息。 如果你仍然无法找到新创建的账户或用户可能需要仔细检查上述因素同时可以在问题解决后尝试重新连接到数据库实例或刷新对象资源管理器。 2.3. 授予权限
2.3.1授予 SELECT、INSERT 和 UPDATE 权限
授予权限给用户以便他们可以在特定表上执行 SELECT INSERT,UPDATE操作
--1.授予select权限
USE master;
GRANT SELECT ON dbo.banji TO [NewUser];--2.授予 INSERT 和 UPDATE 权限
USE master;
GRANT INSERT, UPDATE ON dbo.banji TO [NewUser];
检查是否权限授予成功有以下方法
2.3.1.1检查权限-使用系统函数
SQL Server 提供了系统函数 fn_my_permissions该函数可以用于检查当前用户在指定对象上的权限。以下是一个示例
SELECT *
FROM fn_my_permissions(schema_name.table_name, OBJECT);使用系统函数 fn_my_permissions 来检查当前用户对指定对象的权限。以下是对这段代码的解析 - SELECT *: 这是 SQL 查询的一部分它指示从结果集中选择所有列。- FROM fn_my_permissions(schema_name.table_name, OBJECT): 这部分指定了要查询的函数和参数。fn_my_permissions 函数返回当前用户在指定对象上的权限信息。 - schema_name.table_name: 这是一个字符串参数用于指定对象的完全限定名称即包括数据库模式和表名称。你需要将它替换为实际的数据库模式和表名称。 - OBJECT: 这是 fn_my_permissions 函数的第二个参数指示你希望检查的是一个对象的权限。可以使用 OBJECT、DATABASE 或 SERVER。 因此这个查询的目的是获取当前用户对指定表的权限。执行查询后结果集将包含关于当前用户在该表上的权限信息如 SELECT、INSERT、UPDATE、DELETE 等。 要使用这个查询将 schema_name.table_name 替换为你要查询的具体表的模式和名称。查询的结果将显示当前用户在该表上的权限列表。 切换用户方法
执行该命令查看是否权限授予成功首先需要切换到该用户。切换用户的方法有多种我在这里只列举一种比如
使用 EXECUTE AS 语句EXECUTE AS 语句允许你切换到另一个用户的身份以执行特定的操作。以下是一个示例
EXECUTE AS USER username;
-- 在此处执行需要切换用户身份的操作
REVERT;在上述代码中将 username 替换为要切换到的用户名。在 REVERT 语句之后就可以返回到之前的身份。
然后对应于我需要检查的用户
--用户切换
EXECUTE AS USER NewUser;
-- 在此处执行需要切换用户身份的操作
REVERT; 然后再执行 如下命令
SELECT *
FROM fn_my_permissions(dbo.banji, OBJECT);
输出结果为 注意 只有切换用户后才能注意到授予权限否则将会看到很多内容导致自己无法判定是否授予成功。就比如下图所示 列举其他几种切换用户的方法可自行尝试
**使用连接字符串** 如果是使用连接字符串连接到数据库可以在连接字符串中指定用户名和密码来登录不同的用户。让你以指定用户身份连接到数据库。 Data SourceServerName;Initial CatalogDatabaseName;User IDUsername;PasswordPassword; 将上面的 ServerName、DatabaseName、Username 和 Password 替换为实际的值。
**使用 SQL Server Management Studio (SSMS)** 在 SSMS 中可以在连接到数据库时使用不同的凭据。在连接窗口中的“连接到服务器”部分选择“使用 SQL Server 验证”并提供用户名和密码。这将使用指定的凭据连接到数据库。
**使用 Windows 身份** 如果是使用 Windows 身份连接到数据库可以在 Windows 账户之间切换。在 Windows 中注销并以不同的 Windows 用户登录然后使用 Windows 身份连接到 SQL Server。 特别注意 无论使用哪种方法切换用户的能力取决于你拥有的权限。确保在切换用户身份时遵循安全最佳实践并只在必要的情况下执行。同时要小心在生产环境中切换用户以避免出现意外问题。 2.3.1.2检查权限--使用系统视图
查询 sys.database_permissions 系统视图可以查看当前用户在数据库中的权限。以下是一个示例 SELECT *FROM sys.database_permissionsWHERE grantee_principal_id DATABASE_PRINCIPAL_ID(); 这个查询会显示当前用户被授予的数据库权限。
2.3.1.3. 使用 SQL Server Management Studio (SSMS)
如果你使用 SQL Server Management Studio 连接到数据库你可以右键单击对象例如表、视图、存储过程等然后选择“属性”或“权限”选项从而查看当前用户对该对象的权限。
2.3.1.4. 使用视图和查询
可以查询系统视图如 sys.fn_my_permissions 或 sys.database_permissions结合其他相关的系统视图来获取更详细的权限信息。 特别注意: 权限检查的能力取决于你所使用的权限。如果你是数据库管理员或拥有足够权限的用户你将能够查看其他用户的权限。如果你是普通用户你只能查看自己的权限。确保你在进行任何权限查询时遵循安全最佳实践并只查看你有权查看的信息。 2.3.2授予 EXECUTE 权限 这个示例将授予用户 Alex 对数据库中的存储过程 sp_GetProductInfo 的 EXECUTE 权限允许他执行该存储过程。
USE YourDatabase;GRANT EXECUTE ON dbo.sp_GetProductInfo TO Alex;
2.3.3授予所有权限
这个示例将授予用户 Admin 对数据库中的表 Products 的所有权限包括 SELECT、INSERT、UPDATE 和 DELETE。
USE YourDatabase;GRANT ALL PRIVILEGES ON dbo.Products TO Admin; 注意 权限的授予是一个敏感的操作应谨慎进行。确保只为需要的用户授予必要的权限以确保数据库的安全性。权限的授予需要有足够的权限通常由具有系统管理员或数据库管理员权限的用户执行。在实际操作中你需要将示例中的数据库名称、表名称、用户名称等替换为实际的值。 2.4. 收回权限
要撤回用户的权限可以使用 REVOKE 语句。以下是撤回用户权限的几种常见方法
2.4.1撤回特定权限
使用以下语法可以撤回特定权限
REVOKE permission_typeON object_name TO user_name; 其中permission_type 是要撤回的权限类型如 SELECT、INSERT、UPDATE 等object_name 是权限所在的对象如表、视图、存储过程等user_name 是要撤回权限的用户。 示例
--收回权限
REVOKE insert
ON dbo.banji TO NewUser;
2.4.2撤回所有权限
如果要撤回用户在一个对象上的所有权限可以使用以下语句
REVOKE ALL PRIVILEGESON object_name TO user_name; 示例 REVOKE ALL PRIVILEGES ON dbo.Table1 TO John; 这会撤回用户在指定对象上的所有权限。
2.4.3撤回特定角色中的权限
如果你的用户是通过角色分配的权限你可以从角色中撤回权限。首先需要从角色中撤回权限然后角色中的用户也会丧失相应的权限。 示例
REVOKE SELECT ON dbo.Table1 FROM RoleName;
撤回角色 RoleName 在表 dbo.Table1 上的 SELECT 权限。 注意 执行 REVOKE 操作需要有足够的权限。通常只有拥有相应权限的用户如数据库管理员、系统管理员才能执行这些操作。在执行权限撤回操作之前请确保你理解其影响并谨慎操作以防止意外的数据访问问题。 2.5. 修改用户信息
在 SQL Server 中可以修改用户的多个信息包括登录名、密码、默认数据库、默认语言等。以下是修改用户信息的一般步骤
2.5.1修改用户信息的步骤
1. 打开 SQL Server Management Studio (SSMS) 或使用 Transact-SQL。2. 在 SSMS 中展开 Security 节点然后选择 Logins。3. 找到要修改的登录并右键单击该登录。4. 选择 Properties。5. 在相应的选项卡中进行所需的更改。6. 确认更改并应用。
以下是常见的用户信息修改操作
2.5.2修改登录名
①在 General 选项卡中可以更改登录名。
②使用 Transact-SQL 操作
ALTER LOGIN [old_login_name] WITH NAME [new_login_name];
2.5.3修改密码
①在 General 选项卡中可以更改密码。
②使用 Transact-SQL 操作
ALTER LOGIN [login_name] WITH PASSWORD new_password;
比如
ALTER LOGIN [NewLogin] WITH PASSWORD NewStrongPassword456;
2.5.4修改默认数据库
①在 General 选项卡中的 Default database 部分可以选择新的默认数据库。
②使用 Transact-SQL 操作
ALTER LOGIN [login_name] WITH DEFAULT_DATABASE [new_default_database];
2.5.5修改默认语言
①在 General 选项卡中的 Default language 部分可以选择新的默认语言。
②使用 Transact-SQL 操作
ALTER LOGIN [login_name] WITH DEFAULT_LANGUAGE [new_default_language];
2.5.6修改其他设置
根据需要还可以在不同的选项卡中修改其他设置如服务器角色、数据库角色等。 上述示例只是一些常见的修改操作具体情况可能因需求而异。在执行这些操作之前请确保理解其影响并进行适当的备份因为一些修改可能会导致用户失去对特定资源的访问权限。 2.6. 删除用户
在 SQL Server 中可以使用不同的方法来删除用户。以下是删除用户的两种常见方法以及相应的操作步骤
2.6.1使用 SQL Server Management Studio (SSMS)
1. 打开 SQL Server Management Studio (SSMS) 并连接到相应的 SQL Server 实例。2. 在 Object Explorer 中展开目标数据库然后选择 Security 节点。3. 在 Security 节点下选择 Users。4. 找到要删除的用户右键单击该用户。5. 选择 Delete删除。6. 系统将提示您确认删除操作确认后用户将被删除。
2.6.2使用 Transact-SQL 查询
1. 打开 SQL Server Management Studio (SSMS) 并连接到相应的 SQL Server 实例。2. 打开一个新的查询窗口。3. 在查询窗口中使用以下 Transact-SQL 查询来删除用户
首先从数据库中删除用户
USE [YourDatabase]; -- 切换到目标数据库
DROP USER [NewUser];
然后从登录账户中删除用户
DROP LOGIN [NewLogin]; 在执行删除操作之前请务必谨慎操作并确保已经备份了相关数据。删除用户将同时删除其在数据库中的权限和资源可能会影响到其他相关对象。 无论哪种方法删除用户都需要谨慎处理因为一旦删除用户的数据和权限将不可恢复。在进行删除操作之前请确保您已经了解其影响并已经进行了充分的测试和备份。 2.7. 禁用用户
禁用用户是指限制该用户对数据库的访问和操作权限从而防止其登录和执行特定操作。禁用用户的操作可以通过以下步骤完成
2.7.1禁用登录
1. 打开 SQL Server Management Studio (SSMS) 或使用 Transact-SQL。2. 在 SSMS 中展开 Security 节点然后选择 Logins。3. 找到要禁用的登录并右键单击该登录。4. 选择 Properties。5. 在 General 选项卡中找到 Login: 部分选择 Disabled 选项。或者使用 Transact-SQL 查询
ALTER LOGIN [login_name] DISABLE;
2.7.2撤销禁用
1. 若要撤销禁用可以重复上述步骤但在第 5 步中选择 Enabled 选项。2. 或者使用 Transact-SQL 查询
ALTER LOGIN [login_name] ENABLE; 注意 禁用用户只会影响其登录权限不会直接影响其数据库内的权限。如果需要限制用户在数据库中的操作权限还需要进行以下操作 2.7.3禁用用户在数据库中的操作权限
1. 在 SSMS 中展开目标数据库选择 Security 节点然后选择 Users。2. 找到要禁用的用户并右键单击该用户。3. 选择 Properties。4. 在 General 选项卡中取消勾选 Login name 下方的复选框这将阻止用户登录到数据库。5. 在 Membership 选项卡中取消用户与角色的关联以撤销用户在数据库中的特定权限。
或者使用 Transact-SQL 查询
USE [database_name];
ALTER USER [user_name] DISABLE;
要重新启用用户的操作权限使用以下 Transact-SQL 查询
USE [database_name];
ALTER USER [user_name] ENABLE; 总之禁用用户的操作包括禁用登录和限制数据库中的操作权限。具体步骤可能因 SQL Server 版本而异但通常遵循类似的基本原则。在执行这些操作之前请确保理解其影响并进行适当的备份。 三、使用SSMS创建用户和账户补充
3.1创建账户
首先登录数据库一般选择第二个登录原因是为了方便移植连接 右键登录名点击新建登录名选择第二个输入用户名和密码关闭强制密码过期选择可以登录的数据库最后点击确定比如创建账户【张三】如下所示 然后登录刚设置的新账户查看登录名 如果在此账号再次创建用户会失败因为普通用户权限不足基本都是以系统用户进行创建 。如图所示: 3.2创建用户
登录数据库右键登录名选择【新建登录名】 输入登录名和密码关掉强制密码过期然后点击确定创建成功 切换用户登录张三后再次创建用户发现创建失败原因是普通用户没有权限 本节SQL sever用户管理的笔记和学习理解已经 整理完毕其中内容如有错误劳请各位读者指出然后我及时修改。感谢大家的查看我会继续更新后续SQL sever学习总结
