Alternatives to granting users Database Owner

Did you create the users via RDM?

When we create users via RDM we create a Login + User then we grant:

GRANT SELECT, INSERT, DELETE, UPDATE TO [...];Depending on the user privileges we will revoke some a few actions on a subset of tables.

No db_owner should be required.

Regards,

We are using Active Directory to import users.

Using a script? You are creating them directly into the RDM database?

No Script. The process has been RDM > Administration Tab > Users > Add User > Integrated security (AD) checked > Create SQL Server Login and User unchecked.

Are you saying that if we check off “Create SQL Server Login and User” then RDM will create the SQL login without having to manage it from the actual SQL Server?

Exactly

Thanks, Stefane. That resolved the issue that we were having. Just to follow up, so is the RDM DB is encrypted? Hypothetically speaking, what would happen if one of our the DBOs were to take a copy of the RDM database. Can they open it and pull out passwords? We are currently testing PVM and RDM, if that is relevant.

By default passwords are always encrypted (decryptable by RDM/PVM only).

So in theory it would be possible to copy-restore the DB then use RDM to open up the copy of the DB and see the passwords using RDM/PVM.

For more security (double encryption) I would suggest you have a look at the security providers (see link below) more precisely the Shared passphrase or Certificate providers. They require user input (passphares or cert.) to be able to decryption the data. This way as long as the DBO doesn't have access to that "user input" then there is no way to get to the data.

Don't forget the passphrase or lose the certificate, once you do, you won't be able to decrypt your data (ever).

http://help.remotedesktopmanager.com/index.html?administration_securityproviders.htm