SQL Server to recognize each individual user

Question

SQL Server to recognize each individual user

Vic Rauch-303403

SSCommitted

Points: 1576
More actions
January 24, 2018 at 10:41 am

#402988

What must I do within SQL Server or the SQL Database itself in order for SQL Server to know what user is posting an add, edit, or delete of a record?
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com

Viewing 10 posts - 1 through 9 (of 9 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

What do you actually mean by this? SQL Server can tell you what user and login is being used in the current session by using CURRENT_USER() and SYSTEM_USER() respectively.

Are you attempting to create some kind of audit table perhaps?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

Vic Rauch-303403 - Wednesday, January 24, 2018 10:41 AM
What must I do within SQL Server or the SQL Database itself in order for SQL Server to know what user is posting an add, edit, or delete of a record?

Setup some form of auditing. That's also going to require you to figure out which tables need to be audited and several other things. There will also be caveats depending on the average width of each row and whether or not you have blobs in the tables and what you actually need to record for each action. You may also have to worry about doubling your disk space, how to work around any performance issues that may occur, and possibly design an archive process if you don't need to keep the audit data "forever". You also need to consider that such audit information is totally stagnant once a row is written to the audit table(s) and determine a partitioning scheme (which can also be done even in the standard edition of 2012 using "Partitioned Views" so that a couple of years from now, you're not backing up a terra byte of data that will never change.

It's not a simple subject.

--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)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Also, you have to have a way of identifying the user. This means one of three options. One, everyone has an independent login on the database. Two, everyone is using AD in such a way that you can capture their connection (means, no application or service only connections). Three, you add this as a parameter to all calls to the DB. That's just to ensure you can identify who is doing what.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Vic Rauch-303403 SSCommitted Points: 1576 More actions · Answer 4

Grant Fritchey, that sounds like exactly what I'm trying to do. The boss wants to know who created a record, and who the last one is that updated a record. He is very aware he is not going to get a list of everyone that has updated a record since it was created, but who was the last one to update the record. We have an Access front-end and want to use SQL as the back-end DB. I worked for the same boss at a LARGE company where they had many SQL Server Admins and they would create the database, then I would be able to create tables, views, SP, etc. the DB Admins also did all the security for the DBs. The company we are working for now is quite small, and the SQL DB Admin job falls on me. (Oops). Anyway, that is why this question I have asked.
Option 2 seems to be the way the last company did it. Could you explain what has to be done within the SQL database in order to use each users AD information?
Thanks!

[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

Vic Rauch-303403 - Wednesday, January 24, 2018 1:28 PM
Grant Fritchey, that sounds like exactly what I'm trying to do. The boss wants to know who created a record, and who the last one is that updated a record. He is very aware he is not going to get a list of everyone that has updated a record since it was created, but who was the last one to update the record. We have an Access front-end and want to use SQL as the back-end DB. I worked for the same boss at a LARGE company where they had many SQL Server Admins and they would create the database, then I would be able to create tables, views, SP, etc. the DB Admins also did all the security for the DBs. The company we are working for now is quite small, and the SQL DB Admin job falls on me. (Oops). Anyway, that is why this question I have asked.
Option 2 seems to be the way the last company did it. Could you explain what has to be done within the SQL database in order to use each users AD information?
Thanks!

The easy way is to get access through an Active Directory group. You can then use the connection settings to capture that value. I was thinking you might be dealing with a web app, where things get tougher.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jasona.work SSC Guru Points: 50117 More actions · Answer 6

Vic,
Presuming your server is using Windows Authentication and your front end (which you indicated is MS Access) is being run as the logged in user on the workstation, SQL will "know" the AD login name. You can find it in sys.dm_exec_sessions in the "login_name" field, and split into the domain and login in "nt_domain" and "nt_user_name"

I have an application here where the front-end is MS Access, and to simplify user management, we had the customer get an AD group created for them. SQL still reports the individual login name, rather than the group name.

Now, if anyone is using a connection in Access (bear in mind, I don't develop front ends, I just manage the DB servers,) that uses either a SQL login or an AD "application" login (which is really just a login in the AD used to connect to the SQL Server,) then you'll have to find another way to capture the info you're looking for, or get things changed up so that everyone who uses the application is also using their AD login to connect.

Joe Torre SSChampion Points: 10248 More actions · Answer 7

Are you running SQL Server 2012? If so the best way to track DML changes would be to add columns for validFrom and validUntil, updateSUser. Then add update and delete triggers to write deleted to a history table.

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 8

jasona.work - Wednesday, January 24, 2018 1:45 PM
Vic,
Presuming your server is using Windows Authentication and your front end (which you indicated is MS Access) is being run as the logged in user on the workstation, SQL will "know" the AD login name. You can find it in sys.dm_exec_sessions in the "login_name" field, and split into the domain and login in "nt_domain" and "nt_user_name"
I have an application here where the front-end is MS Access, and to simplify user management, we had the customer get an AD group created for them. SQL still reports the individual login name, rather than the group name.
Now, if anyone is using a connection in Access (bear in mind, I don't develop front ends, I just manage the DB servers,) that uses either a SQL login or an AD "application" login (which is really just a login in the AD used to connect to the SQL Server,) then you'll have to find another way to capture the info you're looking for, or get things changed up so that everyone who uses the application is also using their AD login to connect.

I believe just using ORGINAL_LOGIN() will do it, except for that last case.