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?
Last Words
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.
Strate SQL 
Regarding #28 above “Anyways to identify host name if an app is used through web, as well as mobile, to access SQL database? This question is just for tracking DB SELECT activity and the device/app usage from mobile?”
I too deal with a web based application (an asp.net app run on IIS 7) in which users can connect/logon from any internet enabled device with a compatible version of IE installed and for which the IT dept has authorized the device to access our internal network. Because of this setup I too deal with the issue of what the true source device is. Because access to the database is managed by IIS there is no connection between the back end (SQL Server / Database) and the end user as far as I can tell. IIS acts as a go-between and while I can verify the user account being used by requiring every user to use a unique login there is no way that I can determine that will tell me the source device the user is connecting to the web app from. At least not without making some kind of change to the web app itself which of course would have to be done by the third party vendor that produces the app
LikeLike