August 7, 2019 at 10:18 pm
Vendor app is requesting for db_owner role to do some development, when I asked for the reason for db_owner
then they requested the following access for development.
Rights to execute following actions. Table creation, Stored procedure, trigger creation, Views creation and rights to drop, delete and truncate tables. Creating reference
And may be some activities related to db maintaince in Dev environment(which doesn't make any sense to me)
However, I can do this without granting a db_owner role by a more precise method is to use the CREATE permissions. I will also need to grant ALTER on the schema where they can create/alter the objects. So to grant the ability to create procedure and views in the dbo schema. Something like
GRANT ALTER ON SCHEMA::[dbo] TO ;
GRANT CREATE PROCEDURE TO ;
GRANT ALTER VIEW TO ;
So this is DEV, so I could do the above workaround for granting the permissions other than db_owner role. But I would still hate to give all the requested rights to development team of vendor, so i would like minimize as much as i can. I would never give these rights in UAT and PROD.I am sure they would ask for the same in UAT and prod. What would be the better or alternate solution to this. Would DBA needs to run these scripts in order to minimize the permissions? If yes, that would be additional overhead for a DBA. Please advise how to handle this better way?
Thanks in Advance!
August 8, 2019 at 12:03 am
Create a role with the necessary permissions and add their logins to that role. Roles and groups are usually the easiest way to minimize overhead of maintaining user, permissions. Keep a script of that role so that you can rerun it when needed for things such as refresh from prod.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply