none
How to get UserName from java.sql.Connection?

    Question

  • I have a data source for SQLServer created in Weblogic with username 'sa'.

    In code I am using following to get user name.

    Context ctx = new InitialContext(prop);
    Object obj = ctx.lookup("sqlserver1");
    System.out.println("Data Source Found….");
    DataSource ds = (DataSource) obj;
    Connection conn = ds.getConnection();
    DatabaseMetaData mtdt = conn.getMetaData();
    // Get UserName
    System.out.println("User name: " + mtdt.getUserName());

    But above code always returns 'dbo' as the username. I expected the username to be 'sa'.

    Monday, February 04, 2013 6:09 PM

Answers

  • The 'sa' account is a loginname, but is never a username.

    If the 'sa' login owns the database, it will be known in the database as 'dbo'.  Further, the username for the owner of the database, no matter which login is used, is 'dbo'.   Also, the 'sa' login cannot be added to the database as an 'sa' user. 

    If you use SSMS to show the properties of the 'dbo' user, you may see that the login is 'sa'.  So, what you are after is the login, not the user.

    I do not use Weblogic, but it seems likely that GetUserPrincipal is more what you are after.  To get a login name for the dbo in T-SQL, you could issue the query.

    SELECT l.name AS Login
    FROM sys.server_principals AS l
      JOIN sys.database_principals as U
         ON l.SID = U.SID
         WHERE u.name = 'dbo'

    This will find the login in server_principals that is 'dbo' in database_principals.  You can use the getUserName() of course to feed the query, if that is necessary.

    Of course, you can use the same logic (without = 'dbo')  to look up any other user whose login name may be different from the user.

    RLF

    PS - I understand that you are using Weblogic with Microsoft SQL Server.





    Monday, February 04, 2013 10:07 PM
  • Yes, if you are logging in as 'sa' then SUSER_SNAME() will work for you.  (Of course, if you are logging in as 'sa' you do not need to find out who you are.)

    But if you are logging in as some other login and just want to know which login is the 'dbo' then you can use my earlier query, or you can:

    DECLARE @sid VARBINARY(85);
     
    SELECT @sid = SID 
    FROM sys.database_principals
    WHERE principal_id = 1;
     
    SELECT SUSER_SNAME(@sid);
     
    RLF
    Tuesday, February 05, 2013 1:26 PM

All replies

  • The 'sa' account is a loginname, but is never a username.

    If the 'sa' login owns the database, it will be known in the database as 'dbo'.  Further, the username for the owner of the database, no matter which login is used, is 'dbo'.   Also, the 'sa' login cannot be added to the database as an 'sa' user. 

    If you use SSMS to show the properties of the 'dbo' user, you may see that the login is 'sa'.  So, what you are after is the login, not the user.

    I do not use Weblogic, but it seems likely that GetUserPrincipal is more what you are after.  To get a login name for the dbo in T-SQL, you could issue the query.

    SELECT l.name AS Login
    FROM sys.server_principals AS l
      JOIN sys.database_principals as U
         ON l.SID = U.SID
         WHERE u.name = 'dbo'

    This will find the login in server_principals that is 'dbo' in database_principals.  You can use the getUserName() of course to feed the query, if that is necessary.

    Of course, you can use the same logic (without = 'dbo')  to look up any other user whose login name may be different from the user.

    RLF

    PS - I understand that you are using Weblogic with Microsoft SQL Server.





    Monday, February 04, 2013 10:07 PM
  • Hello,

    dbo is the name of the default schema assigned to a view,table,... But sa ( which is a super administrator of the the SQL Server instance ) is often linked to the dbo schema ( which is the schema name which is assigned when a table,view,... has been created with no specific schema name.

    Which is the exact version of your java.sql.connection ? ( and the manufacturer ( Microsoft ? )

    Which is the version of your SQL Server ( 2005,2008,2008 R2,2012 + last installed servicepack ) ?

    http://msdn.microsoft.com/en-us/library/ms378022.aspx : not too clear for me ( but jdbc is far to be my usual provider )

    I have looked at http://msdn.microsoft.com/en-us/library/ms378167.aspx 

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Monday, February 04, 2013 10:09 PM
  • Thanks.

    ok 'dbo' and 'sa' are different. I think can get Connection and execute 

    SELECT SUSER_NAME()
    to get the login name for current Connection.
    Tuesday, February 05, 2013 4:25 AM
  • Yes, if you are logging in as 'sa' then SUSER_SNAME() will work for you.  (Of course, if you are logging in as 'sa' you do not need to find out who you are.)

    But if you are logging in as some other login and just want to know which login is the 'dbo' then you can use my earlier query, or you can:

    DECLARE @sid VARBINARY(85);
     
    SELECT @sid = SID 
    FROM sys.database_principals
    WHERE principal_id = 1;
     
    SELECT SUSER_SNAME(@sid);
     
    RLF
    Tuesday, February 05, 2013 1:26 PM