We are trying to get PVM setup using our campus' central SQL instance. It is a highly locked down and quite unusual setup. We ran into issues when we tried to configure a multi-user configuration. While we could connect to the data source using a SQL account to create the database and logon to the app (and add AD users), we had to do the following before we could actually get a regular AD user connected to the PVM app.
1) Add the AD\username as SQL Level user with the Public role.
2) Grant the AD\username read/write access at the db level. Since I don't administer the SQL cluster, I do not know the exact "roles" that were granted.
3) Launch the app as that user (and configure the Data Source for Integrated Security).
So here are my questions:
1) Is this normal?
2) Should the application have configured the SQL level rights for the admins, or are we unique in that we did not initially access SQL with an SA account?
3) What are the proper database level access to grant our users in SQL? Is simple read/write access sufficient, or do they need dbo?
If the user launching PVM has the following privileges : Create login, Grant, Deny and can also read from syslogins, then you should be able to perform all user administrative tasks from within PVM.
If you dont have the necessary rights then you must create the users thru SQL Management Studio. You then create the same users in PVM and the system will detect they already exist and simply go ahead with the GRANT / DENY statements.
You shouldn't need dbo to use the system. Read/write permissions in our tables is sufficient.