0

I'm trying to create a history table that stores deleted records as well as user that deleted the record. I know how to store the records but not the user.

The table definition looks like this:

CREATE TABLE HistoryComics(
    Id           INT IDENTITY PRIMARY KEY,
    ComicId       INT,
    Title         NVARCHAR(50),
    Author        NVARCHAR(50),
    [Description]     TEXT,
    Issue         INT,
    Publisher     VARCHAR(20),
    CoverPage     VARBINARY(MAX),
    DateDeleted   DATETIME DEFAULT SYSDATETIME(),
    DeletedBy     VARCHAR(20)
);

I've seen examples of how to do this with MySQL but not with T-SQL

Isaac Opperman
  • 53
  • 8
  • 3
    You really should consider not using the TEXT datatype. It has been deprecated for almost 15 years now. For the question at hand, do you mean the database user? Or do you mean an application user? – Sean Lange Jun 05 '19 at 14:56
  • 1
    The user? The user account associated with the current database connection? – Ross Bush Jun 05 '19 at 14:56
  • 1
    Is this the user with the current database connection? Or is a userID being passed into a stored procedure from an application? – JamesS Jun 05 '19 at 15:04
  • 1
    How is the user connecting the database? Using an service account (such as an applications) or using their own credentials? – Thom A Jun 05 '19 at 15:04
  • [This](https://stackoverflow.com/a/11891352/92546) answer and the comments may offer some helpful information. Note that a `UserId` that is persistent is more useful over time than a name, e.g. `DeletedBy VARCHAR(20)`, that may need to be truncated and may not uniquely identify the user. – HABO Jun 05 '19 at 15:28
  • @HABO I agree mostly, but it also really becomes useless if the user is deleted. Then all you have is a meaningless UserID, or if there is a foreign key you can't delete users because they are in the audit columns. – Sean Lange Jun 05 '19 at 15:33
  • @SeanLange it's a database user – Isaac Opperman Jun 05 '19 at 15:40
  • 1
    You could use [CURRENT_USER](https://learn.microsoft.com/en-us/sql/t-sql/functions/current-user-transact-sql?view=sql-server-2017) or [ORIGINAL_LOGIN](https://learn.microsoft.com/en-us/sql/t-sql/functions/original-login-transact-sql?view=sql-server-2017) – Sean Lange Jun 05 '19 at 15:54
  • 1
    SQL Server is an enterprise database management system. It is used to create systems that manage data. It seems like you are trying to use SQL Server as the application itself. I would avoid piggy-backing on SQL Server's security and create a "user" model yourself that is detached from SQL Server's actual security model. But assuming you have a scenario where users connect directly with SQL Server accounts and execute a procedure to delete a record, then Sean has your answer. Use system functions to get user execute the statement and enter that in your log table. – jamie Jun 05 '19 at 16:05

1 Answers1

1

You should use CURRENT_USER/SUSER_SNAME ( SQL Server 2008+).

It is safe to add default value to your column DeletedBy.

However, be careful, both returns sysname, which means nvarchar(128).

In addition this means that you should enlarge your table definition.

Good starting point on topic 'How to Capture Deleted Records' is my article published on

SQL Server Central.

How to Capture Deleted Records

More info on USER_NAME (Transact-SQL) and SUSER_SNAME (Transact-SQL)