I’m writing a SQL Server security presentation and I need your help.
5February 24, 2022 by Kenneth Fisher
I’m working on a new presentation titled
Watch Ken solve security headaches in SQL Server.
In this presentation I’m going to pick a handful of common security problems and solve them in demos. How many? Well, enough to fit the time frame. I’ll prepare at least 10, probably quite a few more and keep going until I run out of time.
This is a list of examples of SQL Server security related questions that I get fairly frequently and that I’ll probably be using.
- Does account XYZ have execute access for stored procedure sp_blah?
- What AD groups have read access in database DBName?
- Please copy the permissions for account svcAccountA to svcAccountB.
- Please copy all of the logins that are associated with DBName from ServerA to ServerB.
- Please script the permissions for database DBName.
- Can I have sysadmin access?
- Jacky has the access I need in database DBName. What AD group should I be added to to get the same access that she has?
Here’s where I could use your help. What are some of the problems you have? Rather than just come up with things that I have to answer all the time, I’d like to know what types of security questions give you headaches?
SQL Studies 
Why can’t I reseed an identity column when I truncate a table via SSIS reload process.
It might be beyond the scope of your presentation, but authorizations involving linked servers (to other SQL instances) can get confusing. Especially when connections are made using a specific security context.
It’s not (yet) a problem but they get a bad press: linked servers.
I find them extremely useful for reports. If they are not to be used, what are? SSIS-packages?
I’d ask
— Who can generate an execution plan?
— Does the user have access to the sys files?
— Can the user see the Reports Tab off of the server?
— Can the user see all of the reports/links off of this tab
These are current issues our DBAs need to resolve – generally you’d need a higher level of access to see these.
I would also include ways of unintentionally enabling privilege escalation, such as db owner being an sa account, or setting a user to security admin. Both allow a non-sa account to elevate their access to an sa. There are other ways that also exist. Knowing about them is probably the hardest part.