Problem with users after restoring from another database?

Error 15023: User already exists in current database.

1) This is the best Solution.
First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.

USE YourDB
GO
EXEC sp_change_users_login 'Report'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.

USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified

USE YourDB
GO
EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
GO

2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.

USE YourDB
GO
EXEC sp_dropuser 'ColdFusion'
GO

Create the same user again in the database without any error.

Stored Procedure 1:

/*Following Stored Procedure will fix all the Orphan users in database
by mapping them to username already exist for user on server.
This SP is required when user has been created at server level but does
not show up as user in database.*/
CREATE PROCEDURE dbo.spDBA_FixOrphanUsers
AS
DECLARE @username VARCHAR(25)
DECLARE GetOrphanUsers CURSOR
FOR
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND (sid IS NOT NULL
AND sid <> 0x0)
AND SUSER_SNAME(sid) IS NULL
ORDER BY name
OPEN GetOrphanUsers
FETCH NEXT
FROM GetOrphanUsers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
EXEC sp_changedbowner 'sa'
ELSE
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END
CLOSE GetOrphanUsers
DEALLOCATE GetOrphanUsers
GO

Stored Procedure 2:

/*Following Stored Procedure will fix all the Orphan users in database
by creating the server level user selecting same password as username.
Make sure that you change all the password once users are created*/
CREATE PROCEDURE dbo.spDBA_FixOrphanUsersPassWord
AS
DECLARE @username VARCHAR(25)
DECLARE @password VARCHAR(25)
DECLARE GetOrphanUsers CURSOR
FOR
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND (sid IS NOT NULL
AND sid <> 0x0)
AND SUSER_SNAME(sid) IS NULL
ORDER BY name
OPEN GetOrphanUsers
FETCH NEXT
FROM GetOrphanUsers
INTO @username
SET @password = @username
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
EXEC sp_changedbowner 'sa'
ELSE
EXEC sp_change_users_login 'Auto_Fix', @username, NULL, @password
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END
CLOSE GetOrphanUsers
DEALLOCATE GetOrphanUsers
GO

Stored Procedure 3:

----Following Stored Procedure will drop all the Orphan users in database.
----If you need any of those users, you can create them again.
CREATE PROCEDURE dbo.spDBA_DropOrphanUsers
AS
DECLARE @username VARCHAR(25)
DECLARE GetOrphanUsers CURSOR
FOR
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND (sid IS NOT NULL
AND sid <> 0x0)
AND SUSER_SNAME(sid) IS NULL
ORDER BY name
OPEN GetOrphanUsers
FETCH NEXT
FROM GetOrphanUsers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
EXEC sp_changedbowner 'sa'
ELSE
EXEC sp_dropuser @username
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END
CLOSE GetOrphanUsers
DEALLOCATE GetOrphanUsers
GO


Reference: Pinal Dave

Deleting a Project in TFS 2008

TFSDeleteProject [/q] [/TeamFoundationServer:] [/force]

