I have a user where I have granted the following roles:
How do I grant this user the ability to CREATE and ALTER store procedures? However, I would like to deny this user from the ability to CREATE or ALTER tables, views, etc...
Thanks
did you try Grant Create Procedure to .....
is this user a developer? I stay away from giving individual users specific permissions
Yes, this user is a developer.
do you have a developer AD Group? or maybe create a SQL Role and then add developer in there?
When you say a SQL Role, are you referring similarly to the DB_Executor that I have created? If yes, can I do this for the Deny of creating and altering tables too?
Deny alter and create table to the developer?
Yes because this developer is a report developer and only needs to CREATE/ALTER SP to read from tables. SPs are then bind to SSRS reports. Don't want this developer to have database admin privileges.
That's a bit too much isnt it? This individual is a developer. And is this restriction in production? If so why are you even given this dev proc change in production. Are they allowed to make changes of procs in prod? Then you have bigger problems in your process.
What exactly does your development and deployment process look like?
Let me know is your issue got solved or not.
Not yet.
This one is a small shop. It is complicated to explain.
fair enough. sometimes there are things we cannot control.
I would recommend you give 0 perms to a dev in prod. 1 DB Admin in prod.
Have a preprod (dev,qa) instance of both the database(s) and SSRS report.
Dev deploys anytime to preprod, qa test the report. then when all looks good deploy to prod.
For this small company, if there are separate PROD and DEV environments, then I wouldn't be posting this question. Unfortunately, that is what we have here and I am making the best out of what they have. It is just me and another position that comes in and out.
How would we know we dont work there. We cant assume. Do you review his code?
dev could write a sproc that has a section that gives him all perms, or drops the whole database or ....
REVOKE ALTER ON dbo.Customers TO [babyqua/dbnowrights];
But then again, you have to check what other permissions
developer was given when it was first created in db .
Maybe someone before you gave him all permissions etc.
-- Create a db_executor role
CREATE ROLE db_executor
-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor
What if the developer needed to create a lookup table - or build a small data mart from production tables? If they cannot create any tables/views then they cannot optimize any of their code using smaller lookup tables when needed.
If the issue is related to access to the production data - then limit the developer to a specific schema that you create for them. They can then create anything needed in that schema - but only have read access to anything outside that schema.
CREATE SCHEMA dev AUTHORIZATION dbo;
Then you could do this:
GRANT SELECT,EXECUTE ON schema::dbo TO {developer};
GRANT ... ON schema::dev TO {developer};
But - do not grant ALTER to the schema...you should only grant DELETE, EXECUTE, INSERT, SELECT, UPDATE and VIEW DEFINITION. That should be enough to allow the developer to create procedures, functions, tables, views in the dev schema - and access tables/views/procedures/functions in the dbo schema.
Set the developers user default schema to the dev schema. This will also force the developer to schema qualify all code as by default SQL Server will then look to the dev schema for the object.
BTW - the developer should NOT have write access to the production schema...at all. They should not be able to write any code in production - or execute any code - that directly updates production tables. Especially since this developer is a report developer...
The other option (which is one I prefer) is to create a 'Reports' database where the developer(s) have the ability to create what they need - and their user only has db_datareader access to the production database(s). This separates their code from the production database completely...
Jeff, great input! They do not have a dev anything. This all happens in production
That is what I figured...so either separate the developer out to their own database or create a separate schema where they can create their objects.
hope they do have a solid back up system in place.
disgruntled employee could deploy a proc that could add
DROP TABLE Prodsystems.dbo.Orders
in the proc they deploy to the db they have perms in.
Next SSRS run time, scramble drill.
This is what I have been trying to convince my boss but doesn't seem to get the concept because developers come and go here.