在Sqlserver中创建用户并为其授权是数据库管理员必须掌握的基本操作之一。以下是创建用户并授权的详细步骤。
1. 创建登录用户
首先,需要使用Sqlserver账号登录数据库。然后,在“Security”文件夹下的“Logins”文件夹上单击鼠标右键,选择“New Login...”选项,或者直接在“Logins”文件夹上双击打开“New Login”对话框。在“General”选项卡上,输入登录名称,选择“SQL Server authentication”身份验证方式,输入密码并确认密码,选择“default database”(默认为master),勾选“Enforce password policy”和“Enforce password expiration”,最后单击“OK”保存修改。
CREATE LOGIN 用户名
WITH PASSWORD = '用户密码',
CHECK_POLICY = ON, -- 启用密码策略验证
CHECK_EXPIRATION = ON; -- 密码过期时需要禁用
1.1 PASSWORD 强度不符的错误
如果密码复杂度不符,Sqlserver会提示错误:
Msg 15138, Level 16, State 1, Line 1
The password does not meet the requirements of the password policy. Check the password policy requirements and try again.
若想跳过检查密码策略,则可以在密码后面加上以下语句:
MUST_CHANGE = OFF, -- 禁止强制要求下次启动更改密码
LOGIN = 用户名;
2. 创建用户并分配登录用户
接着,在要分配访问权限的数据库上单击鼠标右键,选择“New User...”选项,或在“Security”文件夹下的“Users”文件夹上单击鼠标右键,选择“New User”选项。在“General”选项卡上,输入用户名称、选择登录名称(即第一步创建的登录用户)、选择“default schema”(默认为dbo)、选择数据库角色,并单击“OK”保存修改。
CREATE USER 用户名
FOR LOGIN 登录名 WITH DEFAULT_SCHEMA = dbo;
-- 添加用户至 db_owner 角色,即数据库拥有者
EXEC sp_addrolemember 'db_owner', '用户名';
2.1 没有 ALTER ANY LOGIN 的错误
如果执行创建用户时,出现下面的错误:
Msg 3969, Level 16, State 1, Line 1
Cannot ALTER LOGIN because the SID of the binary of marriage does not match the specified SID.....
可以尝试将系统默认的 dbcreator 角色权限:ALTER ANY LOGIN 的钩子勾上。
3. 分配用户对象的权限
为了将用户与数据库的对象(如表、视图、存储过程等)关联起来,还需要为该用户分配对所需对象的权限。可以使用GRANT命令实现。
-- 授予 SELECT 权限
GRANT SELECT ON 某个表 TO 用户名;
GRANT SELECT ON 某个表 TO 角色名;
-- 授予 UPDATE/INSERT/DELETE 权限
GRANT UPDATE, INSERT, DELETE ON 某个表 TO 用户名;
GRANT UPDATE, INSERT, DELETE ON 某个表 TO 角色名;
3.1 没有 GRANT 权限的错误
如果执行GRANT命令时,出现下面的错误:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object '某个表', because it does not exist or you do not have permission.
这说明当前用户没有操作该表的权限,需要将该权限授予当前用户或角色。
4. 撤销用户权限
如果需要撤销用户的权限,可以使用REVOKE命令。例如,要撤销用户的SELECT权限,可以使用以下命令:
-- 撤销 SELECT 权限
REVOKE SELECT ON 某个表 FROM 用户名;
REVOKE SELECT ON 某个表 FROM 角色名;
总结
创建用户并分配权限是数据库管理员必须掌握的基本操作之一。通过上述步骤,可以在Sqlserver中创建用户、登录用户,并授予其所需的权限。注意,为了保证数据库的安全性,需要谨慎分配权限,避免给予用户过多权限,或意外暴露敏感信息。