Right out of the box, SQL Server makes it pretty easy to grant SELECT, INSERT, UPDATE, and DELETE to all user tables. That’s accomplished by using the built-in db_datareader (SELECT) and db_datawriter (INSERT, UPDATE, and DELETE) database roles in every user database. Any user you add to those database roles will be granted those permissions. But what if you want to grant EXECUTE permission to all of the user stored procedures. Where’s the built-in database role for that? Nowhere to be found. Oh you can create a database role, add users to that role, and grant EXECUTE permission to that...
Continue reading...2012
How to Make SQL Server Listen on Multiple Ports
Have you ever needed an instance of SQL Server to listen on multiple TCP/IP port numbers but didn’t know how to implement it? Well here’s a quick step-by-step guide that will work for SQL Server 2005, 2008, and 2012. Open SQL Server Configuration Manager. On the left hand side, expand SQL Server Network Configuration. Select your instance name. On the right hand side, right click on TCP/IP and select Properties. Click the IP Addresses tab. Scroll down to the IPALL section. Make sure the TCP Dynamic Ports field is empty. In the TCP Port field, enter each port number separated...
Continue reading...Use Powershell to Manage Idera SQL Diagnostic Manager
Idera SQL Diagnostic Manager (or as I call it “SQLdm”) is a great monitoring and performance tuning tool that I was introduced to a few years ago. There are no server-side agents, so it’s a very lightweight tool and good for monitoring all SQL Servers in your environment. The GUI is great for managing servers except when I need to put all monitored servers in maintenance mode. If I’m only monitoring a few servers, it’s not that big of a deal. However, if I’m monitoring a hundred servers, then using the GUI to place them all in maintenance mode could...
Continue reading...Welcome to Everday SQL
My name is Patrick Keisler and I have been a Microsoft SQL Server DBA for over 12 years. During that time I have been working for Wells Fargo Securities. I have gained considerable knowledge over this time in various production support duties, including performance tuning, database design, database maintenance, monitoring, and project management. I started this blog to share my knowledge of SQL Server with the hope of saving DBAs time and effort in their everyday tasks.
Continue reading...
