SQLLawman

Family man and SQL Geek

Developer can not create views

By MichaelDeputy, 1 year and 7 months ago

I received a e-mail a few days ago from one of our developers informing me that he is unable to create views on one of our SQL Server 2005 databases.  He also told me he had another developer try and she also could not create views.  Interesting, I thought, so out to my favorite co-worker - Google.  My buddy Google quickly pointed me to this MSDN post.  This explained to me that when a user gains rights by Active Directory group membership they are not given a default schema.  Say what? Yes, that's right, because multiple AD groups could have rights on a database, and a user could be a member of multiple AD groups it could get confusing.  So any statement that requires a schema will fail with some form of the following error message.

Keep reading →

Related posts

Share this post

free desktop Virtualization

By MichaelDeputy, 1 year and 7 months ago

I enjoy testing things on my computer at home.  I have two machines at my complete disposal to test and play around.  First is my Toshiba Laptop, dual core processor, 4 GB Ram, 2x200 GB hard drives, Windows Vista HOme Premium 64 bit, not provided by my employer.  Number 2 is an old desktop.  Single core processor, 2 GB Ram, 200 GB hard drive, Windows XP home.  I am also very thrifty (read cheap) so I went looking for a virtualization solution for these machines.  I started with VMware server.  This works perfectly and easily on my XP desktop and was my tool of choice.  Unfortunately it was a but troublesome on my Vista laptop.  I started to dig into making it work, but said to myself: «self you want to play with SQL, not VMware why waste time trying to make it work?» so I looked elsewhere.  I found Sun's VirtualBox.  This worked pretty well, and I used it for several months.  But that relationship began to wane  when I was out in Seattle for the PASS conference.  VirtualBox required me to create network bridges to get connectivity and that began to become troublesome as I moved form wifi spot to wifi spot.  I was having trouble connecting to access points.  I deleted all the bridges and my wifi problems went away, but so did my vm's ability to get to the Internet.  So I began to stop using virtualization on my laptop for a while.  But then I wanted SQL 2008 on Windows 2008 on my laptop, so I took another look around and downloaded Microsoft's VirtualPC.  Well truthfully, I downloaded Virtual Server, but it said NO to my Vista OS.  What a waste of a download.  So I then downloaded VirtualPC.  I installed Windows 2008 & SQL2008 and was pretty happy.  I also used VirtualPC to install the Windows 7 beta.  I installed the SQL 2008 management tools on that VM and it works quite well.  Well that's all for now.  I'll possibly revisit this later as new tools become available.

Keep reading →

Related posts

Share this post

One New Years Resolution Down....

By MichaelDeputy, 1 year and 7 months ago

After being tagged to blog my Professional New Years Resolutions late last year, I made getting certified as my first one.  Today I bit the bullet and took the 70-431 test.  I took about an hour to complete the 35 trivia (multiple choice) and 12 simulations.  I was a little shocked when the screen displayed 1000/1000 for my score.  The family and I celebrated big time.  We went to Moe's Southwest Grill and then went to the mall so the kid could use the Christmas money that was burning a hole in their pocket.  So I'm going to take the rest of the month off, then start studying for the next one.  Maybe the 70-432...

Keep reading →

Related posts

Share this post

My Wordle

By MichaelDeputy, 1 year and 7 months ago

After seeing a few of the other Wordles beginning to pop up on blogs, I had to add my own.  I think it will be fun to do this again in a few months to see how the words change.  So enjoy.

Keep reading →

Related posts

Share this post

20 Week Challenge

By MichaelDeputy, 1 year and 7 months ago

I had plopped onto my recliner over the weekend and looked around at my family.  I noticed that there seemed to be more of us than I had remembered.  Since the summer/fall ended we had begun become sedentary.  A family that swam, biked, and ran all summer and fall, were turning into couch blobs.  I wondered to the bathroom and stepped on the scale, yep, 15 lbs since my Hawaii trip.  So I came up with a challenge.  What I proposed was a 20 week challenge.  Here's the challenge:  On the first week, everyone will do at least 5 minutes of aerobic activity every day Monday through Saturday.  During the second week, the time increased by 6 minutes each day.  The third week, 7 minutes and so on.  The payoff?  If all four of us can perform this 80% of the time over the 20 weeks, I will take the family to Great Wolf Lodge for a weekend.  (interestingly enough, 20 weeks is about the end of the school year.)  Today was day one.

Keep reading →

Related posts

Share this post

Finding databases that have changed to Simple Recovery Mode

By MichaelDeputy, 1 year and 8 months ago

As I am beginning our journey into SQL 2008, I am taking this time to go through all our scripts and try to resolve some of the issues I get alerted on that are due to how we do things.  One of the things we do is programatically list all the databases, determine which are prod, test, & system.  Then the Prod databases have xlog backups and the others do not.  Occasionally, some of our prod databases will change from Full Recovery Mode to Simple Recovery Mode.  How I find out about this is that the Xlog backup job will fail.  The first database that should have been in Full but was in Simple would fail the whole job and transaction logs would not be backed up.  Failing jobs is a horrible way to find out that a recovery mode changed.  So I re-wrote our backup job to query sys.databases and do xlog backups to any database in full recovery mode.  No more xlog failures for wrong setting.  Life is good.  But wait, what if a database does change modes how will I know?  Sure the xlogs of databases that are in full recovery mode backed up, but that may not be good enough.  So I sat back down and started wrote an new script.  This script looks at yesterday's list of Simple Recovery Mode servers and compares it to Today's list.  If there is a new database in Simple recovery mode it sends me an e-mail to investigate it.  If there is no change, it just ends.  Here's the code.

Keep reading →

Related posts

Share this post

Subscription Options:

Subscribe via RSS Subscribe via Email

Search

Tags

Categories

Archives

Links

Latest posts

Latest comments

  • Who’s Winning the Olympics?:
    • MichaelDeputy: That is a very astute observation, but if one athlete were able to win 2 golds and 2 silvers it would give the country a huge boost and obviously that country produced the best athlete (in his/her discipline) which is what I am looking for. After all the song they play is the national...
    • SQLRockstar: how about tracking the distinct number of athletes that win medals? if one country sends ten athletes, but one athlete wins two golds and two silvers, then their score (by your scale) would be a 1.0, which is quite skewed. it would be better to only count that athlete once, not sure which medal...
    • Jenny: Very cool system. I can't wait to see how your scores pan out over the next two weeks.
  • SKU is Invalid when installing 2nd node in SQL 2008:
  • Find all Triggers:
    • SQLRockstar: funny, i just put together some t-sql for finding triggers as well: SELECT name, OBJECT_NAME(parent_id), is_disabled as [Parent Object] FROM sys.triggers i use sys.triggers because it includes DDL triggers, which are not in the sysobjects table.

Most commented posts