Getting UserDetails from Application

• KGNH

  Ten Centuries

  Points: 1087

  More actions

  July 2, 2018 at 10:51 pm

  #372657

  I have to write a procedure where I need to capture some data from application and insert into some tables. Here I also have to get the UserName who is accessing application and done the changes. I have written a procedure to capture the changes. But the user details should come from the application. Here in my table I am having a field UserName to capture the data.

  So While writing the procedure what should I give for Username.
  For inserting the UserName any thing that should be done from the database side or it will be handled only at the application level.

  Currently all the users accessing application are using windows credentials and connecting to database with the same.

  Thanks.

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  July 3, 2018 at 5:11 am

  #1996112

  KGNH - Monday, July 2, 2018 10:51 PM

  I have to write a procedure where I need to capture some data from application and insert into some tables. Here I also have to get the UserName who is accessing application and done the changes. I have written a procedure to capture the changes. But the user details should come from the application. Here in my table I am having a field UserName to capture the data.

  So While writing the procedure what should I give for Username.
  For inserting the UserName any thing that should be done from the database side or it will be handled only at the application level.

  Currently all the users accessing application are using windows credentials and connecting to database with the same.

  Thanks.

  For Application with shared SQL login

  CREATE PROCEDURE foo.bar
  @SomeValue int
, @UserName nvarchar(256)
AS
BEGIN
INSERT INTO SomeTable(UserName, SomeValue)
VALUES (@UserName, @SomeValue));
END;
GO

  For Application with passthrough Windows Credentials
  CREATE PROCEDURE foo.bar
  @SomeValue int
AS
BEGIN
INSERT INTO SomeTable(UserName, SomeValue)
VALUES (SUSER_NAME(), @SomeValue));
END;
GO

  ---

  How to post data/code on a forum to get the best help.

• KGNH

  Ten Centuries

  Points: 1087

  More actions

  July 3, 2018 at 5:28 am

  #1996113

  DesNorton - Tuesday, July 3, 2018 5:11 AM

  KGNH - Monday, July 2, 2018 10:51 PM

  I have to write a procedure where I need to capture some data from application and insert into some tables. Here I also have to get the UserName who is accessing application and done the changes. I have written a procedure to capture the changes. But the user details should come from the application. Here in my table I am having a field UserName to capture the data.

  So While writing the procedure what should I give for Username.
  For inserting the UserName any thing that should be done from the database side or it will be handled only at the application level.

  Currently all the users accessing application are using windows credentials and connecting to database with the same.

  Thanks.

  For Application with shared SQL login

  CREATE PROCEDURE foo.bar
  @SomeValue int
, @UserName nvarchar(256)
AS
BEGIN
INSERT INTO SomeTable(UserName, SomeValue)
VALUES (@UserName, @SomeValue));
END;
GO

  For Application with passthrough Windows Credentials
  CREATE PROCEDURE foo.bar
  @SomeValue int
AS
BEGIN
INSERT INTO SomeTable(UserName, SomeValue)
VALUES (SUSER_NAME(), @SomeValue));
END;
GO

  Thank you Des. So if we use SUSER_NAME() then user logged into application can be directly retrieved? Also no need to use any parameters for getting the user name?

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  July 3, 2018 at 5:37 am

  #1996115

  KGNH - Tuesday, July 3, 2018 5:28 AM

  Thank you Des. So if we use SUSER_NAME() then user logged into application can be directly retrieved? Also no need to use any parameters for getting the user name?

  IF the user uses Windows Credentials to log into the application, AND those Windows Credentials are used by the application to connect to the SQL DB, then that is correct.
  However, very few applications use such a model.

  You will have to test your application to see how it works

  ---

  How to post data/code on a forum to get the best help.

• KGNH

  Ten Centuries

  Points: 1087

  More actions

  July 3, 2018 at 6:28 am

  #1996124

  DesNorton - Tuesday, July 3, 2018 5:37 AM

  KGNH - Tuesday, July 3, 2018 5:28 AM

  Thank you Des. So if we use SUSER_NAME() then user logged into application can be directly retrieved? Also no need to use any parameters for getting the user name?

  IF the user uses Windows Credentials to log into the application, AND those Windows Credentials are used by the application to connect to the SQL DB, then that is correct.
  However, very few applications use such a model.

  You will have to test your application to see how it works

  Thank you Des.

• KGNH

  Ten Centuries

  Points: 1087

  More actions

  July 4, 2018 at 3:28 am

  #1996263

  DesNorton - Tuesday, July 3, 2018 5:37 AM

  KGNH - Tuesday, July 3, 2018 5:28 AM

  Thank you Des. So if we use SUSER_NAME() then user logged into application can be directly retrieved? Also no need to use any parameters for getting the user name?

  IF the user uses Windows Credentials to log into the application, AND those Windows Credentials are used by the application to connect to the SQL DB, then that is correct.
  However, very few applications use such a model.

  You will have to test your application to see how it works

  Hi Des,

  Can you please also suggest how can we get that when the users uses a service account or common connection to connect to the database.

  Thanks

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  July 4, 2018 at 4:19 am

  #1996273

  KGNH - Wednesday, July 4, 2018 3:28 AM

  DesNorton - Tuesday, July 3, 2018 5:37 AM

  KGNH - Tuesday, July 3, 2018 5:28 AM

  Thank you Des. So if we use SUSER_NAME() then user logged into application can be directly retrieved? Also no need to use any parameters for getting the user name?

  IF the user uses Windows Credentials to log into the application, AND those Windows Credentials are used by the application to connect to the SQL DB, then that is correct.
  However, very few applications use such a model.

  You will have to test your application to see how it works

  Hi Des,

  Can you please also suggest how can we get that when the users uses a service account or common connection to connect to the database.

  Thanks

  Then your application must figure out the User details and pass it to the stored procedure.
  See my first example in this post

  ---

  How to post data/code on a forum to get the best help.

• KGNH

  Ten Centuries

  Points: 1087

  More actions

  July 4, 2018 at 5:09 am

  #1996278

  DesNorton - Wednesday, July 4, 2018 4:19 AM

  KGNH - Wednesday, July 4, 2018 3:28 AM

  DesNorton - Tuesday, July 3, 2018 5:37 AM

  KGNH - Tuesday, July 3, 2018 5:28 AM

  Thank you Des. So if we use SUSER_NAME() then user logged into application can be directly retrieved? Also no need to use any parameters for getting the user name?

  IF the user uses Windows Credentials to log into the application, AND those Windows Credentials are used by the application to connect to the SQL DB, then that is correct.
  However, very few applications use such a model.

  You will have to test your application to see how it works

  Hi Des,

  Can you please also suggest how can we get that when the users uses a service account or common connection to connect to the database.

  Thanks

  Then your application must figure out the User details and pass it to the stored procedure.
  See my first example in this post

  Thank you very mush Des.