June 23, 2021 at 3:32 pm
Hi guys
Setting up a new SQL Server on Azure (Managed Instance). I have three basic requirements in terms of what certain users should be able to access, so I imagine these would be three roles which I could then assign to users?
I expected this to be a lot easier than it seems to be. Would someone be able to walk me through the basics here?
Thanks!
June 24, 2021 at 2:57 pm
My opinion - I would refrain from any "execute all" or "read all" permissions. They are risky. I would operate on a least privilege model and create roles to access specific objects on an as needed basis.
Admin access is dependent on what you mean. Do you mean admin to the instance OR admin to the database? if it is the instance, then sysadmin is the role you are talking about, but I'd use that role VERY sparingly. if it is at the database level, db_owner is the role you are looking for, but again, I'd use that role VERY sparingly. Admin access (sysadmin or db_owner) gives the end user a LOT of permissions that you may not be thinking about like dropping the database or shutting down the instance.
Next, if your permission list is exactly what you want, I would re-do it a bit. I would work with a more descript permission set. What I mean is since permission set 2 and 3 have some overlap, I would make permission set 2 to be JUST reading from tables and views and have permission set 3 just be executing stored procedures. Then when you assign people to the database, you put them in permissions set 3 and permissions set 2 to cover what you suggested for permissions set 2. The reason for this is in the future when you create a 4th permissions set for reading data only (which is useful for things like reporting users), you have that permission set already created as number 2.
Now, as for the "all" part of permission set 2 and 3, I would strongly encourage you to avoid those "all" permissions. The reason being that it is easy to set up, but hard to maintain. Lets say in 5 years someone makes a stored procedure that should only be run after hours due to the duration and resource usage. Since it should only be run after hours, you also want to restrict it so that only a small number of users can run it which should reduce the chance it gets run during company uptime. That becomes a pain if you need to modify all existing objects so that the stored procedure can only be run by the small group of people.
What I would recommend is you work with a least privilege model. So if you have a finance team and an HR team (for example), they likely shouldn't have too much overlap in what they can run or read from the database. And if you have HR data in the database, you may be required to restrict that.
Now if you choose to ignore the above and grant permissions across the board like you suggested, you could do this by creating a new role called something like "ExecuteAll" and granting EXECUTE on the schema (or schemas if you have more than 1) to that role. Next create a role called "SelectAll" and granting SELECT on the schema (or schemas if you have more than 1). Next, you add users to the new roles. For permission set 3, they get added to ExecuteAll. For permission set 2, they get added to ExecuteAll and SelectAll. A user can be a member of multiple roles. I do not recommend going this route as it will very likely lead to additional overhead in the future.
A 3rd option, the least secure option mind you and it assumes that your permission sets listed above are a complete list of permissions on the database, would be to grant execute on the schema to public. This would allow any user with an account on the database to execute any stored procedure. But this is also the least secure option and the biggest pain in the butt to maintain so I would avoid this as much as you possibly can.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 25, 2021 at 3:01 pm
Hi Brian, thanks for your detailed answer. These instances will be accessed by a very limited number of users. Admin access is literally the sysadmin/head DBA, so sysadmin is fine. ‘All’ access will always apply in this case. For granting permissions across the board your example of creating database roles would need to be done within each database, however there are many databases and new databases are added from time to time. What I need here is a way to grant execute permission to a role, or grant select permission to role, *at a server level*. Is this impossible?
June 25, 2021 at 4:13 pm
Lets say in 5 years someone makes a stored procedure that should only be run after hours due to the duration and resource usage. Since it should only be run after hours, you also want to restrict it so that only a small number of users can run it which should reduce the chance it gets run during company uptime.
For such a proc, the proc itself should check the time and verify that it's a valid time for it to be run.
Yes, overall you can still have an issue with limiting specific procs to specific people when using an "all" permission approach, but time of day is not a controlling requirement on those situations.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 25, 2021 at 5:11 pm
Scott - that is a good point. Time of day isn't the best use case for limiting stored procedure execution. A better use case would be for something like modifying HR data. Someone from IT shouldn't be running an HR stored procedure. And if the stored procedure SHOULD be limited by the time of day but isn't restricted in the stored procedure, someone who SHOULD have access could still run it at the wrong time.
Atat - In that case, I would set it up on all existing databases minus master, msdb, and tempdb. You could script out the query and use a CURSOR to build up dynamic SQL to have it run on each existing database. Since it is added to model, all new databases that are created would have those permissions pre-created. That is what the model database is used for - a template for new databases.
Alternately, it could be part of your "new database" script. I have a series of scripts I tend to run on new databases, but they are mostly sanity checks to make sure I didn't do something silly while creating the new database like leaving autogrow set to a percentage or having the database and log files initial size set too small. But I have some for setting up service broker (for example) if the system needs it.
This would go against the best practice (least privilege security model) which is why this isn't a default option, but if it is a requirement at your place of work, that would be a solution.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 25, 2021 at 5:55 pm
What I need here is a way to grant execute permission to a role, or grant select permission to role, *at a server level*. Is this impossible?
Maybe not. If you add that role with those permissions to the model db, it should propagate to every new db you create.
You can use sp_MSforeachdb or a similar cursor to apply permissions to call current, non-system dbs that you need them to apply to.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 20, 2021 at 9:25 am
Thanks guys for all your help! Sorry for the late thanks - I was on leave. Everything works now as needed 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply