| Author |
 Topic  |
|
|
t1g312
Posting Yak Master
United Arab Emirates
148 Posts |
 Posted - 07/04/2005 : 02:15:51
|
Hi All,
I have created a role that grants exec access to all my stored procedures. Unfortunately, it does not allow my audit trail trigger to execute. What do I do?
Thanks in advance,
Adi
-------------------------
/me sux @sql server |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/04/2005 : 03:27:32
|
Have the audit trail trigger call an SProc?
What's it doing that is denied?
Kristen |
 |
|
|
t1g312
Posting Yak Master
United Arab Emirates
148 Posts |
Posted - 07/04/2005 : 03:36:49
|
quote:
Originally posted by Kristen
Have the audit trail trigger call an SProc?
What's it doing that is denied?
Kristen
I have an audit trail trigger for each of the tables. Obviously the audit trail trigger tries to read the data from the table but I haven't even given table read access to the role.
Adi
-------------------------
/me sux @sql server |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/04/2005 : 10:16:54
|
Doesn't sound right, the trigger should run with the rights that the SProc was created with. (Unless there is an action on a different database)
Kristen |
|
|
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 07/04/2005 : 21:33:58
|
i want to help but it's quite confusing..
you're saying the role grants execute rights on SPs, now if a user that was granted by the role executes the SP which does something on the table with a trigger, the trigger doesn't function?
check the trigger for any specific conditions that might be the cause,check if the trigger is enabled or better yet post the trigger
--------------------
keeping it simple... |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/05/2005 : 04:29:00
|
I knocked up a specific example here.
Created a pair of tables and a "Save" SProc (as "sa")
Create a trigger on Table1 that inserted into Table2
Created a user
Granted EXECute [on the SProc] to the User
REVOKEd SELECT, INSERT, UPDATE, DELETE on both tables from the user
The user can execute the SProc just fine ...
Which is how I think it should be (PROVIDED that the Sproc and Tables are all in the same database)
Dunno if that helps though?
Kristen
|
|
|
|
t1g312
Posting Yak Master
United Arab Emirates
148 Posts |
Posted - 07/06/2005 : 02:07:23
|
Hi Kristen,
Thanks for the reply. I'm using a slightly modified version of nr's trigger and I get the error "no PK on table tablename". When I grant read access to the table, it works just fine.
Adi
-------------------------
/me sux @sql server |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/06/2005 : 03:18:58
|
If you post the code you are using we can take a look for you
Kristen |
|
|
|
t1g312
Posting Yak Master
United Arab Emirates
148 Posts |
Posted - 07/06/2005 : 04:26:18
|
Hi Kristen,
The trigger seems to be failing at this point:
-- Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end
And I get the error no PK...
I am assuming that it is because the trigger does not have select access to the table. I can post the entire trigger if you want me to.
Adi
-------------------------
/me sux @sql server |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/06/2005 : 13:13:23
|
As you haven't posted the code I can't see where @PKCols is set.
Either way, @PKCols is NULL and thus it is generating the error [deliberately].
I can't tell without the code, but I presume @PKCols is checking that you have a Primary key on the table, and without it for some reason the code can't run - so its raising an error.
Kristen |
|
|
|
t1g312
Posting Yak Master
United Arab Emirates
148 Posts |
Posted - 07/09/2005 : 01:35:24
|
Hi Kristen,
Here's the code.
CREATE trigger tr_AuditPaxDetails on dbo.TableName for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKSelect varchar(1000)
select @TableName = TableName
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Action
if exists (select * from inserted)
begin
if exists (select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
select top 1 @UserName=UserName from masUser where spid=@@spid order by LastLoginAt desc
select @UpdateDate = convert(varchar,getdate(),120)
end
else
select @Type = 'D'
-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @fieldname = ''
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
begin
if @Type = 'I'
select @fieldname = c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk where c.TABLE_NAME=pk.TABLE_NAME and c.COLUMN_NAME = pk.COLUMN_NAME and c.TABLE_NAME = @TableName and pk.CONSTRAINT_NAME LIKE 'pk%' and c.ORDINAL_POSITION = @field
else
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
if @fieldname <> 'LastUpdateAt' and @fieldname <> 'LastUpdateBy' and NOT(@fieldname IS NULL) and @fieldname <> ''
Begin
select @sql = 'insert AuditTrail (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
select @sql = @sql + ' select ''' + @Type + ''''
select @sql = @sql + ',''' + @TableName + ''''
select @sql = @sql + ',' + @PKSelect
select @sql = @sql + ',''' + @fieldname + ''''
select @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
select @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
select @sql = @sql + ',''' + @UpdateDate + ''''
select @sql = @sql + ',''' + @UserName + ''''
select @sql = @sql + ' from #ins i full outer join #del d'
select @sql = @sql + @PKCols
select @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
select @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
select @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
exec (@sql)
End
end
end
Adi
-------------------------
/me sux @sql server |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/11/2005 : 09:00:54
|
Seems to be as I surmised. You don't have a Primary key on that table, which is a pre-requisite for the trigger you have written.
I'm also surprised that this code, near the beginning, doesn't raise an error:
select @TableName = TableName
Kristen |
|
|
|
t1g312
Posting Yak Master
United Arab Emirates
148 Posts |
Posted - 07/18/2005 : 08:36:44
|
Hi Kristen,
I do have a primary key on the table that I'm trying to Audit.
TableName isn't the real name of the table.
Adi
-------------------------
/me sux @sql server |
|
|
|
sql_newbie121
Yak Posting Veteran
USA
52 Posts |
Posted - 11/23/2009 : 15:41:32
|
Hi Adi:
I want to add two columns on this Trigger based on my table: One is CustomerID and GroupID. GroupID is because there will be lot of inserts and deletes on this table and from the application i just want to show the group ID and if want to see the details, i can click on the groupID and all the changes can be seen. How can i do this? Thanks |
|
|
| |
Topic |
|
Security and Audit Trail
