none
user server role RRS feed

  • Question

  • With Vba or with Api can I know the user server role?

    If the user is <SA> I am sure is an administrator, bat if the name is different?

    Thanks

    Marco Dell’Oca

    Tuesday, September 26, 2017 11:11 AM

Answers

  • You certainly can create a PT query in Access, and then paste in this:

    	SELECT u.name as CUser, r.name as role
    	  FROM sys.database_role_members AS m
    	  INNER JOIN sys.database_principals AS r
    	  ON m.role_principal_id = r.principal_id
    	  INNER JOIN sys.database_principals AS u
    	  ON u.principal_id = m.member_principal_id
    	  WHERE u.name = SESSION_USER

    The above will return this

    CUser    role
    Albert     db_datareader
    Albert     db_datawriter

    You can also "test" or check for membership in a role, but that was not your question.

    Regards

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada



    Wednesday, September 27, 2017 5:59 AM

All replies

  • Hi Marco. Can't test this right now but you might be able to execute a query against the system tables to get the user permissions.
    Tuesday, September 26, 2017 1:24 PM
  • You certainly can create a PT query in Access, and then paste in this:

    	SELECT u.name as CUser, r.name as role
    	  FROM sys.database_role_members AS m
    	  INNER JOIN sys.database_principals AS r
    	  ON m.role_principal_id = r.principal_id
    	  INNER JOIN sys.database_principals AS u
    	  ON u.principal_id = m.member_principal_id
    	  WHERE u.name = SESSION_USER

    The above will return this

    CUser    role
    Albert     db_datareader
    Albert     db_datawriter

    You can also "test" or check for membership in a role, but that was not your question.

    Regards

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada



    Wednesday, September 27, 2017 5:59 AM
  • Many thanks Albert

    that is the answer I was looking for.

    Marco Dell'Oca

    Thursday, September 28, 2017 2:32 PM
  • Hi Albert,

    Thanks for the assist. Cheers!

    Thursday, September 28, 2017 3:29 PM