Let’s suppose for a minute that you want to retrieve the top x number of rows from any given query. Keep in mind that x is a value that your users can supply. Sure, you could do string concatenation and EXEC sp_executesql ‘SELECT TOP ‘ + @count + ‘ x FROM y;’. But, then again, a vicious hacker could get all cute and decide to change your form variable to ‘; DECLARE @bad_var AS NVARCHAR(MAX); SET @bad_var = ””; SELECT @bad_var = @bad_var”DROP TABLE ” + name + ”;” FROM sys.

SQL SERVER Top 10 Interview Questions to Ask Senior DBAs Brent Ozar poses some great questions to ask DBAs (and that you might be asked as a DBA) during an interview. These are the good kind of interview questions, not the “how would you do XYZ” kind of questions. (My default response to the XYZ questions is often “Check Books Online”.) Breaking a String into “Words” the CLR way Jonathan Kehayias looks into breaking a string up into discrete words using the CLR instead of pure T-SQL.

This is following up from my previous post on Database Roles. In my previous post, I talked about fixed database roles. If you’ve forgotten, go back and re-read the article. I’ll still be here when you get back. So, what exactly are flexible database roles? They are roles that you create in the database. It’s as simple as that. When you start with a new database, there are no flexible roles, just the fixed roles.

SQL SERVER Re-associate SQL Users with Logins Scott Koon put up this script a while ago (back in ’07), but I had cause to use it this week. When you restore a database from a backup taken on a different machine you’ll need to re-associate users with their logins. Easy. Done. Scripting Server Permissions And Role Assignments Kendal Van Dyke shows how it’s possible to script out all permissions and roles assigned to the users of a specific database.

The people who follow me on twitter have probably noticed that when I’m not tweeting absolute nonsense, I’m tweeting about performance tuning SQL. What most people don’t know is that I don’t have sysadmin access on our servers, nor do I have any of the other permissions that would let me collect trace information or access the dynamic management views. I’ll be honest, at first I thought this would be an insurmountable task – how could I possibly tune queries without access to the tools that I have typically used to tune queries?

SQL SERVER Set based random numbers George Mastros points out that generating a random number using RAND() in a set-based operation will always return the same value for every row in the result set. What’s a DBA to do? Luckily, George also covers a great way to get around this predicament. Hardware for SQL Server 2008 Andrew Fryer posted a few links to SQL Server 2008 Hardware recommendations from Microsoft. Good times.

SQL SERVER The first pillar – A Coherent Design Louis Davidson has started to go in depth into his pillars of database design. Louis clearly explains what a coherent database design is and why it’s important. sp_indexinfo – enhanced index information procedure Tibor Karaszi has put together a custom stored procedure to provide additional information about tables and indexes, including missing indexes on a table. Start / Stop SQL Server, SSIS, SSAS, SSRS and SQL Server Agent at once with a batch file Jorg Klein has posted a batch file to stop/start SQL Server and associated services on command.

Since it’s the new year, and I feel like being a lemming and going along with everyone else in the blogosphere, here are my goals for 2009: Learn the SQL Server BI stack. At first read this is a lofty goal. However, I don’t plan to learn and master the entire BI stack in SQL Server, but I do plan on giving it my best shot. SSRS, SSIS, SSAS, data warehousing, these are all tools that I don’t know well enough to make use of on a daily basis, yet.

Database roles (also referred to as database-level roles) are security roles that exist on a database level, as opposed to the server level. If you are familiar with any aspect of system administration, database roles are similar to groups in the world of Windows system administration. Just like a Windows group, when a user is added to a role they inherit all the rights and permissions of the role. There are two kinds of database roles in SQL Server: fixed roles and _flexible_roles.

SQL SERVER Best practices for taking on the DBA role as a developer – Armando Pratocovers the basics of what it takes to go from developer to DBA. This is a good article and one that’s very near and dear to my heart since I’m pulling myself away from the development world and into the DBA world (I’m almost there!). Auto generated SQL Server keys – uniqueidentifier or IDENTITY – Armando Prato (who gets a double dose of link love) wrote a pretty well-balanced article about a subject that is often a source of contention between DBAs and developers – primary key types.