查看SQL Server Resource Database以及修改系统表

参考文献：

SQL 2005修改系统表

SQL Server 2005中修改系统表的方法

如何登入DAC(SQL Server 2005) 

ResourceDB Location Changes

ResourceDB Location Changes

1.What is resourcedb?(from msdn)

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

2.ResourceDB’s Location

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. Their locations location are different depend on the SQL Server version.

2.1.In SQL Server 2008 and above

<drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\. Such as

D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

2.2.In SQL Server 2005

Same place where master database is located. Such as D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

3.Experiment on 2008 and above

Step1:

Copy the mssqlsystemresource.MDF and mssqlsystemresource.LDF to the master database’s location, rename the copies to MyResourceDB.mdf and MyResourceDB.ldf.

Step2:

Attach the new files with this script generated by the SSMS:

--正常登录模式
USE [master]
GO
CREATE DATABASE [MyResourceDB] ON
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyResourceDB.mdf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyResourceDB.ldf' )
 FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'MyResourceDB' AND
    SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
        EXEC [MyResourceDB].dbo.sp_changedbowner @loginame=N'sa', @map=false
GO
use MyResourceDB
--查询失败：Invalid object name 'sys.role_permissions'.
select * from sys.role_permissions
--修改失败：Invalid object name 'sys.role_permissions'.
update sys.role_permissions  set number=111  where name='System Administrators'

Then you can find the new database MyResourceDB in the Object Explorer. But if you want to open these table in the MyResourceDB, it will report error: Invalid object name 'MyResourceDB.sys.role_permissions'.

Step3: Starting SQL Server in Single-User Mode

1. Close all the SQL Server Service in the SQL Server Configuration manager, such as SQL Server Agent, SQL Server Reporting Service and so on.
2. Use sqlservr.exe -m -c cmd to start SQL Server in single-user mode in the path of D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>.

Step4: Use the Dedicated Administrator Connection to modify system table.

We Use the Dedicated Administrator Connection with SQL Server Management Studio. First open the SSMS, then click New Query, then we choose the Server type and type the server name as the follow picture:

“ADMIN:.” means Dedicated Administrator Connection to the default instance.

Step5: Browse the resourcedb

--DAC模式
use MyResourceDB
select * from MyResourceDB.sys.role_permissions   --system table，succsess successfully
select * from MyResourceDB.sys.databases  --system view,excute successfully

--update this system table，error：Failed to update database "mssqlsystemresource" because the database is read-only.
update MyResourceDB.sys.role_permissions  set number=111  where name='System Administrators' 

--update this system view，success.
update MyResourceDB.sys.databases  set create_date='2009-07-11 11:13:40.723'  where name='TESTDB1' 
--Warning: System table ID 28 has been updated directly in database ID 8 and cache coherence may not have been maintained. SQL Server should be restarted.

------------------------------------------------------------------------------------------

--直接使用mssqlsystemresource
use mssqlsystemresource
select * from mssqlsystemresource.sys.role_permissions
select * from mssqlsystemresource.sys.databases

--修改这张表，报错：Failed to update database "mssqlsystemresource" because the database is read-only.
update mssqlsystemresource.sys.role_permissions  set number=111  where name='System Administrators' 

--与前面使用MyResourceDB不同。修改视图，报错：Failed to update database "mssqlsystemresource" because the database is read-only.
update sys.databases  set create_date='2012-07-11 11:13:40.723'  where name='TESTDB1' 
select * from sys.databases

------------------------------------------------------------------------------------------

use master
select * from sys.databases  --success
update sys.databases  set create_date='2012-07-11 11:13:40.723'  where name='TESTDB1' 

Conclusion

In SQL Server 2008 and above, resource database is a read-only database and every instance has only one. It was created when the database instance installed. Because the resource database is read-only and never changed after it is created, when the resource database is damaged or lost, we can copy other database instance’s resource database to our instance in the same SQL Server version.

This experiment is the similar with the experiment that how to update the sys.databases view in master database.

4.     Experiment on 2005

Now, we have to do the same experiment on SQL Server 2005. As before, I have said that mssqlsystemresource.mdf located the the same path of master.mdf. We also copy and rename these two files and attach them to the database.

At this time, we will see the different result. We can change the sys.role_permissions table without any warning and error. So if you want to change the resource database, you only need to create a resource database copy and attach it as a new database. We can change it whatever we want. At last, we replace the resource database with the new database.

--正常登陆模式,附加数据库
USE [master]
GO
CREATE DATABASE [MyResourceDB] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyResourceDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyResourceDB.ldf' )
 FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'MyResourceDB' AND
    SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) 
        EXEC [MyResourceDB].dbo.sp_changedbowner @loginame=N'sa', @map=false
GO

--使用MyResourceDB
use MyResourceDB
select * from sys.role_permissions--可以查询

--可以修改
begin tran
update sys.role_permissions  set number=111  where name='System Administrators' 
select * from sys.role_permissions   --可以查询
rollback  --回滚
select * from sys.role_permissions   --可以查询

--DAC模式下
use mssqlsystemresource
select * from sys.role_permissions --可以查询
--更新列,报错:无法更新数据库"mssqlsystemresource"，因为数据库是只读的。
update sys.role_permissions  set number=111  where name='System Administrators' 

--使用MyResourceDB
use MyResourceDB
select * from sys.role_permissions--可以查询

--修改,报错:无法更新数据库"mssqlsystemresource"，因为数据库是只读的。
update sys.role_permissions  set number=111  where name='System Administrators' 

结论：

MyResourceDB可在普通登录模式下被修改，在DAC模式下修改MyResourceDB就是在修改mssqlsystemresource这个数据库。

5.比较SQL Server不同版本在查看和修改MyResourceDB上的区别

对于MyResourceDB这个数据库，对于不同版本的SQL Server以及不同的登录模式下，我们有不同的权限，我们通过下面两张表说明其中的不同。

使用SSMS正常登陆

使用SSMS正常登陆	2005	2008	2012
System Tables中是否有表	Y	Y	N
能否查看System Tables表的内容	Y	N	N
能否修改System Tables表的内容	Y	N	N

单用户模式启动实例，使用DAC登录

单用户模式启动实例 使用DAC登录	2005	2008	2012
能否查看System Tables表的内容	Y	Y	Y
能否修改System Tables表的内容	N	N	N

DAC

If you use a named instance, you must open SQL Server Browser Service to connect to dedicated administrator connection (DAC) endpoints. If you use a default instance, it does not need.

在启动sharepoint server的时候无法使用单用户模式启动实例。