Login failed for user 'NT AUTHORITY\IUSR'

If you encountered this error while running your published website or web service in IIS7:

Message: Sys.WebForms.PageRequestManagerServerErrorException: Cannot open database "[DB_NAME]" requested by the login. The login failed.

Just follow these simple steps to solve the problem.

1. Open SQL Server Management Studio.

2. On the left menu, expand Security then Logins. Check if 'NT AUTHORITY\IUSR' exists. If not then that is the cause of the problem.

3. Right-click on Logins then click New Login...

4. On the General page, click Search...

5. On the next popup window, click Advanced...

6. Then on the following window, click Find Now and find IUSR on the Search Results. Then click OK.

7. Back on the left menu, you'll now see the 'NT AUTHORITY\IUSR' user. Right click on it and click Properties.

8. On the Server Roles page, check on sysadmin.

9. Then on the User Mapping page check on the database name you need access to; and below that on the database role membership make sure to also check on db_owner then click OK.

...And that's a wrap, you shouldn't be seeing the login failed error now.



-k

Creating Scalar-valued Functions in SQL Server Management Studio

We use functions in T-SQL to return a value based on passed parameters just like we do on any other language. But in SQL one use of functions is to provide in-line data processing in our queries like you will see in our example today.

Begin by expanding the database in your SQL Management Studio, then Programmability, Functions, and right-clicking on Scalar-valued Functions and then select "New Scalar-valued Function...".

Notice Table-valued functions? We won't cover it for now as those kind of functions return a whole table like Stored Procedures. Maybe we'll do that in another lesson. But right now let's focus on Scalar-valued functions which will return a single value.

From the template, you may delete the comments if you want.

Our sample function will be returning a string value (varchar in SQL) of the month depending on the passed integer, which can range from 1-12. Let's call our function GetMonthName.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetMonthName]
(
    -- Add the parameters for the function here
    @month int
)

RETURNS varchar(10)
AS
BEGIN
    DECLARE @month_name varchar(10)

    IF @month = 1
    BEGIN
        SET @month_name = 'January';
    END
    ELSE IF @month = 2
    BEGIN
        SET @month_name = 'February';
    END
    ELSE IF @month = 3
    BEGIN
        SET @month_name = 'March';
    END
    ELSE IF @month = 4
    BEGIN
        SET @month_name = 'April';
    END
    ELSE IF @month = 5
    BEGIN
        SET @month_name = 'May';
    END
    ELSE IF @month = 6
    BEGIN
        SET @month_name = 'June';
    END
    ELSE IF @month = 7
    BEGIN
        SET @month_name = 'July';
    END
    ELSE IF @month = 8
    BEGIN
        SET @month_name = 'August';
    END
    ELSE IF @month = 9
    BEGIN
        SET @month_name = 'September';
    END
    ELSE IF @month = 10
    BEGIN
        SET @month_name = 'October';
    END
    ELSE IF @month = 11
    BEGIN
        SET @month_name = 'November';
    END
    ELSE IF @month = 12
    BEGIN
        SET @month_name = 'December';
    END
    ELSE
    BEGIN
        SET @month_name = 'Invalid month number!';
    END


    RETURN @month_name;
END

So to explain, we use the CREATE FUNCTION keyword to create our function followed by our desired name then the parameters (name and type) enclosed in parentheses. Then the RETURNS keyword followed by its type which in our case is a varchar. It's just to say that our function must return a varchar. After that follows our logic which is just a basic if..else.. that will return the corresponding month name or invalid if it is not between 1 to 12. Then of course return that string for our varchar return value.

Here's sample output from our scalar-valued function:

In our 4th example we use the built-in SQL function getdate() to get the current system date then enclose it with another built-in function month() which accepts datetime as parameter and returns the month in integer form, which our function accepts as parameter and finally returns the name of the month.

'Til our next lesson... I hoped you learned/remembered something today.

-k