USE pubs
GO

-- pre-SQL Server 2005 execution context
-- this will execute as the direct caller
CREATE PROCEDURE count_rows(@name NVARCHAR(50))
WITH EXECUTE AS CALLER
AS
EXECUTE('SELECT COUNT(*) FROM ' + @name)
GO

-- this will execute as the stored procedure creator
CREATE PROCEDURE count_rows_as_me(@name NVARCHAR(50))
WITH EXECUTE AS SELF
AS
EXECUTE('SELECT COUNT(*) FROM ' + @name)
GO

-- this will execute as a specific user
CREATE PROCEDURE count_rows_as_fred(@name NVARCHAR(50))
WITH EXECUTE AS 'FRED'
AS
EXECUTE('SELECT COUNT(*) FROM ' + @name)
GO

-- hazards of dynamic SQL
DECLARE @s VARCHAR(50)
SET @s = 'authors;drop table customers'
-- count the rows and drop the table!
count_rows_as_me @s
GO