user server role

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

Answer 1

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.

Answer 2

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

Answer 3

Many thanks Albert

that is the answer I was looking for.

Marco Dell'Oca

Answer 4

Hi Albert,

Thanks for the assist. Cheers!