Sunday, September 4, 2011

Fixing Orphanes Users in SQL Server


What is an orphaned User?
A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance. 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.

How do you fix the problem without dropping and re-creating the user and messing up the permissions in the process?
By using a 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.

Detecting orphaned users:
USE ;
GO;
sp_change_users_login @Action='Report';
GO;
Note: sp_change_users_login cannot be used with SQL Server logins that are created from Windows.

Fixing a particular Orphaned User:
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','name_orphanedUser','name_appropriateLogin'

Fixing Orphaned Users Automatically:
This procedure should be created in the Master database. This procedure takes no parameters. It will remap orphaned users in the current database to EXISTING logins of the same name. This is usefull in the case a new database is created by restoring a backup to a new database, or by attaching the datafiles to a new server.

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_fixusers
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>'
END

GO

CREATE PROCEDURE dbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers 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 fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END


CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>'
go

Congratulations! Orphaned users are Fixed.

Friday, September 2, 2011

Configuring Database Mail in SQL Server 2008 R2

Database mail enables an instance of SQL server to send outbound e-mail messages.

For using Database mail feature in SQL server, one has to enable database mail feature by running the following query:

EXEC sp_configure "Database Mail XPs",1
GO
reconfigure with override


The next step would be to Configure Database Mail. For this,
    Connect to the instance of SQL server. Open Management node -> right click on Database Mail -> Configure. You will get welcome screen. Click Next. (There's nothing to do on the Welcome screen). In case you didnt enable the feature of Database Mail, it shows you a pop up asking you to enable it. Click "ok". If you did enable it earlier, it just takes you to the next page of the dialog.
    Click Set up(assuming you're installing it for the first time) -> Next.
    Specify Name for the profile and Click on ADD for configuring e-mail.
    You need an SMTP account for this. Fill in your account details. Also select your authentication mode and fill in the required details.
    Click ok and then click Next -> Finish.
    The final page shows all your configurations and gives message of completion. Click Close.
Now you are ready to test your Database Mail.
Right Click on Database Mail and click on send Test Mail. Enter the recepient's email address and hit send mail. Verify that you have recieved it.
Congratulations. You are all set to start using this wonderful service.


Thursday, March 31, 2011

Installing Maven on Fedora

Maven: Maven is a software project management and comprehension tool. Based on the concept of a Project Object Model (POM), Maven can manage a project's build, reporting and documentation from a central piece of information.

System Requirements:

JDK:

1.5 or above (this is to execute Maven - it still allows you to build against 1.3
and prior JDK's).

Operating System:

No minimum requirement. On Windows, Windows NT and above or Cygwin is required for
the startup scripts. Tested on Windows XP, Fedora Core and Mac OS X.

Installation:

1) Download maven from : http://maven.apache.org/download.html
You could use the latest release.

2) Unpack the archive where you would like to store the binaries. I am assuming you know how to do that. A directory called "apache-maven-3.0.x" will be created.

3) Add the bin directory to your PATH. In the terminal type : export PATH=/usr/local/apache-maven-3.0.x/bin:$PATH
You may be asked to perform this operation as root.

4) Make sure JAVA_HOME is set to the location of your JDK. If you have JDK already, you would have done it at the time of installing it. If you don't have it, you need to download and install it.

5) Run "mvn --version" to verify that it is correctly installed.
If correctly installed the output will be similar to the following:

[root@localhost /]# mvn --version
Apache Maven 3.0.3 (r1075438; 2011-02-28 23:01:09+0530)
Maven home: /home/jayati/Download/apache-maven-3.0.3
Java version: 1.6.0_18, vendor: Sun Microsystems Inc.
Java home: /usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "2.6.30.10-105.2.23.fc11.i586", arch: "i386", family: "unix"

Congratulations! You have successfully installed Maven.

Monday, September 6, 2010

Installing Apache Mahout

To start, you need to install the following prerequisites:

* JDK 1.6 or higher
* Ant 1.7 or higher
* If you want to build the Mahout source, Maven 2.0.9 or 2.0.10

You also need this article's sample code (Download), which includes a copy of Mahout and its dependencies. Follow these steps to install the sample code:

