Error: Permissions denied mssqlsystemresource – configurations

Last week I encountered a strange issue on a production SQL Server. We weren’t able to connect to some of them. If we tried to connect with a normal Windows NT (Active Directory) or SQL account, we got the following error (I recreated the error locally):

Message: The SELECT permission was denied on the object ‘configurations’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)

For further information about the mssqlsystemresource database, please read my previous post about this database.

But in my case, there was nothing wrong with this Resource database. After all, no other database was unavailable or corrupt. So I needed a little bit more research. After a while I found out that the issue is coming from a security setting.

We allowed an external company of Data Warehouse consultants (KVL) to access our production SQL Server. They needed to create a Snapshot, and the person responsible for making this possible created 2 Stored Procedures (SP’s) for this task. One SP to create a Snapshot, and one to drop the Snapshot. These SP’s are stored in the Master database.

But because he wanted them to only access the SP’s, the created a user for this (again, created this locally):

And he added a server mapping for the user:

After that he tested it, and it worked like a charm! He e-mailed me the location of the SP’s and the names, so I could send them to our consultants. Then he added one more thing to the User Mappings without any of us knowing:

When the consultants tried to create the Snapshot, they couldn’t get it to work. After some research I found out that the User Mapping on the Master database was set to db_denydatareader. As you all know, deny permissions always overrule access permissions. In this case this worked against us.

So if you ever encounter this issue, please take a look at your security settings. If all of your other databases are still accessible, the error is coming from a lack of permissions. You’ll encounter this issue most of the time when a user (NT or SQL user) is a member of 2 separate groups with different permissions.

SQL Server system database – mssqlsystemresource

The database mssqlsystemresource is a read-only database that is shipped with SQL Server from version SQL Server 2005. It contains all the system objects that are included in SQL Server. An example of this are the sys.objects. These are stored in the Resource database, but appear in every other database. The sys.objects from a user database refer to the Resource database.

SQL Server cannot backup this database. You can create a backup yourself, but don’t back it up like a normal .MDF file. It also can’t be restored via SQL Server, but you can do it manually.

The Resource database makes upgrading to a new version easier and faster.

The mssqlsystemresource database is invisible for users, even for the System Administrator (sa). The database can be restored by copying the .ldf and .mdf files from the folder “[Drive]\Program Files\Microsoft SQL Server\MSSQL10_50.[Instance Name]\MSSQL\Binn” to a folder of your choice (in this case D:\Databases). After that, start a query with the user sa, and run the following script:

USE [master]
GO

CREATE DATABASE [mssqlsystemresource_RESTORED] ON 
	(FILENAME = N'D:\Databases\mssqlsystemresource.mdf'),
	(FILENAME = N'D:\Databases\mssqlsystemresource.ldf')
FOR ATTACH
GO

Once you’ve done this, you can query the restored version of the Resource database.

Comparison: Ad-hoc vs Stored Procedure vs Dynamic SQL

Sometimes when you are creating a SQL query, you wonder if it’s more sensible to use a standard T-SQL, or some other possibility. In this post I will try to compare the three different possibilities, so that you can make your own decision. If you read the comparison below, keep in mind that there is no right or wrong in this. Each situation requires a different point of view, and may offer it’s own difficulties.

  Ad-Hoc Query Stored Procedure Dynamic SQL
Use it for Long, complex queries
(OLAP; for example Reporting or Analysis)
Short, simple queries
(OLTP; for example Insert, Update, Delete, Select)
Short, simple queries
(OLTP; for example Insert, Update, Delete, Select)
Performance Compiled at runtime, Execution Plan stored in Cache

Changed data is no issues because of re-compile

Compiled once at first run, and stored in Procedure Cache

Changed data might be a performance bottleneck. Can be
solved with recompile

Compiled at runtime, and execution plan is not stored

(unless
using the more efficient sp_executesql)

Changed data is no issue because of re-compile

Security Permissions (read/write) on all objects (database(s)/table(s)) Execute permissions on SP are enough Permissions (read/write) on all objects (database(s)/table(s))
Flexibility If changed, your application needs to be recompiled If changed, only need to change the SP in the database If changed, your application needs to be recompiled
Number of Statements Only 1 statement possible Multiple statements possible Multiple statements possible
Memory Usage Uses more memory then an SP Uses less memory then an ad-hoc query Uses more memory then an SP
Network traffic Query executes server side

Query and resultset are send across
network/internet

Query executes server side

Execute statement
and resultset are send
across network/internet

Query executes server side

Statement
and resultset are send across
network/internet

Separation Database logic and business logic are mostly combined in the query Seperate database logic from business logic Seperate database logic from business logic
Troubleshoot Relatively easy to troubleshoot Relatively easy to troubleshoot Difficult to troubleshoot
Maintaining Difficult because of several locations in applications and database Easy because of single location Difficult because of several locations in
applications and database
Access Difficult to access multiple objects in different databases,
or in dynamic databases
Difficult to access multiple objects in different databases,
or in dynamic
database
Allows any object (database, table, columns, etc) to be referenced
WHERE clause Fairly static WHERE clause Fairly static WHERE clause Dynamic WHERE clause (add/remove), based on parameters
Versioning Only possible via Source Controlling your application Possible via Source Controlling your database, and by commenting your SP Only possible via Source Controlling your application
CRUD Can be created by getting all your  queries together, and looking for
specific
keywords

(Update, Delete, Select, Etc)
Difficult to catch in a CRUD

(Create, Read, Update, Delete) diagram
Difficult to catch in a CRUD (Create, Read, Update, Delete) diagram
Structure Update Can be changed
simultaneously with structure changes
Needs to be altered when the underlying structure is changed Can be changed simultaneously with structure changes
Searching No standard way to search through Possible to use sys.procedures to search through SP contents.

Dependency window in SSMS shows SP content

No standard way to search through
Testing Can be compiled/tested in code Impossible to automatically compile without 3rd party tools Difficult to test in code
Mapping ORM (Object-relational mapping) is possible ORM (Object-relational mapping) is impossible ORM (Object-relational mapping) is impossible
Compiling Compiles the whole statement Compiles the whole statement Only static elements can be compiled

For the design of this comparison chart, I need to thank my buddy and colleague Pascal (Blog | @pdejonge). For the record: I’m not a designer, and my “design” was what you guys might call Fugly.

These comparison chart covers the main reasons for me to use or not use a specific option. These are my personal beliefs. If you have any suggestions to add, please don’t hesitate to contact me.

Follow

Get every new post delivered to your Inbox.

Join 50 other followers