SQL server Permission

Question

SQL server Permission

pamalatha

Old Hand

Points: 377
More actions
October 4, 2011 at 11:45 am

#244146

Somebody is keep changing the user permission in the database. I was able to create the trigger, and I capture only Creating, alter or dropping users. But I could not able to find change of permission for users.
at the same time I could able to see they is login change by running the following query:
SELECT * FROM sys.server_principals sp
order by sp.modify_date desc
I donot know who modified and to which database.
Any body could help me?
Thank you
Padma

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

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

SQLArcher SSC Eights! Points: 950 More actions · Answer 1

Hi,

The easiest way to find this out is to check out the default trace. The catch however is to do this as quickly as possible after the permissions changed due to the trace file being only 20MB.

First, get the location where the trace is located:

SELECT * FROM ::fn_trace_getinfo(default)

After this you can run the following code in SQL;

SELECT * FROM ::fn_trace_gettable ('default trace path', NULL)

You can opt to open the trace file with Profiler as well, but to use SMSS is easier in my opinion and it's easier to sort the data or to query specific columns.

Another way to do this is to use database auditing if you are using SQL Server 2008+ to get the info you need.

Regards...

pamalatha Old Hand Points: 377 More actions · Answer 2

Hi,

I really appreciate your help.

I was able to locate the trace file and browsing through it... I have some clues. Something better than nothing.

But I was not able to see the TextData for all the events. Do you know what it is?. IS that possible to see more information.

Thanks a lot.

Padma

SQLArcher SSC Eights! Points: 950 More actions · Answer 3

Hi,

Traces is not very clear on this matter, the best suggestion that I can think of, and find is to do the following:

SELECT

e.name,

t.starttime,

t.DatabaseName,

objectname,

LoginName,

t.HostName,

t.ApplicationName,

t.spid,

t.eventclass,

t.textdata

FROM

::FN_TRACE_GETTABLE('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_34.trc',NULL) AS t

JOIN sys.trace_events e ON e.trace_event_id = t.EventClass

WHERE

t.EventClass = 109 -- object altered

OR t.targetloginname = 'AFFECTED USER'

Other than this, I can only think of auditing and the way to do this is:

/*Capturing user auditing*/

/*Step 1 creates a location to store the events, as well as the auditing specification. Step 2 creates a specification just for individual databases*/

--Step 1

USE [master];

GO

CREATE SERVER AUDIT [security]

TO FILE

(FILEPATH = N'FILEPATH\'

,MAXSIZE = 40 MB --for testing purposes

,MAX_ROLLOVER_FILES = 5

,RESERVE_DISK_SPACE = OFF

)

WITH

(QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

,AUDIT_GUID = '6d2ad6d4-acde-480f-9983-6d91b7f5f974'

);

GO

--Step 2

USE [database];

GO

CREATE DATABASE AUDIT SPECIFICATION [user_permission_changes]

FOR SERVER AUDIT [security]

ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),

ADD (DATABASE_PERMISSION_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_ACCESS_GROUP)

WITH (STATE = ON)

GO

To select from the audit specification file execute the following:

SELECT * FROM sys.fn_get_audit_file('FILEPATH',DEFAULT,DEFAULT)

This will provide you with the information you require.

You can read more on:

http://msdn.microsoft.com/en-us/library/cc280386.aspx

http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

Regards...

pamalatha Old Hand Points: 377 More actions · Answer 4

Hi,

Thank you so much... I tested the query filter with eventclass workes great. I have created Audit, and see how that goes.. Any way you gave me lot of options. Every one of them seems to be very helpfull..

Regards

Padma