This is a problem that plagues DBAs everywhere. When you restore a database, you run the risk of orphaning the users in the database. All users are linked via a SID to a login and if you have SQL Server logins, who’s SIDs are managed by SQL Server, you are at risk. Typically a restore to the same server from which the backup was taken won’t cause an issue unless you dropped and recreated the login. Generally the problem rears its ugly head when you restore a backup to a server that was not the original location. You planned ahead and created the same logins on the new server as existed on the old server, so why do the users end up orphaned? As I mentioned earlier, SQL Server manages the SIDs for SQL Server logins so there is no guarantee that the new login has the same SID as the original login did. Then when you restore your database, the users in that database are expecting SIDs that are not there and the next thing you know you have orphaned users. Just a note, this does not occur with Windows Logins because the SID is controlled by Windows or Active Directory. Unless you drop and re-create the user in Windows, the SID of an Active Directory user will be the same on all SQL Servers and hence your user accounts see the SID they are looking for. So, the million dollar question is, how do you fix the problem without dropping and re-creating the user and messing up the permissions in the process? Microsoft provides us with a handy little stored procedure called sp_change_users_login that you can use to fix orphaned users. This procedure can do several things; it can tell you which users are orphaned, it lets you fix an orphaned user manually, and it can attempt to automatically fix your issues. So let’s look at an example. I have deliberately orphaned a user called Annie in the AdventureWorks2008 database. When I run sp_change_users_login with the REPORT option, I can see that I indeed have an orphaned user. EXEC sp_change_users_login 'REPORT'
…
UserName UserSID
-------- -----------------------------------
Annie 0xA5B5548F3DC81D4693E769631629CE1D To fix this orphaned user all I have to do is run sp_change_users_login with the UPDATE_ONE action and tell SQL Server the name of my orphaned user and the name of the appropriate login. EXEC sp_change_users_login 'UPDATE_ONE','Annie','Annie' There you have it, a simple quick fix to orphaned users that you can use next time you have an issue. I just want to add one more thing regarding the AUTO_FIX action of sp_change_users_login. If you use this option, the procedure tries to automatically fix your orphaned users by matching user name to login name. If no match is found, it will create the appropriate login for you. The only reason I don’t like it is that is has the potential to create logins you don’t want, especially if your login names happen to deliberately differ from your user names.
 Digg It
