Report to show id's that have no access?

[tjruska]

I am trying to free up some licenses by identifying any id that has no access to any repository at all and thus make them inactive. Is there any easier way (outside of manually checking each id's access in Vault) or SQL query available that would provide this?
We are on Version 4.1.4.18402.

Thanks, Tom

[lbauer]

We don't have a query handy, but the information is in the sgvault database in the dbo.tblrepositoryaccess table.

If you run

Code: Select all

select * from sgvault.tblrepositoryaccess

you'll get a list with the repository ID, userID number, and repositorypermissions. The permissions are either F (Full Admin) or N (No access).

If you have 10 repositories and see a user ID listed 10 times with No Access, then that user has no access to any repositories.

To find the repository name, run this:

Code: Select all

select * from sgvault.dbo.tblrepositories

For user id:

Code: Select all

select * from sgmaster.dbo.users

Note: for safety, backup your databases before running queries on your databases.