The system objects are the objects which store the schema for all your objects. The stored procedures, table schemas, etc. are all stored in these system objects.
If the system objects become corrupt you have to restore the primary filegroup.
You can view the data within the system objects by querying the system catalog views such as sys.tables, sys.procedures, etc.
Do they appear in the 'System' Database?
Not sure what you are talking about, there's no database called "System". The system databases are master, msdb, model (to some extent) and tempdb. There is no user database data stored within the system databases. (Things like logins, jobs, SSIS packages not withstanding.)
From BOL:
any permission denial overrides all grants
The implication is that when you DENY
permissions to an object those permissions override any GRANTed
permissions applicable to that object.
Having said that, you don't need to explicitly deny "view definition" permissions on procedures and other code. If you don't grant VIEW DEFINITION
the definitions are not visible, even if the code can be executed. Tables work slightly differently; you'll need to deny view definition
to any tables the role has SELECT
rights on.
You can test this using the code below.
Create a test database:
USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
BEGIN
DROP DATABASE TestPerms_20160921;
END;
GO
CREATE DATABASE TestPerms_20160921;
GO
USE TestPerms_20160921;
GO
Create a table, and a stored proc:
IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test;
CREATE TABLE dbo.Test
(
TestID INT NOT NULL
);
GO
IF OBJECT_ID('dbo.TestProc') IS NOT NULL
DROP PROCEDURE dbo.TestProc;
GO
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
SELECT 1;
END
GO
Create a test user, and a test role:
IF EXISTS (
SELECT 1
FROM sys.database_principals dp
WHERE dp.name = 'TestU'
)
BEGIN
DROP USER TestU;
END
CREATE USER TestU WITHOUT LOGIN;
IF EXISTS (
SELECT *
FROM sys.database_principals dp
WHERE dp.name = 'dbtestrole'
)
BEGIN
DROP ROLE dbtestrole;
END
CREATE ROLE dbtestrole;
ALTER ROLE dbtestrole ADD MEMBER TestU;
Grant SELECT
and EXECUTE
to the test objects:
GRANT SELECT ON dbo.Test TO dbtestrole;
GRANT EXECUTE ON dbo.TestProc TO dbtestrole;
Impersonate the user so we can see the effect of the GRANT
statements above:
EXECUTE AS USER = 'TestU';
/* column names and types **are** visible */
SELECT s.name
, t.name
, c.name
, ty.name
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
AND t.name = 'Test'
ORDER BY s.name
, t.name
, c.name;
/* access to view the definition is denied */
EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL;
/* we can run the proc! */
EXEC dbo.TestProc;
REVERT;
DENY VIEW DEFINITION
on the table, and test again:
DENY VIEW DEFINITION ON dbo.Test TO dbtestrole;
EXECUTE AS USER = 'TestU';
/* no rows returned as we can no longer view the table definition */
SELECT s.name
, t.name
, c.name
, ty.name
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
AND t.name = 'Test'
ORDER BY s.name
, t.name
, c.name;
/* we can still run the proc */
EXEC dbo.TestProc;
/* we can still see the content of the table */
SELECT *
FROM dbo.Test;
REVERT;
Cleanup:
USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
DROP DATABASE TestPerms_20160921;
Best Answer
The underlying system tables used by the
sys.symmetric_keys
catalog view are stored on the PRIMARY filegroup along with other database meta-data.