How to get the permission creation and modification date

Question

How to get the permission creation and modification date

Siqing LU

Say Hey Kid

Points: 665
More actions
December 23, 2021 at 12:19 am

#3966532

Any one can help me to get the SQL database permission creation and modification date? I can only get the database user creation and modifying date.

Thanks

SLu

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Siqing LU wrote:

Any one can help me to get the SQL database permission creation and modification date? I can only get the database user creation and modifying date.
Thanks
SLu

I could be wrong but I can't recall anywhere the " database permission creation and modification date" is stored.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

You CAN get the date that a user was provided access to a database...

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql?view=sql-server-ver15

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Siqing LU Say Hey Kid Points: 665 More actions · Answer 3

Yes, I can get the user creation date, but not the permissions.

Thanks

SLu

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

Grant revoke deny timestamps are not stored anywhere.

If you need that level of information you will need to look at DDL triggers or SQL Audit. But that will only give you future data not the past data.

Siqing LU Say Hey Kid Points: 665 More actions · Answer 5

Siqing LU

Say Hey Kid

Points: 665

December 23, 2021 at 8:05 pm

#3966824

Thanks, ALl

Gibbs Grasshopper Points: 11 More actions · Answer 6

You can modify the date created by copying a file. The file's created date becomes the modified date and the current date when the file is copied becomes the created date. You can copy a file on your PC to check.

IndigoCard

This reply was modified 2 years, 11 months ago by Gibbs.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

Gibbs wrote:

You can modify the date created by copying a file. The file's created date becomes the modified date and the current date when the file is copied becomes the created date. You can copy a file on your PC to check.

But we're not talking about the created and modified dates on a file. We're talking about permissions associated with a user.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)