February 9, 2010 at 8:01 am
trying to create a DDL trigger to log into a separate database and fire off an email to the DBA's when a developer makes a change. to keep permissions the same i want to use execute as and i created a new user called audit_user under which to run the insert into the log database. the log database is called admindb and i gave audit_user dbo rights to it along with the msdb.
create trigger [trig_alter_sp]
ON database
with execute as 'audit_user'
FOR ALTER_PROCEDURE
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT admindb..ddl_log
(PostTime, DB_User, hostname, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER), host_name(),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
exec msdb..sp_send_dbmail
@profile_name = 'dba',
@recipients = 'dba@broadviewnet.com',
@subject = 'Stored PRocedure was changed',
@body = 'see audit table for details'
to test i'm altering a simple stored procedure in a dev server and getting the following error
The server principal "audit_user" is not able to access the database "admindb" under the current security context.
audit_user has dbo rights to the the admindb database
February 9, 2010 at 8:19 am
You will need to set the source database to TRUSTWORTHY. Note that doing so has security implications.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 9, 2010 at 9:13 am
now i'm getting. even detached and reattached the database
Msg 33009, Level 16, State 2, Procedure trig_alter_sp, Line 0
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'DataFarm'. You should correct this situation by resetting the owner of database 'DataFarm' using the ALTER AUTHORIZATION statement.
The statement has been terminated.
February 9, 2010 at 6:43 pm
Do what it says.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 9, 2010 at 6:44 pm
Or alternatively just set the owner of the database to 'sa'.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 12, 2010 at 6:37 am
RBarryYoung (2/9/2010)
You will need to set the source database to TRUSTWORTHY. Note that doing so has security implications.
HOw to set this setting ?
if i set this user with "db_owner" then ? or what other priviledge i can give it to him.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 12, 2010 at 9:10 pm
TRUSTWORTHY is a Database setting, not a user or login privilege. You set it like this:
ALTER DATABASE SET TRUSTWORTHY ON
You should beware however because TRUSTWORTHY can be a problem for your Server's security:
What TRUSTWORTHY means in practical terms, is that if a user acquires the dbo's User identity within that database (Usually through something like EXECUTE AS), then the Server will also allow it to gain the database owner's Login identity on the Server and in other database's in that same server.
Thus, what setting TRUSTWORTHY on a database means conceptually, is that you trust that database's security to NOT allow anyone to get a User/Login identity that they should not have. If this is not true, then you could be making a huge security hole for yourself.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply