September 23, 2010 at 1:08 pm
I am trying to grant a teacher the ability to view database audits for his students, who each has a database. The audits are working great, but when i try and use fn_get_audit_file in a SP using execute as to change the context (so professor can see databases without having CONTROL SERVER privelege) I get this error:
Msg 297, Level 16 State 1, Procedure activity_monitor, Line 8
The user does not have permission to perform this action.
I have granted impersonate priveliges to the user, and given the execute as user dbowner on the database where the SP resides.
I have also logged in as the execute as user and ran the function and it works fine.
Any ideas.
Here is my SP: (mis325_activity_monitor is a view using the function)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[activity_monitor]
@begdate nvarchar(10)
with execute as 'activity_monitor'
AS
select * from mis325_activity_monitor
where event_time > @begdate
September 9, 2011 at 1:46 pm
Hi George.
Did you ever resolve this? I have a similar situation in which I have created a TVF for a user who I do not want to grant CONTROL SERVER to.
The TVF calls sys.fn_get_audit_file, which I do have permissions to use. I thought that by granting SELECT permission to this TVF, the user would inherit my level of permissioning.
However, the user gets the following error ...
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
I experimented with giving the user read access to the underlying files on the db server but that didn't help.
March 8, 2018 at 8:48 am
Hi,
Is there any solution to this?
I want to push audit data into a third-party application that handles audit logs.
I create a sqlServer login and a view which is basically like this:
CREATE view [dbo].[AuditData] as
SELECT * FROM sys.fn_get_audit_file
('E:\Audit\*.sqlaudit',default, default)
GO
When I query this view as someone who has CONTROL SERVER or sysadmin permissions it works, but otherwise I get: The user does not have permission to perform this action.
March 8, 2018 at 2:48 pm
oskargunn - Thursday, March 8, 2018 8:48 AMHi,Is there any solution to this?
I want to push audit data into a third-party application that handles audit logs.
I create a sqlServer login and a view which is basically like this:CREATE view [dbo].[AuditData] as
SELECT * FROM sys.fn_get_audit_file
('E:\Audit\*.sqlaudit',default, default)
GOWhen I query this view as someone who has CONTROL SERVER or sysadmin permissions it works, but otherwise I get: The user does not have permission to perform this action.
One option is to use a stored procedure and sign it with a certificate. I found that approach to be the least problematic for things like this with permissions. You create a certificate and then create a login from the certificate and the elevated rights are granted to that login created from the certificate. Add a signature to the stored procedure and then grant execute on the stored procedure to the regular user you want to be able to execute the stored procedure. There is a good walk through example in the documentation that you can use to try it out - the script includes drops to clean it all up after trying it:
Tutorial: Signing Stored Procedures with a Certificate
Sue
March 12, 2018 at 5:00 am
Hi, thanks for your reply.
I tried this walkthrough but I still get:
Msg 297, Level 16, State 1, Procedure TestSP, Line 13
The user does not have permission to perform this action.
March 12, 2018 at 7:50 am
oskargunn - Monday, March 12, 2018 5:00 AMHi, thanks for your reply.I tried this walkthrough but I still get:
Msg 297, Level 16, State 1, Procedure TestSP, Line 13
The user does not have permission to perform this action.
I just ran through it twice - no errors. Somewhere in the process you missed one of the steps. Try running through the tutorial and do not do the cleanup that starts with the REVERT. Just create all the objects and then double check permissions, objects, etc.
Sue
March 14, 2018 at 3:55 am
I wonder if it is because I issue a select in the procedure against the view that uses the fn_get_audit_file. I tried it again, and I always get the permission error when I execute as the 'TestCreditRatingUser'
CREATE PROCEDURE TestCreditRatingSP
AS
BEGIN
-- Show who is running the stored procedure
SELECT SYSTEM_USER 'system Login'
, USER AS 'Database Login'
, NAME AS 'Context'
, TYPE
, USAGE
FROM sys.user_token
-- Now get the data
SELECT count(*) from AuditData
END
GO
AuditData is a view that contains:
CREATE view [dbo].[AuditData] as
SELECT * FROM sys.fn_get_audit_file
('F:\QRadarAudit\QRadar_*',default, default)
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply