Earlier this month, I presented an Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s. A video of the session is available at the Pragmatic Works website. As a part of that session, I received a couple dozen questions about security that we didn’t have a chance to go over during the Q&A portion of the webcast. Rather than write a short, possibly insufficient, answer for each question, I decided instead to put each question into a blog post. That way, they’ll be a bit easier to track down, read, and get the information you want out of them. These questions run from simple to complex.
Security Questions Asked
For the questions, I’ve made a few edits here and there for clarity. Overall, though, these are all of the questions that I received. As I answer the questions, I’ll add links to the posts.
- What permissions are required to create temporary tables?
- Do we have easy way to grant all stored procedures execution in a single shot?
- Can you please expound on the difference between “Grant” and “With Grant?”
- What is the difference between sysadmin and CONTROL SERVER Permission?
- Do the different ways of accessing SQL Server (Windows Authentication, SQL Server authentication, certificate or key) have differing authentication and authorization performance? If so, can you order the list?
- Does 2012 provide TRUNCATE TABLE permissions?
- How do you access the list of Server Securables?
- How would handle permissions for people that need full SQL Agent permissions (including being able to edit other peoples jobs) without giving sysadmin rights?
- How can you migrate users and passwords from one server to another server?
- I work in a bank and federal inspectors are always looking at how secure are my databases. How would you prioritize security for the SQL Server?
- How can i be sure a user is no longer used, so it can be deleted? Or when was the last time the logon was used?
- What is the relationship between logons, credentials, and proxies? And why they were introduced?
- What are some tips regarding roles other than sysadmin?
- If an associate leaves, what is the best way to remove them from not only the logins but also all the databases?
- What is the difference between db_datawriter and db_ddladmin?
- Is there a way to grant user to create alerts?
- What is the name of the recommended book again?
Some of these post may inspire additional questions. Please leave those questions in the comments and I’ll either write an additional post to cover them or re-direct you to a post or other resource that can answer the question.