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?
- Whats the best role(s) to assign a junior DBA if you want them to see jobs, error logs, activity monitor, and run profiler, but not be able to manage jobs or kill processes?
- 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 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?
- How can you migrate users and passwords from one server to another server?
- How would handle permissions for people that need full SQL Agent permissions (including being able to edit other peoples jobs) without giving sysadmin rights?
- 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?
- What is the relationship between logins, 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?
- How do I resolve the error: Drop Failed for User – The database principal owns a schema in the database, and cannot be dropped?
- In 2008, a user at the DB level remains when you remove them at the Server level. Is this by design or perhaps ‘fixed’ in 2012 so that if a user is deleted at the server level she is also deleted from all database security?
- Is the chart or dashboard in the Policy Based Management slide a standard one in SQL Server?
- Is the grantor important? When, I as sysadmin grant rigths to a user I believe I come up as sa as grantor.
- Is there a way to grant user to create alerts?
- Is there a way to hide column/restrict from users?
- Is there way to limit permission with date and time? For example, I want to give read access to a table within certain time period. For example, during business hours only.
- Can you grant access through a job that runs at a specified time and remove this access through another job with the end period?
- On a clustered server, can I grant access to a user to run SQL Trace (Alter SQL Trace permission) on specific databases but not all of them?
- I have a login that does not have an entry in view sys.server_permissions. Shouldn’t every login has at least one entry, for “Connect SQL” as value for permission_name?
- Temp tables: as a DBA, can i delete temp tables that are much too big?
- 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?
- 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.
