In general, I have concepts on how to secure a database.
What are your normal best practices?
For example, there was a database that was handed to me where they assigned single window authentication accounts, rather than groups. I understand that is a no no! We have created AD groups and assigned accounts to these groups instead. Then we give the permission by groups.
As a custodian of the data you are responsible to know who has access to the data, unless you are the only people who can amend the AD groups then this can actually be a less safe method as any domain admin or other delegated user can add and remove users to the AD groups.
Unless you are fully auditing the membership of the AD groups you can lose sight of who has access to the data.
and if it is super sensitive data (hippa related, bank etc) come audit time your head is on the chopping block. none of our users have direct access to db not even using groups, they dont need to query the data that way. we restrict things by using dedicated svc accounts. if they want data they can use ssrs. harsh but they are ok with it
Thanks Stephen and yosiasz. Yes yosiasz, I am in that situation.
you are in the situation where people run queries directly against the database(s)?
Unfortunately, correct Yosiasz. BTW... What are SVC accounts?
You said if they want data, they use SSRS. But doesn't SSRS still pass the Window Authentication to SQL Server using Window Authentication? Or do you have the users use Window Authentication on the SSRS reporting tier and then calls a SQL Authentication account?
As of right now, I am planning to change to Window Authentication groups on the Reporting Tier and Window Authentication groups on the Database tier.
service accounts.
you can have a dedicated service account for SSRS data connections, then give permission to the reports or report folders to Window Authentication groups.
The dedicated service account will be the one making the call to the database(s) in behalf of the Window Authentication groups.
Others can chime in on the approach they use, but this has worked out nicely for us working along with our Sys Admin.
Also remember people are allergic to change, whatever you do guide them dont berate them. Show them that there other possibilities without making them feel incompetent. soft skills, people skills go a long way.
Hi Yosiasz,
"Also remember people are allergic to change, whatever you do guide them dont berate them. Show them that there other possibilities without making them feel incompetent. soft skills, people skills go a long way."
I can tell that you are seasonal, professional and have been doing this for a long time. Not just from the above but this has been your practices from your replies with my other posts.
The service account is good but do you grant or do anything special to your service accounts? I would think that they only need to have db_DataReader role and grant Execute to that service account right? I am interested in understanding if you do anything special to the newly created service account in terms of configuration and permissions.
Also, how would you handle for developers? I would think that for developers, they would have access to the DEV database, of course not production database. I am curious.
Thank you Yosiasz.
Well, it is a process. When I was young I tended to be cocky and hard headed thinking my way was best, not because I felt I was better but because I wanted what I felt was best practice. But now I am old and cranky 
But at the end of the day, software is an always changing thing, here today gone tomorrow. but the one constant is people. Today you have SSRS and tomorrow it could be gone, don't get hung up on a specific way of doing thing even if you honestly feel it is accepted best practice. Suggest kindly, work out the politics respectfully and you will gain allies now and any future recommendations you make.
Yes I give the service account read only on the data (this is where a datawarehouse/data mart/mini mart) might be important implementation so that you don't hammer your OLTP, but that is another topic) and execute on the stored procedure that fetch the data. that is it. Now I do not use this svc account (svc-SQLreports) for anything else, especially not as the SQL Service account.
Developers should only have dev instance but walk through this process with them collaboratively not something you impose on them. Your IT manager should have your back. Maybe a few select Sr devs could have access to prod. Also you should accommodate their needs also. Always say "Yes and.." instead of "Yes but.." For example could you provide them a refresh of dev server from prod (with obfuscated data) on a regular basis, or have different versions of the dev instance that matches a certain branch of work they might doing? etc
We have 3 instances, local sql server where I work on internal nodejs application, a dev instance that also acts as our qa server, then production. Nothing is pushed that was not tested by dev on local, qa folks on our dev/sqa server. All scripts follow a certain folder pattern
20181130_VirtualMachine.V2.0
005_Tables
010_Views
015_Procs
runs in local and is re-runable, then runs in dev/sqa etc