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');

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.

 Any Users There?

We’ve not added any users to this database, and most certainly that new login we just created isn’t going to be there. In the interests of being sure let’s check real quick.

SELECT Name FROM SecurityTest.sys.database_principals 

    WHERE name = 'AnonyLpTpSQL1'

    ORDER BY name

Nope, no user there.

Now For Some Magic

If we try to grant execute on the ShowData proc to that login it’s going to fail because that user doesn’t exist in the database, right? I know for sure that’s what happens with SQL logins, after all I’ve seen the “Cannot find the user ‘<username>’, because it does not exist or you do not have permission” error on countless occasions.

Let’s try it anyway and see what happens

GRANT EXECUTE ON SecurityTest.dbo.ShowCols TO [AnonyLPTPSQL1]

Yup, there’s the error…wait, what? There’s no error, it was successful. That must be a mistake surely?

Well let’s check the database principals again

SELECT Name FROM SecurityTest.sys.database_principals 

    WHERE name = 'AnonyLpTpSQL1'

    ORDER BY name

So it appears as though a user has been created for the login without us needing to specify it to do so. In actual fact it’s gone one step further and also created a schema.

SELECT name, principal_id FROM SecurityTest.sys.schemas    

    WHERE name = 'AnonyLpTpSQL1'

But Wait…

Let’s try to execute the proc under the context of that user

EXECUTE AS USER = 'AnonyLpTpSQL1';

EXEC SecurityTest.dbo.ShowCols;

Hey, that’s the error that the devs were reporting they were having. This makes no sense though. We granted permissions on the proc and there’s a user there and everything, what gives?

Security Fundom

So there’s a user and there’s permissions on the proc but we get an error. Most strange. Well, actually I know we granted permissions on the proc, but did they actually make it there? We can use sys.database_permissions to double check this. In this case we’ll check permissions for all the Windows users in the database.

USE SecurityTest

GO

SELECT 

      dpri.name

    , dper.class_desc

    , object_name(dper.major_id) as ObjectName

    , permission_name 

FROM 

    sys.database_permissions dper

        INNER JOIN sys.database_principals dpri

            ON dper.grantee_principal_id = dpri.principal_id

WHERE dpri.type = 'U'

Yup, the permissions are definitely there. Execute on ShowCols. So that’s not an issue.

Note, there’s another row in the table showing CONNECT permissions for dbo. This permission is missing for our login. Interesting. Let’s add that for our user.

GRANT CONNECT ON DATABASE::SecurityTest TO [AnonyLPTPSQL1]

Running the permissions script again now gives 3 rows.

Now if we attempt to run the proc under the context of the login.

EXECUTE AS USER = 'AnonyLpTpSQL1';

SELECT USER_NAME(); -- Confirm we're using the right user

EXEC SecurityTest.dbo.ShowCols;

It worked!

So What Is Going On Here?

By attempting to assign permissions to an object in a database to a Windows login a schema and user are automatically created however the login is not granted the right to connect to the database in order to be able to utilize those permissions. We can easily grant the ability to connect to the database that is required for that login to use the already assigned permissions.

This is a very strange behavior (especially given that SQL logins do not work the same way). I’m sure that there is reason behind this and use cases, even though I’ve not been able to find any documentation describing this.

If you’ve run across this before, have use cases or know of documentation describing the behavior please let me know in the comments and I’ll update the post.