Fun With Windows Logins In SQL

Sometimes you come across quirkiness when playing with SQL Server. Once in a while those things are related to security. This happens to be one of those times.

Release My Code

I was provided with a script by the dev team to create a new proc and grant permissions on that proc to a Windows login that already had access to SQL. No big deal. I executed the script, received no errors and happily went about the rest of my day. A couple of hours later I got an email from the developer saying that when they attempted to call the proc they were getting an error stating that the server principal was not able to access the database under the current security context.

Fix It Damn You

After much troubleshooting to no avail I ended up scripting out all the users permissions, dropping and recreating the user, then applying those permissions back again. Everything worked and the developers went away happy. I myself was not happy and so restored an old copy of the database to a test server to try and find out what the deal was. I found something interesting. Join me in a short but fascinating journey into the world of the Windows login…

Setup A Test

The following is based upon test cases in SQL 2008 and 2008 R2. I have not tested on SQL 2005 and so do not know if the behavior might be different.

Create yourself a new shiny Windows login on your machine and then. Mine’s called SQL1 (because I’m original like that). Now add that as a login within SQL.

CREATE LOGIN [AnonyLPTPSQL1] FROM WINDOWS
GO

1 2	CREATE LOGIN [AnonyLPTPSQL1] FROM WINDOWS GO

Just because validation is good it’s worth double checking that the login exists

SELECT Name, type_desc FROM sys.server_principals 
    WHERE name = 'AnonyLpTpSQL1'
    ORDER BY name

SELECT Name, type_desc FROM sys.server_principals

WHERE name = 'AnonyLpTpSQL1'

ORDER BY name

Looks like we’re good on that front.

Now let’s create a dummy database for our security test and add a table and proc to that.

CREATE DATABASE SecurityTest
GO

USE SecurityTest
GO

CREATE TABLE dbo.QuickTest (ID INT, Col2 VARCHAR(30))
GO

INSERT INTO dbo.QuickTest VALUES (1, 'some text'), (2, 'different text');
GO

CREATE PROCEDURE dbo.ShowData
AS
    SELECT ID, Col2 FROM dbo.QuickTest
GO

CREATE DATABASE SecurityTest

USE SecurityTest

CREATE TABLE dbo.QuickTest (ID INT, Col2 VARCHAR(30))

INSERT INTO dbo.QuickTest VALUES (1, 'some text'), (2, 'different text');

CREATE PROCEDURE dbo.ShowData

SELECT ID, Col2 FROM dbo.QuickTest

Feel free to execute the proc at this point just to be sure that you get results.

Continue reading on SirSQL.net.