Urgent help about Sp permission

Question

Urgent help about Sp permission

only4mithunc

SSCrazy Eights

Points: 8434
More actions
January 19, 2010 at 5:52 am

#209241

Hi friends,
Can some one let me know is it possible to get the date and time on which Execute/View permission is given to a user/group for a Stored Procedure.
Say, I have created one SP yesterday , so if I check the create and dmodified date in sys.objects both will be same. Assume Im giving/modifying the above permissions today, in this case is there any way to see the date on which I gave/modified the permission?
Thanks,
MC
Thanks & Regards,
MC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

If I understand your request correctly you can query the Default Trace (if you haven't disabled it) for the Audit Schema Object GDR Event to find when a user or role is granted access to an object.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Hi many thanks for reply.

I'm not that experienced in SQL ,I think the below script is the basic to get the default trace.

SELECT * FROM fn_trace_getinfo(default)

Could you please help me how to make it as you mentioned below.

Thanks,

Regards.

Thanks & Regards,
MC

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Here is the way I query the default trace:

SELECT

TE.[name],

I.*

FROM

sys.traces T CROSS Apply

sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0

THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'

ELSE T.[path]

End, T.max_files) I JOIN

sys.trace_events AS TE ON

I.EventClass = TE.trace_event_id

WHERE

T.is_default = 1

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

Hi Jack....

Many thanks...!!!! it really helped me... hope it is a very good knowledge for others too...

Once again thanks for your prompt reply...

Regards,

MC

Thanks & Regards,
MC

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

Jack,

Just to clarify on more thing,

I need to try this in production database , so before executing ( I'm not sure whether the default trace is enabled or disable over there..)

so as you told if the default trace is disabled.. do we have any other go to meet the requirement..?

Regards,

MC

Thanks & Regards,
MC