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.