SQLServer · 最佳实践 · RDS for SQLServer 2012权限限制提升与改善
最后更新于：2022-04-01 10:33:53
## 背景
SQL Server 作为一种强大的关系型数据库，能够提供所有场景的应用。在绝大多数云计算公司中，都提供了SQL Server作为服务的云数据库服务，譬如阿里云。但既然是服务，那么服务就需要可管理，可控制，因此，在云计算初期，都对云数据库服务进行了严格的权限控制，好处就是可控可管理，但给用户会带来一些限制，某些限制实际上是可以再细粒度管控。因此，今天我们就要介绍一下阿里云数据库SQL Server 2012在权限限制方面的提升与改善。
## 用户最关注的权限和使用
根据我们对用户的理解和日常用户的反馈，最常见的需求如下：
1\. 需要创建、修改和删除登录和用户的权限
2\. 需要创建、修改和删除数据库的权限
3\. 数据库上的所有权限
4\. 需要结束连接的权限
5\. 需要方便读取数据库日志的权限
6\. 需要链接服务器权限
7\. 需要SQL Server Profiler的跟踪功能
8\. 需要Database Engine Tuning Advisor来调校性能
## 一、 RDS SQL Server 2012权限新增功能使用介绍
我们在SQL Server 2012的版本中，用户的关注是我们改进和提升的方向，因此，经过论证与设计，会将这些权限还给用户，让用户有更多的自由选择和方便调试性能、管理自己的数据。那么如何来使用这些功能，我这里会将每个需求都演示一下如何使用（针对阿里云数据库SQL Server 2012）：
### 1\. 创建、修改和删除登录和用户的权限
首先，阿里云数据库SQL Server 2012会提供一个根帐户，这个根帐户也可以与你新建帐户的权限一致，但强烈建议你新建一个帐户,根帐户可以修改密码，也可以DISABLE掉，甚至删除掉，你还可以重置根账号。
#### 1.1 创建LOGIN
~~~
--check current login user
SELECT SUSER_NAME(),ORIGINAL_LOGIN()
--create login
CREATE LOGIN Test11
WITH PASSWORD=N'4C9ED138-C8F5-4185-9E7A-8325465CA9B7'
~~~
创建登录过程中，会将服务器级、数据库级别等权限授予用户。你会在message(消息)看到下列信息：
#### 1.2 更改LOGIN
~~~
--create login
ALTER LOGIN Test11
WITH PASSWORD=N'123',
CHECK_POLICY=OFF
~~~
你不能更改你创建的任何之外的LOGIN，否则会出现一下错误：
#### 1.3 删除LOGIN
~~~
--drop login
DROP LOGIN Test11
~~~
同样你不能删除你创建的任何之外的LOGIN，否则会出现一下错误：
#### 1.4 创建USER
你只能在自己新建的用户数据库创建用户，在系统数据库上是无法做操作的, 所以为了演示创建用户，需要先创建一个数据库testdb(稍后介绍具体信息)：
使用login test11登录到实例（假如你的密码是123，因为刚才新建的用户已经删除，请重新创建一下）
请注意，如果是当前用户创建数据库，那么就将当前用户设置该数据库的一个用户，并且角色自动分配为db_owner, 你也可为数据库创建其他用户和角色：
~~~
USE TestDB
GO
--create user
CREATE USER [Test] FOR LOGIN [Test]
--add database role
ALTER ROLE [db_owner] ADD MEMBER [Test]
GO
-- query user name and role name
SELECT
dpp.name,dpm.name
FROM sys.database_principals dpp
INNER JOIN sys.database_role_members drm
ON dpp.principal_id=drm.member_principal_id
INNER JOIN sys.database_principals dpm
ON dpm.principal_id=drm.role_principal_id
WHERE dpp.name='test'
~~~
#### 1.5 更新USER
你可以更改USER,与SQL SERVER原始的操作方式一样，比如更改用户映射的登录，如下：
~~~
USE TestDB
GO
ALTER USER test WITH LOGIN=test
~~~
#### 1.6 删除USER
你还可以更改USER，与SQL SERVER原始的操作方式一样，如下：
~~~
USE TestDB
GO
DROP USER test
~~~
## 二、创建、修改和删除数据库
## 2.1 创建数据库
正如上面所说，如果是当前用户创建数据库，那么就将当前用户设置该数据库的一个用户，并且角色自动分配为db_owner。如下：
~~~
CREATE DATABASE TestDb
~~~
你会收到下列消息：
~~~
Change database owner to sa sucessfully.
Create user Test11 and grant db_owner role in database TestDb.
~~~
表示当前创建的登录用户是Test11,并且已经分配db_owner角色
注意：
在创建数据库是，你不能随意指定文件路径，你可以不指定任何路径，例如上面的SQL语句，但你也可以指定正确的路径。如果你指定错误的路径，会收到错误提示：
~~~
USE [master]
GO
CREATE DATABASE [TestDb_error_path]
ON PRIMARY
( NAME = N'TestDb_error_path', FILENAME = N'E:\KKKK\DDD\\DATA\TestDb_error_path.mdf' )
LOG ON
( NAME = N'TestDb_error_path_Log', FILENAME = N'E:\\KKKK\DDD\\DATA\TestDb_error_path_log.ldf')
~~~
你会收到下列信息:
~~~
Msg 50000, Level 16, State 1, Procedure *******, Line 57
The file path [
E:\\KKKK\DDD\\DATA\TestDb_error_path.mdf
E:\\KKKK\DDD\\DATA\TestDb_error_path_log.ldf ] is invalid,please specify correct path folder [ E:\mmm\gggg\ ].
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.
~~~
显然，这个正确的路径只有E:\mmm\gggg\，其他任何路径都不会创建成功。 这个只是为了规范正确的路径而已，没有其他任何功能限制。
## 2.2 更改数据库
你可以更改大部分的数据库属性，但有几个地方你需要注意，有些特性是不可以随便更改的,。
### 2.2.1 不能移动到错误的文件路径
~~~
ALTER DATABASE [TestDb]
MODIFY FILE
( NAME = N'TestDb', FILENAME = N'E:\KKKK\DDD\DATA\TestDb.mdf' )
~~~
你会得到下列信息：
~~~
Msg 50000, Level 16, State 1, Procedure ******, Line 152
The file path [
E:\KKKK\DDD\DATA\TestDb.mdf ] is invalid,please specify correct path folder [ E:\mmm\gggg\ ].
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.
~~~
### 2.3.2 不能将数据库的恢复模式设置为FULL之外的其他模式
~~~
ALTER DATABASE [TestDb]
SET RECOVERY SIMPLE
~~~
你会得到下列信息：
~~~
Msg 50000, Level 16, State 1, Procedure ******, Line 46
Login User [Test11] can't change database [TestDb] recovery model.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.
~~~
### 2.3.3 将数据库设置为OFFLINE后，不能直接ONLINE
~~~
USE [master]
GO
--set offline
--ALTER DATABASE [TestDb]
--SET OFFLINE
--WITH ROLLBACK AFTER 0
ALTER DATABASE [TestDb]
SET ONLINE
~~~
你会得到下列错误信息：
~~~
Msg 5011, Level 14, State 9, Line 1
User does not have permission to alter database 'TestDb', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
~~~
当前设置为OFFLINE时，也不必惊慌，你可以使用sp_rds_set_db_online存储过程，让数据库ONLINE:
~~~
EXEC sp_rds_set_db_online 'TestDb'
~~~
## 2.2 删除数据库
删除数据库没有任何特别之处，但是如果你的数据库没有进行过任何备份，也会提示：
~~~
DROP DATABASE [TestDb]
-------------------------------------------------------------------------------------------------
Kindly reminder:
your database [TestDb] does not exist any backup set.
-------------------------------------------------------------------------------------------------
Login User [Test11] has dropped database [TestDb] .
~~~
## 三、数据库上的所有权限
数据库我们首先设置为db_owner,但是回收了备份等必要的管控机制，现在的数据库级别权限非常的自由，但也是有两面性，权限更多了，那么自己操作的时候就需要更严谨和更仔细，避免导致数据库的使用问题。
## 四、结束连接的权限
结束连接的权限，我们更通俗地将是KILL权限，RDS SQL Server 2012已经授予这个权限，但是用户只能KILL自己的的连接，用户不要KILL其他连接，比如备份的连接。
~~~
KILL (SPID)
~~~
## 五、需要方便读取数据库日志的权限
以前你需要在控制台读取数据库错误日志，现在你可以直接使用存储过程sp_rds_read_error_logs读取错误日志，使用方法与sp_readerrorlog是一样的：
~~~
-- eg.1
EXEC sp_rds_read_error_logs
-- eg.2
EXEC sp_rds_read_error_logs 0,1 ,'error'
~~~
## 六、需要链接服务器权限
链接服务器现在还存在两个问题，首先，不能用UI界面去创建链接服务器，UI界面创建链接服务器需要sysadmin权限，可以使用一系列的存储过程创建。第二，由于RDS的设计，不能直接通过DNS和对应的IP创建，具体创建方式，暂时不能提供。但有解决方案。但我们提供了一个简单的一键创建方式：
~~~
DECLARE
@linked_server_name sysname = N'my_link_server',
@data_source sysname = N'***********', --style: 10.1.10.1,1433
@user_name sysname = N'****' ,
@password nvarchar(128) = N'**********',
@link_server_options xml
= N'
<rds_linked_server>
<config option="data access">true</config>
<config option="rpc">true</config>
<config option="rpc out">true</config>
</rds_linked_server>
'
EXEC sp_rds_add_linked_server
@linked_server_name,
@data_source,
@user_name,
@password,
@link_server_options
~~~
如下图：
message(消息):
The linked server ‘my_link_server’ has set option ‘data access’ to ‘true’.
The linked server ‘my_link_server’ has set option ‘rpc’ to ‘true’.
The linked server ‘my_link_server’ has set option ‘rpc out’ to ‘true’.
create link server ‘my_link_server’ successfully.
## 七、SQL Server Profiler的跟踪功能
RDS SQL Server 2012的 Profiler功能没有变化，但是不能生成文件到服务器上，因为你不能登录到RDS的实例服务器上。你可以放在表里。在使用SQL Server Profiler功能时，请注尽可能使用多的过滤条件和使用少量的列。
## 八、Database Engine Tuning Advisor来调校性能
这个功能的使用方法没有办法，不过，绝大多数时，用户不会使用这个功能。只是提供一种可选的方式。
## 总结
RDS SQL Server在权限方面做了很多改变，能够满足绝大多数用户的使用，性能调优和管理方面的需求。但是权限开放了，也需要用户谨慎使用，比如数据库层面，可以有更多设置了，设置不同的值会对数据库的性能产生不同影响。