Security in SQL Server 2005 – unsetapprole

One of the things folks would always ask during the Ascend program was "anything new for application roles? do they support connection pooling yet?". Well, it the most recent CTP (June, July?) there is.

You can unset application roles now in addition to setting them. To unset you need to create a cookie, using an alternate form of sp_setapprole. It looks like this:

sp_addapprole 'myapp', 'StrongPW1'

DECLARE @theCookie varbinary(256)
EXEC sp_setapprole 'myapp', 'StrongPW1',
     @fCreateCookie = true, @cookie = @theCookie OUTPUT
— Check user, should be myapp
— now, unset it
EXEC sp_unsetapprole @theCookie
— Check user should be original user

So does it now support connection pooling with ADO.NET or OLE DB/ODBC? I'd think the capability exists, but because this is a recent feature addition and they'd have to store the cookie in the pooling code somewhere, to use with sp_reset_connection, I don't think its built in yet. But, if you remember to store the cookie and unset it yourself…

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.