SQL Server - EXEC(@sql) vs. EXEC sp_executesql(@sql) with EXECUTE AS

Asked By Jesse Reich on 24-Apr-09 01:03 PM

I'm writing a script to test user permissions. I noticed that when I try to use the EXECUTE AS statement in a string, it works in sp_executesql but not EXEC(). Does anyone know why?:

-- logged in as sysadmin

USE master

GO

IF NOT EXISTS (SELECT * FROM sys.databases WHERE [name] = 'TestDatabase')

CREATE DATABASE TestDatabase

GO

USE TestDatabase

GO

SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = 'TestUser')

CREATE USER TestUser WITHOUT LOGIN

DECLARE

@sqlcmd varchar(max),

@nsqlcmd nvarchar(max)

SET @sqlcmd = 'EXECUTE AS User = ''TestUser'''

SELECT

USER_NAME() AS currentuser,

@sqlcmd AS sqlcmd

EXEC(@sqlcmd)

SELECT USER_NAME() AS currentuserIn_EXEC

SET @nsqlcmd = N'EXECUTE AS User = ''Testuser'''

EXEC sp_executesql @nsqlcmd

SELECT USER_NAME() AS currentuserIn_spExec

REVERT

Results below...

currentuser                      sqlcmd

------------------------------------------------------------------------------------------------------

dbo                               EXECUTE AS User = 'TestUser'



currentuserIn_EXEC

-------------------------------------------------------------------------------------------------------

dbo



currentuserIn_spExec

--------------------------------------------------------------------------------------------------------

TestUser

Exec is less problematic than SP_ExecuteSQL.

[)ia6l0 iii replied to Jesse Reich on 24-Apr-09 01:11 PM
"EXECUTE AS is actually two things. It is a clause you can add to a stored procedure or any other SQL module, and that is what you can use to grant permissions to non-privileged users. But there is also astatement EXECUTE AS, and we will look at the statement before we turn to the clause.

There are two apparent uses for the EXECUTE AS statement:
  • A privileged user can use EXECUTE AS to test queries and procedures as another user, without having to open a new query window. This can be very handy, and all example scripts in this article useEXECUTE AS for this purpose.
  • To implement "application proxies". In this case, the application authenticates the users outside the server. The application connects to the server with a proxy login that has IMPERSONATE rights on the real users and then issues EXECUTE AS to run as them. When you create a user in SQL 2005, you can specify the clause WITHOUT LOGIN to create a user exists in the database only. Thus, you can implement a solution where the real users do not need any sort of direct access to SQL server."
You can find more info at  http://www.sommarskog.se/grantperm.html

Santhosh N replied to Jesse Reich on 24-Apr-09 01:17 PM
SP_EXECUTESQL  allows you to create parameterized queries and that is the difference between two...

Check here for more info..

http://michaelsync.net/2006/07/04/exec-exec-sp_executesql
http://technet.microsoft.com/en-au/library/cc966425.aspx

Descriptions

Ravenet Rasaiyah replied to Jesse Reich on 24-Apr-09 01:29 PM
Hi

sp_executesql

sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.

This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.

Usage:

Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'

Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'

EXEC

  This is normal exexcute statemnet in  Sql server

Usage :

Declare @SQL VarChar(1000)

SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)

thank you