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
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
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
Feel free to execute the proc at this point just to be sure that you get results.
Continue reading on SirSQL.net.