1. unzip sample.zip
2. cd apache-mahout-examples
3. ant install

Step 3 downloads the necessary Wikipedia files and compiles the code. The Wikipedia file used is approximately 2.5 gigabytes, so download times will depend on your bandwidth. It also uncompresses the files. You need to have 10 GB of free space.
Occasionally, there may be an error.
You can try commenting the lines of download of 2.5 GB of wiki pages and download them separately with the link given. Then ant install will give you build failed. Do not worry. A folder named Wikipedia would have been created in the apache-mahout-examples directory. If not, create one. Place the downloaded content of wikipedia file there. Do ant install again. Now those files already downloaded will just uncompress and it will take about half an hour. Depending upon your system configuration, this may vary. The result will be installation of mahout.

Sunday, September 5, 2010

Installing ant

First, Please make sure you have the Latest jdk. Currently java1.6 with update 21 would be required.
Now download ant from http://ant.apache.org/
The binary edition of Ant is shipped with 3 different compression formats:

1. .zip - Recommended compression format for Windows, can also be used on other platforms. Supported by many programs and some operating systems natively.
2. .tar.gz - Uses the tar program to gather files together, and gzip to compress and uncompress.
3. .tar.bz2 - Uses the tar program to gather files together, and bzip2 to compress and uncompress..

Choose the format that is best supported for your platform.
Next step would be to extract the folder and place in the directory.
Set environmental variables JAVA_HOME to your Java environment, ANT_HOME to the directory you uncompressed Ant to, and add ${ANT_HOME}/bin (Unix) or %ANT_HOME%/bin (Windows) to your PATH.

Setting up environment Variables.
From the Start Menu, select Start > Settings > Control Panel.
Double-click System to open the System Properties window.
On the Advanced tab, select environmental variables .
Modify each environmental or system variable.
Set the PATH environment variable to include the directory where you installed the Ant bin directory:

1. Find the PATH environment variable in the list. If PATH is not listed, click on New under the System variables section.
2. Type %ANT_HOME%\bin;%JAVA_HOME%\bin;
Important: If there are other variables listed, create a new variable separated by a semicolon. Ensure there are no spaces before or after the semicolon.

Set the ANT_HOME environment variable to the directory where you installed Ant:

1. Click on New under the System variables section.
2. Type ANT_HOME in the variable name field.
3. Type the location where you extracted your ant in the variable value field.

Set the JAVA_HOME environment variable to the directory where you installed the J2SE SDK application:

1. Click on New under the System variables section.
2. Type JAVA_HOME in the variable name field.
3. Type C:\j2sdk1.4.2_13 in the variable value field.

Done!
You've just installed ant. To check, open command prompt and type ant -version.
If it shows you the version of ant you installed, everything went on fine.
Congrats.

Friday, August 27, 2010

Using Subeclipse Behind Proxy Server

I loaded the subeclipse plug-in in my eclipse. But it just wouldn't work. I realized this was due to the proxy server I was using. I had configured the proxy settings in Eclipse but surprisingly subeclipse doesn't take proxy from eclipse. Servers in the windows file have to be configured to do the same. Here's how...

  • Open the servers file in your favorite text editor. For this, in RUN type %APPDATA%\Subversion\servers and press Enter. A dialog box appears asking you via which program you would like to open it. Click on any text editor.

  • Scroll to the end of the file. You'll see a Global Section. In the Global section of the file uncomment http-proxy-host and http-proxy-port (and user name and password in case you are using it) and give the required values. That is where the subeclipse will take the settings to connect to the internet from.

  • Now go back to the SVN Repository view in Eclipse and refresh the repository.

Congrats! It's done.

Friday, July 9, 2010

Changing From one Drive to another in Command Line

A quick post to document what came as a little surprising to me.

While using the Command line in Windows Vista Professional, I wanted to change my working directory from C to D. Any amateur would say just type D;\ but guess what, that didn't work for me.

A little googling gave me the solution. I needed to use /d flag.

y:\>cd /d x:\Folder1\Folder2
x:\Folder1\Folder2>

Good Luck!