[/q] - Quiet mode. Do not prompt the user for confirmation.
[/TeamFoundationServer:] - The hostname of the team foundation server. (Only necessary when using more then one team foundation server in your environment.
[/force] - Indicates the program should continue even if some parts cannot be deleted.
- The name of the project. Use quotation marks if there are spaces in the name.

DELETE permission on the project is required.

sample: tfsdeleteproject /q /force /server:Adventureworks storeFront

You can find TFSDeleteProject at \Program Files\Visual Studio 9\Common7\IDE\.

=End of blog=

SQL Server Reporting Server (SSRS) service is failing to start

I was trying to create a new project using team edition when I encountered an error stating that I wasn't able to create a new project because my reporting services is not running.. blah blah blah

So I run the services.msc through run command and start the service manually, but unfortunately the process always fails after clicking the start button and before reaching half of the progress bar...googling the error I found out this solution. This might of help others experiencing the same problem.

To resolve this issue, increase the time-out value for service startup process. Increasing the value, the service will have more time to loading when the computer starts.

Subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
Name: ServicesPipeTimeout
Type: REG_DWORD
Data: The number of milliseconds that you want to give the services to start in

Typically, a data value of 30,000 is sufficient enough to keep the service from timing out. Hence, you can reduce or increase this value according to your specific startup requirements. To create this registry entry follow these steps below:

1. Click Start, click Run, type regedit, and then click OK.
2. Locate and then click the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

3. Right-click Control, point to New, and then click DWORD Value.
4. In the New Value #1 box, type ServicesPipeTimeout, and then press ENTER.
5. Right-click ServicesPipeTimeout, and then click Modify.
6. Click Decimal, type the number of milliseconds that you want to wait until the service times out, and then click OK.

For example, to wait 60 seconds before the service times out, type 60000.

7. Quit Registry Editor, and then restart the computer.

Problem Opening SharePoint Server Alert in Outlook 2007

This problem occurs because Exchange 2003 cannot convert the MIME-type properties to MAPI-type properties correctly if the names of the properties begin with X-.

You cannot open a SharePoint Server 2007 notification message in Outlook 2007 when your mailbox is on an Exchange 2003 server.

The hotfix is available via customer support. The hotfix applies to Exchange Server 2003 and has to be applied after it has been upgraded to SP2.

http://support.microsoft.com/kb/930807/en-us

Send Mail with Asp.Net {C#}

//1. Send Simple Mail in Asp.Net without any credentials (Host, Username or password)

using System.Net.Mail;

public static void SendMail(string From, string To, string Body, string Subject)
{
SmtpClient objSmtpClient = new SmtpClient();

// Give SMTP Server name here.
objSmtpClient.Host = ConfigurationManager.AppSettings["SmtpServer"].ToString();
objSmtpClient.Port = 25;

MailMessage objMailMessage = new MailMessage();
MailAddress objMailAddress = new MailAddress(From);
objMailMessage.From = objMailAddress;
objMailMessage.To.Add(To);
objMailMessage.Subject = Subject;
objMailMessage.IsBodyHtml = true;
objMailMessage.Body = Body;
objMailMessage.Priority = MailPriority.High;

objSmtpClient.Send(objMailMessage);
}

//2. Send Mail in Asp.Net with credentials (Host, Username or password)

using System.Net.Mail;

public static void SendMail(string From, string To, string Body, string Subject)
{

// give SMTP Server Host name or IP Address.
SmtpClient objSmtpClient = new SmtpClient(”HostNameOrIP”);

MailMessage objMailMessage = new MailMessage();
MailAddress objMailAddress = new MailAddress(From);
objMailMessage.From = objMailAddress;
objMailMessage.To.Add(To);
objMailMessage.Subject = Subject;
objMailMessage.IsBodyHtml = true;
objMailMessage.Body = Body;
objMailMessage.Priority = MailPriority.High;

// Give your SMTP Server UserName and Password
objSmtpClient.Credentials = new System.Net.NetworkCredential(”UserName”, “Password”);

objSmtpClient.Send(objMailMessage);

{C#} Simple Encrypt and Decrypt String

//Encrypt Method

private string EncryptString(string strSource)
{
Byte[] b = System.Text.ASCIIEncoding.ASCII.GetBytes(strSource);
string encryptedString = Convert.ToBase64String(b);
return encryptedString;
}

// Decrypt Method

private string DecryptString(string strSource)
{
Byte[] b = Convert.FromBase64String(strSource);
string decryptedString = System.Text.ASCIIEncoding.ASCII.GetString(b);
return decryptedString;
}

C# Convert String into Proper Case

Convert a string to Proper Case
===========================

// Use this Namespace
using System.Globalization;
// Code
string myString = "jEssE PaTricio-jR";
TextInfo TI = new CultureInfo("en-US",false).TextInfo;
Response.Write (TI.ToTitleCase( myString ));