Login and auto-signing

[DanT]

You're working with a SQL database which has a very fine 2-level security system using logins on server level and user rights on the database level. You can use them to set the rights per object, even to the row (using views) /column level. You have there standard built-in functions and variables to get the current user for example: select system_user gets the logged in windows user.
And tracking is standard available on the database, just activate it:

Brilliant! I managed to set up login for myself in SQL Server but it didn't change much when it comes to playing with database via Access. Would you kindly point out how to set it up so this affects Access as well?

[NoellaG]

Hi,

you can create on sql server a table-valued function that returns all info from the current session:

USE [MyDatabase]
GO


/****** Object:  UserDefinedFunction [dbo].[f_getSessionInfo]    Script Date: 6/09/2022 10:14:37 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        NGA
-- Create date: 
-- Description:    Example
-- =============================================
CREATE FUNCTION [dbo].[f_getSessionInfo] 
(    
    
)
RETURNS TABLE 
AS
RETURN 
(
    select session_id, login_name , login_time, host_name, program_name, client_interface_name, nt_domain, nt_user_name
    from sys.dm_exec_sessions where session_id = @@SPID
)
GO

Using a command object in Access you can use the recordset it produces
you can even link the view sys_dm_exec_sessions to your access database, but then you have the info of every session on your database
In the SSMS the command:
select * from [dbo].[f_getSessionInfo]()
returns: