Granting Permissions

SQL Server Administration
21

install sql developer version on each of the dev's development machine.
enabled SSRS there.
create all the dbs necessary with minimal data restored from prod, obfuscated
deploy new sprocs to dev's development machine. check SSRS on dev. once dev feels it is good, they hand it over to you for code review. if all is good, they hand it to you to deploy or push it to gitlab for CICD or some hot folder drop location that is automated using powershell that deployes it to prod.

Option 5: get a new job where you are heard and valued

1 Like
22

Let's assume the production database name is 'Prod'. If the user only has db_datareader permissions on the dbo schema - then how could they build a procedure that can drop a table?

Ideally - create a reports database...the developer has permissions in that database and if they drop something it will only impact what they have developed (and any other developers - of course). They still would not have access in the Prod database other than db_datareader - so the only thing they can do is read data from Prod.

Now - if the user executing the procedure has elevated permissions then yeah...it could do that. But - to avoid that you setup SSRS to use a dedicated SQL account that only has db_datareader in Prod and Execute permissions in Reports. In other words...no one can execute the procedure in the Reports database with elevated rights...

If you cannot create another database - then your developer still cannot modify anything in the production schema directly if you do not grant them those permissions. Even if they create a procedure that has code to drop a table - it will only work if the login/user executing that code has elevated permissions and the procedure is executed with those credentials (which won't happen by default).

The best option would be a separate system...but still utilize a separate database specifically for your reporting requirements, grant the developer db_datareader, db_datawriter, ddl_admin and view_definition in that database - and only grant db_datareader (or a subset if preferred) to the Prod database.

No matter what - do not allow SSRS to use a privileged account to run reports. That account should be limited to only executing procedures in the reports database - with db_datareader access to the Prod database.

1 Like
23

I think the permissions below are what you'd want to give. The ALTER SCHEMA would allow the user to read any data, but I don't believe he/she would be able to CREATE or ALTER a table without explicit permission for that too. Naturally you'd want to test that out.

GRANT ALTER ON SCHEMA::[dbo] TO [<user_name>];
GRANT VIEW ANY DEFINITION TO [<user_name>];
GRANT CREATE PROCEDURE TO [<user_name>];
1 Like
24

I am talking about the service account running reporting services not the dev. After the deployment.

And you honestly think the boss is going to grant, prod db read? I would definitely not.

25

Consider that the OP stated:

I have a user where I have granted the following roles:

  • DB_DataReader
  • DB_DataWriter
  • DB_Executor (for SP)

Yeah, it's safe to say that this user will have read authority on the db.

1 Like