I created a Stored Procedure that performs the Following tasks:
- Creates database
- Change owner to sa
- Set recovery model
- Creates users in database
- Adds users to database roles.
- Grants execute and view definition to users
I then created a certificate, Login from cert, user from cert, granted exec on SP to user, granted control server to login, added signature to sp by certificate.
When non sysadmins execute the SP everything works except for adding users to the database roles with error: Cannot alter the role ‘db_datareader’, because it does not exist or you do not have permission.
If I add the login to the sysadmin server role, when non sysadmins execute the SP it will work. Ideally the login would have minimal permissions to perform these functions. Is it possible to do this w/o granting sysadmin to the user?