Tutorial by Examples

In some cases, you would need to execute SQL query placed in string. EXEC, EXECUTE, or system procedure sp_executesql can execute any SQL query provided as string: sp_executesql N'SELECT * FROM sys.objects' -- or sp_executesql @stmt = N'SELECT * FROM sys.objects' -- or EXEC sp_executesql N'SEL...

You can execute SQL query as different user using AS USER = 'name of database user' EXEC(N'SELECT * FROM product') AS USER = 'dbo' SQL query will be executed under dbo database user. All permission checks applicable to dbo user will be checked on SQL query.

Dynamic queries are SET @sql = N'SELECT COUNT(*) FROM AppUsers WHERE Username = ''' + @user + ''' AND Password = ''' + @pass + '''' EXEC(@sql) If value of user variable is myusername'' OR 1=1 -- the following query will be executed: SELECT COUNT(*) FROM AppUsers WHERE Username = 'myusername...

In order to avoid injection and escaping problems, dynamic SQL queries should be executed with parameters, e.g.: SET @sql = N'SELECT COUNT(*) FROM AppUsers WHERE Username = @user AND Password = @pass EXEC sp_executesql @sql, '@user nvarchar(50), @pass nvarchar(50)', @username, @password Second ...