Archive for the ‘SQL’ Category.
August 17, 2010, 12:50 pm
When you move a database from one instance to another, the security mappings of users to SIDs is lost. This means that when you try to connect to the db, it will fail with an invalid login attempt.
The SIDs in the master db on the old and new servers are not going to be the same. For a db login to be successful, the db security SID must match up to the master db security SID. How to make this so?
sp_change_users_login is the 2000 and 2005 way of doing things. But with a few searches, you’ll quickly see the ‘recommendation’ to user Alter_User instead. Why the change?
sp_change_users_login is only going to match up SQL users in the database against the master db. So, if you have domain users assigned security in your db, these will not get resynched with this stored procedure. Bummer…
Enter Alter_User – this command will allow you to not only resynch db users, but windows\domain users as well. A syntax example can be found at the below site.
August 17, 2010, 12:49 pm
This came about because of some hard-coded row limits on a SQL database.
Luckily, all of the code was encapsulated in SQL a view before hitting the VB application. This meant that we were able to limit the issue by truncating down to the first x rows. Once this was done on the view (which the vb app called), the application then no longer had the Runtime Error 9 message.
June 22, 2010, 1:54 pm
–This will disable All SQL Server Agent Jobs on an instance
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO
December 9, 2009, 12:54 pm
This cropped up in our MOSS 2007 domain environment recently. After a bit of searching, this KB article got us off to the right start (After being on the wrong start trying to find the config database presented like in SP2003):
http://support.microsoft.com/kb/823287
In our case, the issue ended up being one of the Application Pool identities having its password expire (this should have been set when the service account was created to never expire). The way I found this 3rd option to be the issue was by digging in the HTML log.
By going to the Home Directory of a problematic website in IIS, I was able to identify the log location (Windows\Logfiles is it by default). in this were a few 400 and 401 errors. There were also some application event log errors that also indicated a permissions issue. Event 5586
Lastly, the service account may very well have had the ‘Password Does Not Expire’ option removed by a modification to Group Policy.
December 8, 2009, 1:00 pm
Recently while migrating a pretty heavily loaded SQL instance from one clustered envrionment to another. Some potential networking rules were rediscovered.
If you run a clustered SQL environment, the required networking rules need to apply not just to your sql server, but also to the underlying clustered servers as well. This is something that is usually transparent to support workers at the application level (unless they support the cluster as well), so if you have a seperate network team, they might not necessarily get the full request through or understand what it is you are trying to do!