IIS: IE doesn't auto authenticate

I've been doing a little web development work recently with MVC and have come across a (very) minor issue when deploying this to my test server.

Essentially, I've deployed to the application to the server but when I access it via the IP Address, I get prompted for my windows credentials. However, when accessing it via server name I am not prompted and I get the (much better) user experience of auto authentication.

It appears that the issue is not with IIS or my MVC app but with my browser and the way it handles different URL patterns. When accessing it via server name (eg HTTP//Servername/App) the browser "knows" the site is internal so automatically authenticates whereas the IP address (HTTP//1.1.1.1/App) could be external and so throws the prompt.

Its explained here in David Wang's blog.

T-SQL: Allow low privileged user to Truncate table

The problem with TRUNCATE is that it is a DDL statement rather than the regulation DELETE statement which is DML. As such, its not captured by using the db_datewriter database role and so on the face of it, you're left with two options:

1) Add the user to db_ddladmin role (or db_owner)
2) Grant ALTER table permissions to the table(s) in question

I'm not really a fan of either of these approaches as the first gives far too much control to the user and I try to avoid giving object level permissions if possible.

An alternative is to make use of the EXECUTE AS clause and wrap your TRUNCATE statement within a stored procedure and grant permissions on that instead.

-- create a test database 
CREATE DATABASE TruncateTest
GO
USE TruncateTest
GO
-- create a low privilege user to test with
CREATE USER NoRights WITHOUT LOGIN
GO
-- create a table to test against
CREATE TABLE dbo.TestData (Id INT IDENTITY(1,1))
GO
-- add some dummy data
INSERT INTO dbo.TestData DEFAULT VALUES
GO 20
-- check the data is ok
SELECT COUNT(*) FROM dbo.TestData
GO
-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- try to issue the truncate command
TRUNCATE TABLE dbo.TestData
GO
Msg 1088, LEVEL 16, State 7, Line 1
Cannot find the object "TestData" because it does not exist or you do not have permissions.
-- switch back to my superuser
REVERT
GO
-- create a wrapper procedure to truncate the table
CREATE PROCEDURE dbo.TruncateTestData
AS
TRUNCATE TABLE dbo.TestData
GO
-- grant execute to the test user
GRANT EXECUTE ON dbo.TruncateTestData TO NoRights
GO
-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- execute the procedure
EXEC dbo.TruncateTestData
GO
Msg 1088, Level 16, State 7, Procedure TruncateTestData, Line 4
Cannot find the object "TestData" because it does not exist or you do not have permissions.
-- switch back to my superuser
REVERT
GO
-- modify the procedure to execute in the context of the owner (dbo)
ALTER PROCEDURE dbo.TruncateTestData
WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE dbo.TestData
GO
-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- execute the procedure
EXEC dbo.TruncateTestData
GO
-- switch back to the superuser
REVERT
GO
-- check the data
SELECT COUNT(*) FROM dbo.TestData
GO
-- tidy up
DROP DATABASE TruncateTest
GO

SSMS: Help prevent the "oh ****" moment

Its funny how sometimes you think of something which would be great idea and then the solution falls into your lap. This very thing happened to me the other day after an "oh ****" moment when I executed a query on a production server rather than the test server. Unfortunately, there weren't any fail safes in place (eg restricted privileges) so the query ran successfully and promptly updated a stack of live data. Fortunately, the database in question was not being used and so there was no impact on users but this could so easily have not been the case.

I'm sure I haven't been the only one bitten by this, and it occurred to me that this is far too easy to do. When in fire-fighting mode it can often be the case that I'm flicking between windows with many connection windows open at one time and even the most diligent person can get caught out running a query on the wrong database. When its just a SELECT statement or you issue a query that is in the wrong database thats no big deal but if its a DML (or worse a DDL!!) statement then things can get pretty ugly quickly.

What would have been really helpful for me was an obvious indicator showing which server I was on - probably a different coloured query window (red for Prod please!!) for each database server. And then, on my daily trawl through my twitter timeline I came across this tweet:

RT @tsqlninja: [Blogged] Tools of the trade. My list of top sql server tools tsqlninja.wordpress.com featuring @redgate @MladenPrajdic...
— SQLServerCentral (@SQLServerCentrl) July 25, 2012

I won't go into details of all the cool tools that are mentioned in the post, but the SSMS Tools Pack has (amongst other things) functionality that replicates what I was hoping for. You can essentially configure a colour strip for a database server which is a simple visual aid to help you recognise when you in "the danger zone". 

Even better than this is the fact that you can configure a template for a New Query connection which I tweaked to try and remind the user to be aware of the colour. This means that every time I hit the New Query button the query opens with the text to remind the user to check the server with arrows pointing to the colour bar.

This obviously isn't a full proof solution and is no substitute for having more stringent measures in place to prevent issues such as issuing a delete statement on a production server in error. However, it is simple and effective and is another tool you can have in your armoury to help prevent downtime or dataloss. Every little helps!