Author Archives: SQLGator
Sunday Funday
Today’s installment of Sunday Funday is brought to you by the Code Project introducing the spatial datatypes available in SQL Server 2008: SQL Battleship 2008. Enjoy!
SQL Schoolhouse: Myths and Misconceptions Reading
Paul Randal (blog|twitter) did a wonderful month-long series on the Myths and Misconceptions with SQL Server a couple of years back and I thought it would make an excellent read for the Schoolhouse. Here is the link for the 60 page completely documented PDF version of the blog series. Enjoy!
Know Your (Pre-Defined DB) ROLE!
Yesterday we talked about the Rock and how he can help you with SQL Server security ala “know your role.”
Do you know your SQL Server pre-defined database security roles?
These pre-defined roles will allow their respective members to perform defined sets of activities within each database. It is important to realize that these exist in every database and you cannot drop these roles. They are as follows:
- db_owner Role: This is the Mac Daddy of fixed database roles as it can perform all configuration and maintenance activities on the database including dropping the database. It is the only role that members can add additional users to this role.
- db_securityadmin Role: Members of this role can modify role membership (except db_owner) and manage permissions.
- db_accessadmin Role: Members of this role can add or remove access to the database for Windows logins, Windows groups, as well as SQL Server logins.
- db_backupoperator Role: Members of this role can back up the database, that is all.
- db_ddladmin Role: Members of this role can run any Data Definition Language (DDL) command in a database, in other words they can run ALTER, CREATE, DISABLE TRIGGER, ENABLE TRIGGER, DROP, TRUNCATE, and UPDATE STATISTICS statements.
- db_datawriter Role: Members of this role can add, delete, or change data in all user tables.
- db_datareader Role: Members of this role can read all data from all user tables.
- db_denydatawriter Role: Members of this role cannot add, modify, or delete any data in the user tables within a database.
- db_denydatareader Role: Members of this role cannot read any data in the user tables within a database.
There are several of these that I rarely use, but they are there if I need them. Like the Rock says “know your role!” Enjoy your weekend!
Know Your ROLE!
In the famous words of The Rock “know your role and shut your mouth!” Well mainly we were going with “know your role.”
Do you know your SQL Server security roles?
A SQL Server role defines exactly what a user can and cannot do inside a database. It is also possible for multiple users be it AD users and groups or SQL Server users can share this role. Roles are similar to AD groups in Windows security except they only control security within databases.
Today we will discuss the fixed roles which are setup across the server instance and apply to all objects on the server. They are as follows:
- SysAdmin Role: This is the Mac Daddy of roles as it can perform any and all actions on the server.
- ServerAdmin Role: This role can change configuration options on the server. They can also shut down the server, choose them wisely.
- SetupAdmin Role: This role can manage linked servers, SQL Server startup options and tasks.
- SecurityAdmin Role: This role can only manage server security. They can also reset passwords saving you from the mundane task, if you have your own minions.
- ProcessAdmin Role: This role has the ability to kill processes running on the SQL Server aka SPID killer aka Die SPID die!
- DbCreator Role: This role can create, alter, drop and restore databases, which is pretty powerful just now Genie from Aladdin powerful.
- DiskAdmin Role: This role can manage the SQL Server disk files.
- BulkAdmin Role: This role can only run the bulk insert command (I have personally never seen used before).
- Public Role: Every user belongs to the public role by default and therefore you cannot change membership to this role. Keep in mind if you assign any permissions to this role it will apply across the entire instance.
There are several of these that I rarely use, but they are there if I need them. Like the Rock says “know your role!” Tomorrow we will discuss pre-defined database roles. Enjoy!
Happy Independence Day
I hope everyone had a great and safe Independence day today. I spent the entire day in the pool with friends eatinf jerk chicken and great ribs! Enjoy!
Friends, They Make Life Worth Living
Contemplating a couple of technical articles, but with a holiday tomorrow all I can think about is having a great holiday tomorrow. We are going to another family’s house for a pool party barbecue. We have many friends and many levels of friends, but having great friends makes all the difference in the world. We have other great friends, but we share vacations with this couple and we just enjoy hanging out with them.
That is the greatest feeling, having friends you love to be around that is. Now if my other friends are reading this, don’t worry we love you too. The more friends you have in your life the more rich your life will be. I hope you have great friends, too. If not, then invite me over because I am fun to hang out with ;-).
Personal Development Plan…Midway Status
Six months for 2012 are now in the books and I thought it would be a good time to revisit my original goals for the year and see what needs to still be done. My updates will be highlighted in bold.
- I will post daily to this blog in order to give back to the SQL community that has helped me so much over the years. So far so good here!
- I will spend time daily helping people on twitter, SQL blogs, and various SQL-related websites such as SQL Server Central. So far I have only been able to help on twitter and blogs.
- I am setting a goal of 300 followers on Twitter which would double my existing followers for 2011. I now have 315 followers, that is awesome, thank you everyone!
- I will attend at least ten of the twelve Tampa Bay SQL Server User Group meetings. So far I have not missed a monthly meeting.
- I will attend three to five other SQL User group meetings, such as the Tampa BI group or Orlando’s MagicPASS. I need to get going on this one as I have not made one of these yet, bad boy.
- I will attend every Tampa VMWare User Group meetings which occur quarterly. This is spot on, since the Tampa VMUG has not had a meeting since Dec 2011, I have not missed one yet.
- I will attend other events that interface with SQL Server or VMWare such as the Orlando IT Pro Camp scheduled for January 21, 2012. Missed the Pro Camp, will look for others.
- I will attend one Microsoft SQL training course this year, but I cannot say at this time what that course will be as that will depend upon my employer ;-). I would like to attend Course 2778a (Writing Queries Using Microsoft SQL Server 2008 Transact-SQL) and Course 6234 (Implementing and Maintaining Microsoft SQL Server 2008 Analysis Services. Even better, I got to attend a SQLskills.com training!
- I will attend every SQL Saturday offered in the Orlando, Tampa, and Jacksonville areas. I would also like to attend some others, if possible, within driving distance. I am already registered for SQL Saturday #110 in Tampa on March 10, 2012. Had to miss Jacksonville because of a final in one of my college courses, but made up for it by going to Pensacola.
- I will volunteer to help with at least one SQL Saturday this year. Volunteered at Tampa, will do others later in the year as well.
- I would like to attend SQL Rally in Dallas this year and I would definitely like to attend the SQL PASS Summit, but with one child in college this year and two more entering over the next two years, I may have to pass on this one for a couple of years until they are situated and my bank account returns to normal. Rally was a no go, still hopeful for Summit.
- I will continue to take at least one to two classes a semester online in order to complete my degree in Business Administration. All those Engineering and Computer Science classes I took 20 years ago are not relevant today and I would like to possibly return to consulting in five or ten years and that degree would be more beneficial in that long term goal. Still attending classes regularly.
- I will continue SQLAndy’s recommendation to meet at least three people at every SQL event. This is a great recommendation for networking. It is hard sometimes to break out of our introverted ways. So far so good.
- I will attain my VMWare Certified Professional 4 certificate this year for VMWare. Not going to happen as VMWare retired the exam before my one year was completed, and I have not focused on VMWare as much as SQL Server.
- I would like to speak somewhere this year, my local SSUG, VMUG, or even a training session at work. I need to get over my fear of public speaking. 2012 is the year to step it up and take it to another level. Presented at Pensacola SQL Saturday and now will do my local user group.
Wow, I am well on track this year!
#GetHawt Habits
This is just a quick note in preparation of the #GetHawt competition beginning tomorrow.
The bad habit that I will be giving up is tweeting and facebooking while driving. Although I live in a rural area and this is relatively easy today with the slightly slower paced lifestyle (trust me I do not do it when I am in the city), I know that it is dangerous and sets a bad example for my young teenage driving children. This will be a very hard habit to break, trust me!
The new habit that I will starting is helping out around the house. Since I started back to school last year, I have pretty much neglected my household duties, paying teenagers to do them for me. With blogging, school work, overtime, video games and my wife, the house needs some serious TLC from me. I have a long list of honey-dos to get to.
I have no real exceptions to the food although I am not real clear on the portion size that constitutes a meal. I see the spreadsheet with the individual portion sizes but who knows how many of those make up a meal. Help?
Excellent Article Entitled – Why I Blog – The Atlantic
Today marks the completion of six months of daily blogging. I am half way through my goal of 365 days of blogging. It has definitely become a habit that I do not want to break, although I think next year I will reduce the goal to 5-7 blogs per week so that I am not a slave to the blog. I think some days the posts are forced out of necessity to meet a goal. This will allow some flexibility if I am just not feeling like I have anything to say that day.
I found this excellent article entitled Why I Blog in The Atlantic magazine. The quote that spoke to me was:
“Blogging is therefore to writing what extreme sports are to athletics: more free-form, more accident-prone, less formal, more alive. It is, in many ways, writing out loud.”
Enjoy!
Saturday SQL Schoolhouse, Case Studies
SQL Schoolhouse!
Today’s installment of the Saturday SQL Schoolhouse is brought to you by the fine folks over at Microsoft. When searching for a topic to “teach” today in the schoolhouse, I came across this site called Microsoft Case Studies and could not stop reading. Obviously, it makes perfect sense for Microsoft to create such a page to help business see the correlation between their products and solving the problems that they have.
Being a SQL geek, I was enthralled like when you are reading a good spy novel wondering if the migration was successful, did the BI solution solve their reporting quandary, did the cloud really save them millions in unnecessary overhead. Forget the kindle, pick up a good case study and it may help you solve a problem you are having in your office. Enjoy!
SQL Swampland 