Are You Ready? Common Ways to Tell You Are Not Ready to Recover from a Disaster is an article I wrote when working recently on a disaster recovery project. It wasn't my first time working a project such as this, and I noted common mistakes being made over and over. These didn't have much to do with specific technology as much as the approach towards the need for an administrator to provide solid recovery of their systems. Specifics around process, documentation, and choosing from available options seem to catch us off guard way too often. When we realize our mistakes, it's almost too late to recover from the error. I hope to bring a little foresight to those administrators handed this very large task. The journal is available from Realtime-windowsserver.com in the Digital Library section. While you are there, subscribe to the journal which is released monthly. - Eric Beehler (consortioservices.com/blog)
Digg It
They have been around forever, but have you really ever implemented Application Roles in SQL Server? For that matter, do you really know what they are and how to use them? In this short tip, I hope to explain just that. First off, we need to talk briefly about how application security can be implemented in SQL Server. There is some debate over which model is better and I am not endorsing any specific one here. Regardless of whether you use SQL Server Logins or Windows Authentication, you still have to decide whether an application will use a single login to access SQL Server (and all appropriate database objects), or allow each individual user to have their own login. They each have their very own sets of pros and cons, but I want to focus on a specific con of each user having their own login. The biggest issue is that each user login has access to your server and to one or more databases. Does the user need to delete data as part of their job? If so, they will have this right whether they log in via an application or directly to the server. Often, the application controls what can and cannot be deleted based on a set of business rules; these rules usually don’t exist on the SQL Server itself. In short, if each user has their own login, they can access SQL Server directly and potentially cause some damage. This brings us to application roles. You create them and assign permissions to them just like regular database roles but you can’t put users in them. Instead, the goal of application role is to provide a best of both worlds scenario for application and user security. Here’s how they work. You set up each user to have an account on the SQL Server with practically no rights. All they should be able to do is login to the server and run a system stored procedure called sp_setapprole. This procedure accepts a couple parameters, including the name and password for the application role. Running sp_setapprole will immediately endow the user with all the permissions that you set up on the application role for the current session only. So what does this mean for security? As long as the password for activating the application role is only known to the application, your users will not have any rights when they login to the SQL Server directly. In order to have the permissions they need, they will be required to use the application that knows the password and can unlock the permission for the application role. Now you can have SQL Server manage individual logins and still have a secure environment that uses the rules and filters in place within your applications.
Digg It
The newest episode is available at CSTechcast.com, a full podcast for IT professionals. This week, CEO of Brighttalk.com Paul Heald talks about how webcasts are helping business reach other businesses and the technology behind it. In the news Microsoft is set to release the cloud computing OS Red Dog, the Windows XP downgrade gets six more months of life, web sites get their credentials ripped off, AMD swings at Intel with the Shanghai server CPUs, and Steve Jobs IS alive even after a rumored health crisis. Credit card companies lack of concern for bad wireless security gets "The Worst Tech Move of the Week", we take "A Closer Look" at the fork in the road upgrading to Windows Vista or Windows 7, and the new Hyper-V Server's HVCONFIG is detailed in "The Weekly Tech Tip". This episode of CS Techcast is sponsored by: vConferenceOnline. Use the discount code CSTECH at vConferenceOnline.com for a 10% discount on a virtual conference. Don't miss the SSWUG Ultimate Virtual Conference coming up soon. Links to stories and sites discussed during the show:
Microsoft will float cloud OS this month (InfoWorld)
Windows XP gets another lifeline (CNet)
Researcher finds evidence of massive site compromise (ComputerWorld)
AMD Says Shanghai Won't Be Another Barcelona (PC World)
Apple stock tumbles nearly 11% on false report of Jobs' heart attack (CompuerWorld) As always, thanks for listening and hit the CSTechcast.com website for ways to get a hold of us. Also, check us out at friendfeed.com/cstechcast and twitter.com/cstechcast for our opinions on tech throughout the week. Link to the episode: http://www.cstechcast.com/home.aspx?Episode=45 - Eric Beehler (consortioservices.com/blog)
Digg It
SQL Server 2008 provides a feature, which, in my opinion, has been far overdue -- backup compression. For too long, if you wanted the benefit of compressed backups, you had to look at a third-party tool. Now, backup compression is built right into SQL Server 2008, and what's even better is that it's easy to use. All you have to do is append the WITH COMPRESSION option to your backup statements and you are off to the races. This bit of code will back up the AdventureWorks2008 database using compression: BACKUP DATABASE AdventureWorks2008
TO AdWorksBackup
WITH COMPRESSION Using compression with SSMS is just as easy. Simply set the compression option on the Options page of the Back up Database dialog. You may be asking yourself: How effective is this compression? The answer can be tricky because it depends on the structure of your database and the type of data being stored. When I backed up my copy of the AdventureWorks2008 database, which is using about 700 MB of disk space, I got a 147 MB compressed backup file. Compare that to the 636 MB file I got when not using compression. Here's what else is cool: You can change the default compression behavior of your entire server. On the Database Settings tab of the Server Properties dialog, you can select the option Compress Backup. Alternatively, you can run the following t-SQL code: EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'backup compression default', 1
RECONFIGURE The first command enables advanced options and the second will make compression the default for all backups. If you go this route, you won't have to change a thing about your backup scripts in order to take advantage of compression. Now, just a simple BACKUP DATABASE statement will use compression. To run a backup without compression when it's the server default, simply use the WITH NO COMPRESSION option. SQL Server 2008 finally offers backup compression natively and I hope you find it to be a useful feature. It's great for saving disk space and you no longer have to zip backup files before moving them over the network just to improve the copy time
Digg It
You ever find yourself with the need to copy SQL Server logins from one server to another? Maybe you are setting up a failover site, building a replacement server, setting up a reporting instance, or maybe you just want to backup the logins just in case. If you are using Windows Logins, this is a simple matter of scripting the login and applying it to the other server. Copying SQL Server Logins from one box to another is a bit trickier because SQL Server stores and manages the password. So just how do you copy the login and preserve the password? I am glad you asked. Understanding Login Components To successfully copy a login from one server to another, you will need to ensure that the copy has the same SID and password. The link between database users and logins is done with the logins SID, if this is different on the new server than any databases you copy over will contain orphaned users. To ensure that both the SID and the password are the same, Microsoft has written a stored procedure to aid in our transfer. SP_HELP_REVLOGIN SP_HELP_REVLOGIN is a stored procedure that will return a complete list of the logins that exists on you SQL Server in a script that can be run to recreate them. This script does not exist on your SQL Server by default, you must create with the code provided by Microsoft in KB article 918992 here http://support.microsoft.com/kb/918992/. Once you have create the procedures you can easily generate the create statements that allow you to copy your logins. As an example, I created a new login on my local instance of SQL Server called SQLScript with a password of scriptme. Now I can run SP_HELP_REVLOGIN as follows: sp_help_revlogin 'SQLScript' RESULTS: /* sp_help_revlogin script ** Generated Oct 30 2007 9:23AM on laptop1 */ -- Login: SQLScript CREATE LOGIN [SQLScript] WITH PASSWORD = 0x0100B642C5A8BC6778ECE4710ED3DC8D70E0EA31B6DF6B122756 HASHED, SID = 0x80525EB475F8414FB32D627BB876F213, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF As you can see, I now have the syntax I need to recreate the login on another box. The SID will be forced to the same value and the passwords will match by virtue of this statement providing the hashed version of the password. If you need to copy all the logins, SQL Server and Windows Logins, you can run SP_HELP_REVLOGIN with no parameters.
Digg It
Welcome to another CSTechcast.com podcast for IT professionals. This week we interview Adam Shostack, author of The New School of Information Security about the essentials IT organizations need to establish to really do security right. In the news, PDF security holes are under increasing attack, Cisco is busy applying patches to its IOS software, solid state drives from Toshiba hit 256GB for netbooks, and Microsoft announces RTM status for Essential Business Server aimed at the mid-sized market and Windows HPC Server for the super high-end. A lack of great new business apps for smart phones and an overabundance of one-trick ponies gets "The Worst Tech Move of the Week", we take "A Closer Look" at areas of unnecessary tech spending, and blocking access to USB drives from Windows is "The Weekly Tech Tip".
Links to stories and sites discussed during the show:
Adobe PDF Reader Vulnerable, U.S. CERT Warns (InformationWeek)
Cisco releases bundle of router security patches (InfoWorld)
Toshiba Unveils 256 GB Drives For 'Netbooks' (InformationWeek)
Windows Essential Business Server (Microsoft)
Microsoft Takes Its Newest High-Performance Computing Platform to the Street (Microsoft)
Enterprise 2.0 Vendors need to get more serious about mobile (The Fast Forward Blog)
The Podcast Awards nomination period closes soon, so get your votes in for CS Techcast at podcastawards.com. If you want to follow us on the social web check out friendfeed.com/cstechcast or twitter.com/cstechcast. Otherwise, give us a ring or type up some feedback, all available at CSTechcast.com.
Link to the episode: http://www.cstechcast.com/home.aspx?Episode=44
- Eric Beehler (consortioservices.com/blog)
Digg It
We’ve been doing the CS Techcast podcast for a while now. The whole point of our effort was that we're opinionated people that liked to talk. We wanted to get our information out there because we felt we had a unique viewpoint as frontline IT professionals. As time went on, we created accounts on Twitter and Friendfeed. No real reason except to see what the buzz was all about. Well, our uncontrollable urge to share on our weekly show now spills over to discussion online about tech and so much more. I normally use the services as an individual, sharing my opinions and posting things that I think are of note, and even a few silly things I find funny, well hilarious really. This versatility of changing subjects is especially important on the Friendfeed service, where the interface is deemed a “lifestream”. I’ve been critical of self-important people online that do nothing but cause a ruckus, Robert Scoble is not removed from that list at times, but he did add us to: The Scoble Top Tech Blogger/FriendFeed/Social Media List, and he ain't half bad as I've found from his stream. Pictures of him in the shower give him a pass on some bluster, because that self important factor is dialed down; he's just nutty like the rest of us. I am certainly not a top poster or the most respected, but I do participate and make our show available inline in the Feed Radio room at friendfeed.com/rooms/feedradio. I support those who post good stuff and try to create and be original, like a wonderful person I met on Friendfeed, Candace Holly, who added us to The Geek Media network. This is just one example of how Friendfeed begins to work for you. It's a great way to find people, debate the issues of the day, and discuss things that you may not get to approach in your normal circle of friends. It’s the new forums combined, because all of your blog posts, tweats, etc get pushed to the service. You are only as important as your contribution, so a one way push of information is not really useful in this realm. When you embrace this medium, those who write a blog or article, but never respond to their readers will become less important to you. They will need to make themselves available to have that dialog, which sounds like a lot of work but really is as easy as talking around the water cooler. As people find you interesting, they will add you to their stream and they may come to really respect your opinion. You will be more important to them than that technology reporter that will never responds to a comment on their blog. Your political viewpoint will be better than cable news fodder because it's interactive. It's not drive-by commenting like you see on some services like Digg.com. They'll listen to you because they know you have an opinion that can be respected and trusted because of your ongoing interactions with each other. As time goes on, Scoble will have to keep adding to this list because great people are always coming into the fold. It's such a good thing for you and me to have this easy way to get access to amazing people and expand our network. I think you’ll find that Friendfeed will help widen your knowledge and help you meet some cool people. Give it a try and friend me at friendfeed.com/cstechcast. I’ll be glad to see you on the stream. - Eric Beehler (consortioservices.com/blog)
Digg It
Check out another podcast for IT pros at CSTechcast.com. This week John Kembel, CEO of HiveLive, gets us familiar with the ins and outs of getting a business to engage with customers through new social networks. Find more information on them at HiveLive.com. The news brings us a read on IT jobs during an uncertain economy, the hacking of Sarah Palin's e-mail, Apple finally addressing the DNS vulnerability, VMWare Virtual Center coming to the iPhone, and announcements from VMWorld on how to extend virtualization beyond the operating system. Investment bank's lack of real information in a world of technology gets "The Worst Tech Move of the Week", those who undervalue their IT staff get ripped six ways from Sunday in "The IT Pet Peeve", and "The Weekly Tech Tip" reviews the snapshot feature in Hyper-V. Links to stories and sites discussed during the show:
Wall Street turmoil unlikely to KO IT industry (NetworkWorld)
Report: Legislator's son at center of Palin hack talk (InfoWorld)
Apple update finally fixes important DNS bug (InfoWorld)
VMware's VirtualCenter coming to Linux, iPhone (InfoWorld)
VMware chief says the OS is history (InfoWorld) If you'd like to support CS Techcast, vote for us in the Podcast Awards. They are taking nominations until the end of the month, so get your vote in. We'd like any feedback you'd be willing to give. Contact information is up on the home page. This week we took some pictures, so those will be showing up on the web site as well. I hope you enjoy the show and keep coming back to CSTechcast.com. Link to the episode: http://www.cstechcast.com/home.aspx?Episode=43 - Eric Beehler (consortioservices.com/blog)
Digg It
Another fine podcast for IT professionals found here at CSTechcast.com. This week we talk enterprise 2.0 with Ross Mayfield, social networking extraordinaire and Chairman, President, and co-founder of Socialtext. Find Ross' blog at ross.typepad.com and SocialText's offerings at Socialtext.com. In the news, possible privacy issues with the IE8 beta phoning home, Dell's pushing into the VM space with new blade servers and storage, the DOJ is questioning the Google-Yahoo ad deal, HP's building an OS of their own, and the LHC gets hacked. Apple's new BSOD causing iTunes 8 gets "The Worst Tech Move of the Week", we take "A Closer Look" at Yammer and the benefits and drawbacks of micro-blogging in the enterprise, and "The Weekly Tech Tip" talks about Core Config, a new utility for Windows Server 2008 Server Core configuration. Links to stories and sites discussed during the show:
MS defends IE 'phone home' feature, clarifies privacy policy (InfoWorld)
Dell unwraps products designed for virtualization (InfoWorld)
Sandy Litvack, a dogged trustbuster in pursuit of Google (CNet)
Hackers deface LHC site, came close to turning off particle detector (ZDNet)
iTunes 8 causes Windows Vista problems (ZDNet)
TechCrunch50: Yammer Wins TechCrunch50 (PC Magazine)
Core Config Utility (Codeplex) We're not just a podcast, check out our ramblings about random thoughts on the social sites twitter.com/cstechcast and friendfeed.com/cstechcast. We always welcome your feedback, so hit the voicemail, feedback page, or blog. All are available at CSTechcast.com. Thanks for listening. Link to the episode: http://www.cstechcast.com/home.aspx?Episode=42 - Eric Beehler (consortioservices.com/blog)
Digg It
A new interview, tech news, and insight from the podcast for IT pros at CSTechcast.com. This week we talk about smartphone and mobile device security with Dan Dearing, Vice President of marketing at Trust Digital. Find out more about Trust Digital at trustdigital.com. In the news, we discuss a kaleidoscope of a patch from Microsoft, social networking for G Men, Dell shutting down factories of their once high-flying made-to-order operations, a six-core server chip from Intel, and a recall of overheating Sony Vaio laptops. Comcast's FCC countersuit gets "The Worst Tech Move of the Week", Chrome, Firefox, and IE8 start up the browser wars once again when we take "A Closer Look", and "The Weekly Tech Tip" delves into the NETSH command.
Links to stories and sites discussed during the show:
Upcoming Microsoft patch lineup could be 'massive,' says researcher (ComputerWorld)
CIA, FBI push 'Facebook for spies' (CNN)
Dell Plans to Sell Factories In Effort to Cut Costs (Wall Street Journal)
Intel ready to announce six-core chip (CNet)
Sony recalls 440,000 Vaio laptops (ZDNet)
Thanks for listening and remember to give us feedback at the blog, at the voicemail box, and at our email. All of these are available at our home page: CSTechcast.com. Keep coming back and bring your friends too. If you'd like to support our show, post a review on iTunes or on your favorite podcast directory.
Link to the episode: http://www.cstechcast.com/home.aspx?Episode=41
- Eric Beehler (consortioservices.com/blog)
Digg It
A new podcast for IT pros at CSTechcast.com is ready for you to download. Anil Desai, respected author, Microsoft MVP, and consultant, talks about the journey of being an independent technology consultant and the lessons learned. Find more on his web site anildesai.net. In the news, we talk the IT disaster recovery efforts in effect prompted by Hurricane Gustav, what jobs are more at risk to being outsourced, Google Apps are not getting much adoption in the enterprise, we discuss the new Cellular Seizure Investigation Stick, and the latest beta of Internet Explorer 8. Comcast's bit cap gets "The Worst Tech Move of the Week", hit the buzzer for our less than 5 minute game show "Know Your Tech", and check out SharePoint wiki permissions in "The Weekly Tech Tip". Links to stories and sites discussed during the show:
New Orleans IT departments brace for Gustav (ComputerWorld)
IT workers hit hardest by offshore outsourcing, survey finds (ComputerWorld)
Google's tough sell to Corporate America (Fortune)
CSI Stick grabs data from cell phones (CNet)
Internet Explorer 8 beta 2 (ZDNet) Keep up with CS Techcast on the social nets at twitter.com/cstechcast and friendfeed.com/cstechcast. Help us out by writing a review where you subscribe to our podcast, either on iTunes or your favorite podcast directory. We look forward to brining you more great podcasts at CSTechcast.com. Link to the episode: http://www.cstechcast.com/home.aspx?Episode=40 - Eric Beehler ( |