Sql-server – SQL Server Security Hierarchy

It does not open up directly to an "attack". It just means that any user from Database 1 (Kdb) can also access database 2 (Ydb). What's usually more critical is, when you have users with DDL-Permissions (create views, procedures) - they will also be able to access objects in database2. Maybe even more, than plain guests can. That depends on the object owners and permissions you are planning to grant to guest(s).

Under SQL Server before 2012, there was one little security hole, described here: Security-issue: guest-guest impersonation, but this is a very limited scenario

The root of the problem is that I have a schema-only database defined that is generated automatically via triggers. When a DDL statement gets replayed by the INSERT trigger of the changelog table, the error occurs when the schema is out of sync (The schema did not exist in my SchemaOnlyDatabase). The error was being thrown in the context of the SchemaOnlyDatabase, not the database that was currently active TheDevDatabase, but that was not clearly indicated by the message.

CREATE TRIGGER [RecordDatabaseChanges]                                
ON ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS
AS                                                      
BEGIN
SET NOCOUNT ON;
--ALL SETTINGS NEEDED FOR XQUERY IN DDL TRIGGERS
--http://tomaslind.net/2013/10/15/zero-footprint-serverdatabase-ddl-trigger/
SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
DECLARE @data XML;
SET @data = EVENTDATA();
DECLARE @EventType NVARCHAR(30)
SELECT @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(30)')
IF @EventType IN (
N'ADD_ROLE_MEMBER',
N'ALTER_AUTHORIZATION_DATABASE',
N'ADD_SIGNATURE_SCHEMA_OBJECT',
N'DENY_DATABASE',
N'DROP_ROLE_MEMBER',
N'GRANT_DATABASE',
N'REVOKE_DATABASE',
N'UPDATE_STATISTICS'
)
RETURN
INSERT INTO [ChangeLogDb].[dbo].[ChangeLog] ([Event Type], [Database], UserName, SPID, [Schema], Name, [New Name], ObjectType, [New ObjectType], [SQL Used])
SELECT  
COALESCE(@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(30)'),'') AS [Event Type],
COALESCE(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(128)'),'') AS [Database],
COALESCE(@data.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(128)'),'') AS [UserName],
COALESCE(@data.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(128)'),'') AS [SPID],
COALESCE(@data.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(128)'),'') AS [Schema],
COALESCE(@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(128)'),'') AS [Name],
COALESCE(@data.value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(128)'),'') AS [New Name],
COALESCE(@data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(128)'),'') AS [ObjectType],
COALESCE(@data.value('(/EVENT_INSTANCE/TargetObjectType)[1]','nvarchar(128)'),'') AS [New ObjectType],
COALESCE(@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),'') AS [SQL Used]
END

CREATE TABLE [ChangeLogDb].[dbo].[ChangeLog]
(
[Changelog_ID] [int] IDENTITY(1,1) NOT NULL,
[Event Type] [nvarchar](30) NOT NULL,
[When] [datetime] NOT NULL CONSTRAINT [DF_ChangeLog_When]  DEFAULT (getdate()),
[Database] [nvarchar](128) NOT NULL,
[UserName] [nvarchar](128) NOT NULL,
[SPID] [nvarchar](128) NOT NULL,
[Schema] [nvarchar](128) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
[New Name] [nvarchar](128) NOT NULL,
[ObjectType] [nvarchar](128) NOT NULL,
[New ObjectType] [nvarchar](128) NOT NULL,
[SQL Used] [nvarchar](max) NOT NULL,
[NTUserName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_NTUser]  DEFAULT (suser_sname()),
[HostName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_HostName]  DEFAULT (host_name()),
[SessionLoginName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_SessionLoginName]  DEFAULT (suser_name()),
CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED ([Changelog_ID] ASC)
)

AFTER INSERT AS 
BEGIN
DECLARE @sqlbatch NVARCHAR(MAX)
DECLARE @databasename VARCHAR(255)
DECLARE sqlused_cursor CURSOR FAST_FORWARD FOR
SELECT i.[DATABASE], i.[SQL Used] FROM inserted i WHERE i.[Database] = 'TheDevDatabase'        
OPEN sqlused_cursor
FETCH NEXT FROM sqlused_cursor INTO @databasename, @sqlbatch
WHILE @@FETCH_STATUS = 0
BEGIN
-- re-execute in the context of the TheSchemaDatabase db.
EXEC TheSchemaDatabase.dbo.sp_executesql @Stmt=@sqlbatch
FETCH NEXT FROM sqlused_cursor INTO @databasename, @sqlbatch
END
CLOSE sqlused_cursor
DEALLOCATE sqlused_cursor
END