November 9, 2009 at 7:23 am
Hi folks.
We've been trying to implement a DDL log so we can monitor who changes what in our databases as we have some report writers adding/modifying SP's, functions, views.
Initially we tried doing this with a Database trigger. All seemed to work as expected on our test box. When we moved this to our developer box, permission issues started popping up. We carefully checked permissions and couldn't find an evident step we failed to replicate.
The only difference we found was that on our test box SQL runs as LocalSystem whereas on the Developer it was with a domain account. We then tried to test this on another box that had SQL running on LocalSystem and encountered the same errors....
What we're trying to accomplish is the following:
* on each DDL issued we're just trying to log who changed what when.
* this change needs to be recorded in a separate DB, if possible.
The errors we got is that the user (which could be a SQL user or a domain user) didn't have permission to insert in the other DB and no, it wasn't as easy as granting access to the DB and insert into the table......users are in domain groups which is how they are authenticated to the server and granted needed access to the DB's.
We're pretty sure it has to do something on how permissions of users are impersonated in the context of the chain of commands.
We even tried it with the DB trigger calling a SP that did the insert, and where the owner of the SP had access to the other DB and also using brokers, but somewhere along the way we're missing the proper permissions.
So, any ideas on how this can be implemented best? The idea is to be able to register the changes in a centralized place (preferably different DB) without giving users to much access...
Thanks all..
November 9, 2009 at 9:19 am
I was going to do this some time back and was looking at using event notification to write xml to a table and set up a trigger on that table to examine the xml and record the schema changes and script out the object for source control.
It didn't get passed the idea stage for me due to other commitments and priorities but maybe worth investigating?
November 9, 2009 at 9:46 am
November 9, 2009 at 10:34 am
I use service broker to write all DDL to a central repository. It works well but was kind of difficult to get going at first.
November 9, 2009 at 10:51 am
Have you tried creating the trigger with EXECUTE AS clause? You might try to run it as sysadmin or some other login that has access to relevant databases.
Regards
Piotr
...and your only reply is slàinte mhath
November 9, 2009 at 10:58 am
@ben: would you mind sharing the exact steps setting it up, users and access? As explained in my original post, that's what we did successfully on the test machine but haven't been able to replicate on other servers....
@piotr: yes, but with the EXECUTE as you enter the realm of the permissions and how they are chained. Imagine any user was able to use execute as a sysadmin account or other privileged account.
November 9, 2009 at 11:10 am
Richard M. (11/9/2009)
@ben: would you mind sharing the exact steps setting it up, users and access? As explained in my original post, that's what we did successfully on the test machine but haven't been able to replicate on other servers.....
Check your PM
November 9, 2009 at 11:46 am
Richard
for my DDL database trigger, I'm using execute as without an issue;
here's how my trigger starts:
CREATE TRIGGER [ReturnPREventData]
on DATABASE WITH EXECUTE AS 'dbo'
FOR
CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE
AS
here's a complete exampel for reference:
use SandBox
GO
CREATE TABLE [dbo].[DDLEventLog](
[EventDate] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[objectName] [sysname] NOT NULL,
[CommandText] [varchar](max) NOT NULL,
[EventType] [nvarchar](100) NULL
)
--
GO
CREATE TRIGGER [ReturnPREventData]
on DATABASE WITH EXECUTE AS 'dbo'
FOR
CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE
AS
declare @eventData XML,
@uname nvarchar(50),
@oname nvarchar(100),
@otext varchar(max),
@etype nvarchar(100),
@edate datetime
SET @eventData = eventdata()
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
if @uname is not null
begin
insert dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) values
(@edate,@uname,@oname,@otext,@etype)
end
GO
ENABLE TRIGGER [ReturnPREventData] ON DATABASE
GO
--create a user that has db_owner writes to create a proc
create login test with password='test123'
go
use Sandbox
go
create user utest from login test
go
--add user to db_owner to see what happens
exec sp_addrolemember 'db_ddladmin', 'utest'
go
--impersonate utest user - or open new window and log on as test login
execute as user='utest'
go
--check security context
print user_name()
go
ALTER procedure [dbo].[sp_find]
@findcolumn varchar(50)
AS
BEGIN
SET NOCOUNT ON
--print object_name(@@PROCID)
SELECT
TableFound,
ColumnFound
FROM
(
SELECT
1 AS SortOrder,
sysobjects.name AS TableFound,
'' AS ColumnFound
FROM sysobjects
WHERE sysobjects.xtype IN('U' ,'V')
AND sysobjects.name LIKE '%' + @findcolumn + '%'
UNION ALL
SELECT
2 AS SortOrder,
sysobjects.name AS TableFound,
syscolumns.name AS ColumnFound
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id=syscolumns.id
WHERE sysobjects.xtype IN('U' ,'V')
AND syscolumns.name like '%' + @findcolumn + '%'
) X
ORDER BY
SortOrder,
TableFound,
ColumnFound
END
--change back to myself, who is a super user anyway
go
revert;
--as myself, see what changed.
select * from [DDLEventLog]
Lowell
November 9, 2009 at 11:50 am
Lowell,
thank you for the example.
i don't think we have tried with 'dbo' as the execute as user, but then again, in your example the table that captures the data is in the same DB as the trigger.... I will do some experimenting anyway.... Thanks!
November 9, 2009 at 12:15 pm
@piotr: yes, but with the EXECUTE as you enter the realm of the permissions and how they are chained. Imagine any user was able to use execute as a sysadmin account or other privileged account.
Agreed, I suggested sysadmin login for testing purposes only, as it has access to all databases. You should create different login, for example from a certificate and map it to all databases.
Without EXECUTE AS you are in the realm of chained/unchained permissions anyway. Just out of curiosity, what are you trustworthy and chaining settings in DEV and production servers?
Regards
Piotr
...and your only reply is slàinte mhath
November 9, 2009 at 12:23 pm
Piotr.Rodak (11/9/2009)
@piotr: yes, but with the EXECUTE as you enter the realm of the permissions and how they are chained. Imagine any user was able to use execute as a sysadmin account or other privileged account.
Agreed, I suggested sysadmin login for testing purposes only, as it has access to all databases. You should create different login, for example from a certificate and map it to all databases.
Without EXECUTE AS you are in the realm of chained/unchained permissions anyway. Just out of curiosity, what are you trustworthy and chaining settings in DEV and production servers?
Regards
Piotr
Trustworthy and Cross-Database ownership chaining are disabled, but you just gave me something to look at....
November 9, 2009 at 12:33 pm
November 9, 2009 at 12:43 pm
Great 🙂
...and your only reply is slàinte mhath
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply