SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Security and Audit Trail
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 07/04/2005 :  02:15:51  Show Profile  Reply with Quote
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
22403 Posts

Posted - 07/04/2005 :  03:27:32  Show Profile  Reply with Quote
Have the audit trail trigger call an SProc?

What's it doing that is denied?

Kristen
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 07/04/2005 :  03:36:49  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/04/2005 :  10:16:54  Show Profile  Reply with Quote
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
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 07/04/2005 :  21:33:58  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/05/2005 :  04:29:00  Show Profile  Reply with Quote
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
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 07/06/2005 :  02:07:23  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/06/2005 :  03:18:58  Show Profile  Reply with Quote
If you post the code you are using we can take a look for you

Kristen
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 07/06/2005 :  04:26:18  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/06/2005 :  13:13:23  Show Profile  Reply with Quote
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
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 07/09/2005 :  01:35:24  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/11/2005 :  09:00:54  Show Profile  Reply with Quote
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
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 07/18/2005 :  08:36:44  Show Profile  Reply with Quote
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
Go to Top of Page

sql_newbie121
Yak Posting Veteran

USA
52 Posts

Posted - 11/23/2009 :  15:41:32  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000