Security functions not supported in SQL Azure

While trying to help my colleague today to set and test permission on an object in SQL Azure. I received ‘Deprecated feature ‘SETUSER’ is not supported in this version of SQL Server.‘ error. A quick google search revealed that there are a number of security functions not available in SQL Azure. The full list is available here.

To perform action as another user, instead of using ‘SETUSER’ you can use ‘EXECUTE AS‘ clause. Below is an example I copy from MSDN.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE HumanResources.uspEmployeesInDepartment
@DeptValue int
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;
SELECT e.BusinessEntityID, c.LastName, c.FirstName, e.JobTitle
FROM Person.Person AS c
INNER JOIN HumanResources.Employee AS e
ON c.BusinessEntityID = e.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE edh.DepartmentID = @DeptValue
ORDER BY c.LastName, c.FirstName;
GO

-- Execute the stored procedure by specifying department 5.
EXECUTE HumanResources.uspEmployeesInDepartment 5;
GO

You can also find a list of deprecated database engine features in SQL Server 2008 R2 here.