SRE文档

深浅模式

1. 创建超级管理员 ​

sql
USE [master]
GO
CREATE LOGIN [admin] WITH PASSWORD=N'admin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [admin]
GO
USE [master]
GO
CREATE LOGIN [admin] WITH PASSWORD=N'admin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [admin]
GO

2. 创建只读账户 ​

2.1 创建登录服务器账户 ​

sql
USE MyDatabase;
GO
CREATE LOGIN MyUser WITH PASSWORD = 'han123', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
GO

-- 创建数据库用户
CREATE USER MyUser FOR LOGIN MyUser;
GO
USE MyDatabase;
GO
CREATE LOGIN MyUser WITH PASSWORD = 'han123', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
GO

-- 创建数据库用户
CREATE USER MyUser FOR LOGIN MyUser;
GO
  • CREATE LOGIN 命令用于在 SQL Server 实例级别创建登录账户
  • CREATE USER 命令则用于在特定数据库中创建用户账户

2.2 授权 ​

1.role授权 ​

sql
EXEC sp_addrolemember 'db_datareader', 'MyUser';

USE [YourDatabaseName];
GO

ALTER ROLE db_datareader ADD MEMBER MyUser;
GO
EXEC sp_addrolemember 'db_datareader', 'MyUser';

USE [YourDatabaseName];
GO

ALTER ROLE db_datareader ADD MEMBER MyUser;
GO

2.限制访问特定对象 ​

sql
USE [YourDatabaseName];
GO

GRANT SELECT ON [YourSchema].[YourTable] TO [ReadOnlyUser];
GO
USE [YourDatabaseName];
GO

GRANT SELECT ON [YourSchema].[YourTable] TO [ReadOnlyUser];
GO

3. AlwaysOn从库用户权限 ​

现象：

现有SQL SERVER服务器A和B，两个环境都提前创建好用户，设置好权限，然后搭建的AlwaysOn,主副本为A，辅助副本为B，客户端连接正常。切换主库为B后，客户端无法连接。查看A与B中的用户权限可以发现，用户名密码都是一样的，但在数据库映射这一项，A中用户有映射到相应库，B中却没有。于是添加映射，保存，这时提示：用户已存在！（这是B为主库时才能做的操作，若A为主库，在B上执行此操作会提示B为只读。。。）

3.1 查看主库sid ​

查看主库上该账号的sid：

  • 查看所有
sql
SELECT * FROM [dbName]..sysusers;
SELECT * FROM [dbName]..sysusers;
  • 指定查看
sql
select [sid] from sys.syslogins where name='hanuser';
select [sid] from sys.syslogins where name='hanuser';

3.2 创建对应账号 ​

在副本上面操作

sql
CREATE LOGIN bradUser WITH PASSWORD = '?',
SID = ?, --从上面的查询从获取
DEFAULT_DATABASE = [数据库名称], --默认数据库
CHECK_EXPIRATION = OFF, --强制密码过期 关闭
CHECK_POLICY = OFF --强制密码策略 关闭
CREATE LOGIN bradUser WITH PASSWORD = '?',
SID = ?, --从上面的查询从获取
DEFAULT_DATABASE = [数据库名称], --默认数据库
CHECK_EXPIRATION = OFF, --强制密码过期 关闭
CHECK_POLICY = OFF --强制密码策略 关闭

❌ 注意

如果数据库想搬迁到其他环境，或AlwaysOn的从库中运行，都需要用相同的SID来创建用户

或者，只是搬迁数据库，可以解除孤立用户，重新设置权限：

USE 数据库名

GO

sp_change_users_login 'update_one','用户名','用户名'

文档，https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-user-transact-sql?view=sql-server-2017

4. 查看权限 ​

4.1 查看用户权限是否冲突 ​

sql
SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('[user_name]');
SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('[user_name]');