June 6, 2019 at 3:08 pm
Is there a way to keep track of who is changing permission for one of the login our web is using? That login has owner rights and time to time, we run into issues where that login no longer has owner rights. But who is causing it?
I created a user, granted that user with owner rights on one of the dev server, revoked permissions but no information is getting captured. I have tried these scripts which I found on google.
USE MASTER
GO
-- Create the server audit
CREATE SERVER AUDIT login_perm_audit
TO FILE ( FILEPATH ='E:\Trace\' );
GO
-- Enable the server audit
ALTER SERVER AUDIT login_perm_audit
WITH (STATE = ON) ;
GO
SELECT * FROM sys.fn_get_audit_file ('E:\Trace\login_perm_audit_795CD6C5-E565-473D-A98A-E697A0068BCA_0_132041485360530000.sqlaudit',default,default);
GO
CREATE TRIGGER DDL_AUDIT_Logins ON ALL SERVER
FOR DDL_DATABASE_SECURITY_EVENTS AS
SET NOCOUNT ON;
--create table EventsTable (
-- EType NVARCHAR(max)
-- ,EObject VARCHAR(100)
-- ,EDate DATETIME
-- ,EUser VARCHAR(100)
-- ,ECommand NVARCHAR(max)
-- );
DECLARE @EType NVARCHAR(max);
DECLARE @ESchema NVARCHAR(max);
DECLARE @DBName VARCHAR(100);
DECLARE @Subject VARCHAR(200);
DECLARE @EObject VARCHAR(100);
DECLARE @EObjectType VARCHAR(100);
DECLARE @EMessage NVARCHAR(max);
DECLARE @ETSQL NVARCHAR(max);
SELECT @EType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',
'nvarchar(max)')
,@ESchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',
'nvarchar(max)')
,@EObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
'nvarchar(max)')
,@EObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(max)')
,@DBName = EVENTDATA().value('
(/EVENT_INSTANCE/DatabaseName)[1]',
'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)');
INSERT INTO EventsTable
SELECT @EType
,@EObject
,GETDATE()
,SUSER_SNAME()
,@ETSQL;
select * from EventsTable
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
June 6, 2019 at 7:18 pm
The script you posted for the DDL trigger will work if the database owner is changed. In that case, the issue could be where you created the Events table. Just based on what you posted, the Events table would need to be in master. You also have a database audit mixed in there for some reason.
There is no such thing as "granting owner rights" so the other possibility is that the you added the user to the db_owner role. In that case you would want to use a database scoped ddl trigger. And if you did a bunch of grants and that's what you mean by granting owner rights you would again need a database scoped ddl trigger.
Sue
June 7, 2019 at 12:42 am
Your audit won't work because while you created the first part of the audit (where to save the information) you don't create the second part of the audit (the information to capture.)
You'll probably want to add the "DATABASE_OWNERSHIP_CHANGE_GROUP" audit action group which will catch when and who changes the owner of the database. If you also suspect that the permissions of the user account are being changed in the database, you'll want to also add the "DATABASE_PERMISSION_CHANGE_GROUP," "DATABASE_ROLE_MEMBER_CHANGE_GROUP," and "DATABASE_PRINCIPAL_CHANGE_GROUP" audit action groups.
See the MS pages for more on this:
June 11, 2019 at 10:27 pm
Try server level DDL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply