CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Lefteris Gkinis
Lefteris Gkinis

Reputation: 1259

EXEC dbo.sp_executesql @statement

I have created a number of application roles, and automatically the SQL Server database created the following:

declare @idx as int
declare @randomPwd as nvarchar(64)
declare @rnd as float
select @idx = 0
select @randomPwd = N''
select @rnd = rand((@@CPU_BUSY % 100) + ((@@IDLE % 100) * 100) + 
       (DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))
while @idx < 64
begin
   select @randomPwd = @randomPwd + char((cast((@rnd * 83) as int) + 43))
   select @idx = @idx + 1
select @rnd = rand()
end
declare @statement nvarchar(4000)
select @statement = N'CREATE APPLICATION ROLE [AppRole_01] WITH DEFAULT_SCHEMA = [AppRole_01], ' + N'PASSWORD = N' + QUOTENAME(@randomPwd,'''')
EXEC dbo.sp_executesql @statement

GO

Now here I see EXEC dbo.sp_executesql @statement and I can't understand

  1. Why is calling this procedure?
  2. I've searched the database server and I didn't find any procedure sp_executesql especially dbo.sp_executesql

Is there someone to guide me thru this issue?

Upvotes: 4

Views: 4783

Answers (1)

jean
jean

Reputation: 4350

It's a system SP. It comes shipped with SQL Server.

http://technet.microsoft.com/en-us/library/aa933299(v=sql.80).aspx

The query you posted is creating a dynamic SQL and executing it.

Edit Any visual tool you use to manage your DB is, behind the curtains, creating that kind of command and sending it to the engine (and is it doing all the job all the time). You can see it by running a profiler.

You cannot find that system SP and it's good because that way it's not easy to hack in the RDBMS and do nasty things. Please don't even try to change things by updating/altering system objects.

Upvotes: 4

Related Questions