SQL, Code, Coffee, Etc. - Data Musings by Marlon Ribunal

05/03/2012
by Marlon Ribunal
2 Comments

DBA Roadmap Seminar review

Our usual step when seeking for advice is to search the internet for relevant topics. There is no shortage of what we can get if we know where to find them.

The internet has a vast, rich body of information that is readily available for our instant gratification.

But I feel that the goals I want to achieve are too important for me to rely on the John and Jane Doe’s that dominate my Google search.

(Of course, I’m making exception for the likes of Brent Ozar, Thomas LaRock, Brad McGehee, etc. who never fail to offer great pieces of technical advice.)

I am not saying that I cannot put these free information to good use – I can, in more ways than one.

There’s more ‘why’ to that.

I feel that if I invest some of my hard earned money to my goals I can probably get more value than just by consuming free information on my spare time.

I need to invest in myself. I need a concrete way to get my body and soul commit to the journey!

Thus, I paid to get the DBA Roadmap. A thing like this is the least I can do for my career. Some are spending out of pocket upwards of $5,000 dollars each year towards their career (training, conferences, etc).

The DBA Roadmap Seminar is a business venture by the SQL Server couple Sean and Jen McCown (@MidnightDBA), or popularly known as the Midnight DBA’s. The recorded seminar costs $99.00 as of this writing. Update: The couple is now operating as Minion Ware LLC.

The seminar has 7 tracks, including the Introduction and Conclusion. Except for those two, you can listen to any tracks in any order you want (not in sequence).

The main tracks are: How To Study, What To Study, Resumes, Job Hunting, and Interviews. Each track comes with a bonus Companion Guide that gives you a breakdown of what has been discussed on the track. The guide also provides additional links to useful sites and resources. The tracks are described on the DBA Roadmap site.

You’re probably thinking if these tracks can actually offer you new things. It’s true that these topics are often discussed. But do you think the McCown’s will ask you for money just to rehearse things you already know? Yes, I know, right? There was hesitation at first on my part because of the way the seminar was being marketed (newbie’s seminar). But I found out it’s exactly what I needed.

Although a newbie to SQL Server can reap the most benefit from this seminar, I don’t see why a current SQL Server pro can’t learn and pick up resume and interview skills from it.

For 99 bucks, the seminar is a steal. Listening to 5 hours of solid, meaningful material is like attending a whole day of seminar. Plus, you can play the tracks over and over again, as I have, if you cannot digest the content on one pass.

If you are a SQL Server DBA with 2-3 years worth of experience or even a mid-level SQL pro looking to advance his career and thinking that he could use an advice or two, you owe it to yourself to purchase this seminar.

04/23/2012
by Marlon Ribunal
11 Comments

Who are the sysadmins in this sql server?

Sysadmins are pretty much powerful in the SQL Server environment. They can put the whole SQL Server infrastructure under their control. That said, it is important to know who else has this fixed server role.

Here’s a quick query that you can run to find out the users with sysadmin fixed server role. Sysadmins have a complete control of your server. So, it is very important to know who these people are.

Just to give you an example on why it is very important to check who’s who in your server from time to time, Team Shatter has recently published an advisory on Privilege Escalation Via Internal SQL Injection In RESTORE DATABASE Command.

According to the advisory, there is a vulnerability in the RESTORE DATABASE command that allows malicious users to run SQL codes via internal sql injection. This vulnerability can be exploited by users with CREATE DATABASE permission.

A rogue user can find his way to taking control over your databases by using a backdoor such as the vulnerability described above. Imagine the same user was able to add himself to a server-wide role such as the sysadmin.

Find Sysadmins with TSQL Query

So, going back to the query, here is what it looks like:

USE master
GO

SELECT p.name AS [loginname] ,
p.type ,
p.type_desc ,
p.is_disabled,
CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%'
-- Logins that are sysadmins
AND s.sysadmin = 1
GO

And that query should give us something like this:

Find Sysadmins with System Stored Procedure

There is a quicker way to do that, though. You’ll achieve a similar result by executing the system stored procedure sp_helpsrvrolemember:

 EXEC sp_helpsrvrolemember 'sysadmin'

And, you’ll get a result that looks like this:

Of course, you can use any of the other server roles with sp_helpsrvrolemember as parameters. For example, if you want to look for users with the database creator role, you use the variable 'dbcreator' instead of 'sysadmin'.

Server roles are listed somewhere in one of the system tables in the master database. But there’s already a system stored procedure for that as well – sp_helpsrvrole. You don’t have to dig in and search for them yourself. SQL Server gives you most of the stored procedures you’ll likely need out of the box.

EXEC sp_helpsrvrole

Executing that will give you the following list:

Update from the comments:

Here’s a good one from Eric Russell (see comment below): to return what accounts are members of a domain group, we do this:

xp_logininfo 'DEVCOMPUTER\SQLAdmins', 'members';

*suppose SQLAdmins is a domain group

And, it does exactly what it is meant to do – returns the members of the group like this:

04/16/2012
by Marlon Ribunal
9 Comments

10 must follow blogs that might not be in your list

There are few memes going around in the SQL world. Probably the most popular of them is TSQL Tuesday (#tsql2sday). Of course, there’s Meme Monday (#mememonday) and also Un-SQL Friday. Another meme that has been gaining traction is #Meme15 to which this post is contributed.

What’s the significance of a meme, you might ask. In a meme, we see the common voice of a community. We may see different opinions but they always convey a common expression of thought, idea, or passion.

Every meme is bound by certain rules – do’s and don’t’s – which define the scope of the conversation. Every participant must play by the rules.

This adherence to the rules is what makes a meme more interesting because you could only go so far in expressing your thoughts. Go beyond these parameters and you become so different.

Meme reveals those who belong to the pack and it exposes those who are not.

Ok, I think that’s an overkill explanation of what a meme is.

Now let’s go back to #Meme15. This month’s assignment is:”What are ten blogs that you think other SQL Server professionals should be following but might not be?“

So, here are the ten blogs that, I think, fit the bill (in no particular order):

1. SQL Brit by John Sansom – My favorite from his blog is the “How To Become A SQL Server DBA” section.

2. Belle’s SQL Musings by Donabel Santos – Database Administration, Development, BI, Powershell, etc. Belle got the whole nine yards.

3. Ted Krueger’s Blog At Less Than Dot – Backup and Recovery, Security and Database Development are just among the great topics you’ll find in Ted’s blog.

4. SSIS – SQL Server Tidbits by Josef Richberg – Are you looking for SSIS Tips & Tricks? He’s one of the go-to guys on SSIS.

5. Andy Leonard’s Blog – Another expert on SSIS and SQL Server Development in general. What I like most about Leonard is his boldness with his Christian Faith. He is
the Tebow of SQL Server.

6. Tradney’s Blog by Tim Radney – Tim started in SQL Server as an accidental DBA. He has now become a solid DBA. He is the latest addition to Linchpin People, LLC, a company founded by Andy Leonard, et al.

7. The Goal Keeping DBA by Kenneth Brian Kelley – It’s a Personal Self-Improvement blog for database professionals.

8. Code Gumbo by Stuart Ainsworth – Topic includes Data Integration, Database Development, and community-related content.

9. Shell Your Experience by Laerte Junior – One of the PowerShell guys. Laerte is the developer of Codeplex SQLPSX – a SQL Server POwerShell Extension. Some of the posts are in Spanish.

10. Michael Hyatt – I know this is not a tech blog but I must add this here because some of the people I know weren’t aware of this blog. You will learn a lot about Leadership from this guy. Leadership is essential in whatever field you’re on.

This list is just a tiny bit of the blogs I’m following. If the meme asked for 100 blogs, I could have probably produced that list.

Do you follow blogs that you think are worth following? Add them in the comment below.