November 28, 2011 at 1:23 pm
Hello. Some of our users write SPs in their sandboxes that subsequently are used in SSRS and other reports. We're now developing a couple new DBs with schema and ETL that we admins control. I was trying to see if we could still allow them to write and execute procs for their reports but don't think this can be done safely.
Given a user in the DB ReportUser:
sp_addrolemember db_datareader, ReportUser
sp_addrolemember db_dlladmin, ReportUser
sp_addrolemember db_denydatawriter, ReportUser
Grant Execute To ReportUser
This is a lot of permissions :crazy: They can now create an SP that deletes or even drops tables. Can this be avoided? Or maybe they can be allowed to either write or execute SPs, not both.
Thanks,
Ken
November 28, 2011 at 1:30 pm
Executing a procedure is allowed on a one-by-one basis of the procedure.
You can give explicit CREATE PROCEDURE permissions in 2k8, but they need ALTER permissions on the schema they're going to create it in. The best method to deal with this is to create a schema (reportUsers) that they're going to create their procedures in, and give them (and the owner of that schema) data_reader in the dbo schema.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 28, 2011 at 2:10 pm
Thanks a lot. I think this does the trick.
CREATE ROLE rlReportWriter
CREATE USER ReportUser FROM LOGIN test
CREATE SCHEMA ReportWriter Authorization ReportUser
EXEC sp_addrolemember db_datareader, rlReportWriter
EXEC sp_addrolemember rlReportWriter, ReportUser
GRANT CREATE PROCEDURE TO rlReportWriter
GRANT EXECUTE TO rlReportWriter
They can create in their ReportWriter schema but nothing that modifies or deletes dbo data.
Ken
November 28, 2011 at 2:28 pm
You might want to do an explicit denydatawriter to make sure that's fully covered.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 28, 2011 at 2:33 pm
Another thing you can probably do is write a DDL trigger that tracks stored procedure creation and alteration, and which blocks creating them if they have "update" or "delete" or even "insert" in them, if they are written by members of your report user group.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 28, 2011 at 2:38 pm
GSquared (11/28/2011)
Another thing you can probably do is write a DDL trigger that tracks stored procedure creation and alteration, and which blocks creating them if they have "update" or "delete" or even "insert" in them, if they are written by members of your report user group.
That's pretty fancy but I'll look into it.
Thanks,
Ken
November 28, 2011 at 9:58 pm
I can see the report developers writing the stored procedures in their sandbox environments on their local boxes, but they probably shouldn't be the ones moving those into production. That should be handled by the DBA(s). That way they don't need permissions to create or alter procedures.
December 2, 2011 at 12:38 am
@mikejean17: I can ignore SPAM but not copying to Lynn. 😛
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply