What runs CREATE PROCEDURE dbo.sysmail_help_admin_account_sp?

Question

What runs CREATE PROCEDURE dbo.sysmail_help_admin_account_sp?

webrunner

SSC-Dedicated

Points: 31866
More actions
August 20, 2008 at 9:15 am

#191337

I just saw a process blocking twice in the space of 10 minutes, and I used this query to try to get the SQL used by the SPID.
DECLARE @handle VARBINARY(64)
SELECT @handle = sql_handle from sys.sysprocesses where spid = [sipd]
SELECT text FROM sys.dm_exec_sql_text(@handle)
I got this SQL:
CREATE PROCEDURE dbo.sysmail_help_admin_account_sp @account_id int AS SET NOCOUNT ON DECLARE @rc int, @acc_id int, @name sysname, @description nvarchar(256), @email_address nvarchar(128), @display_name nvarchar(128), @replyto_address nvarchar(128), @servertype sysname, @servername sysname, @port int, @username nvarchar(128), @passwordsize int, @cryptpassword varbinary(1024), @credential_id int, @use_default_credentials bit, @enable_ssl bit SET @passwordsize = 0 EXEC @rc = msdb.dbo.sysmail_verify_account_sp @account_id, NULL, 1, 0, NULL IF @rc <> 0 RETURN(1) SELECT @acc_id = a.account_id, @name = a.name, @description = a.description, @email_address = a.email_address, @display_name = a.display_name, @replyto_address= a.replyto_address, @servertype = s.servertype, @servername = s.servername, @port = s.port, @username = s.username, @credential_id = s.credential_id, @use_default_credentials = s.use_default_credentials, @enable_ssl = s.enable_ssl FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s WHERE (a.account_id = s.account_id) AND (a.account_id = @account_id) --get the encrypted password if required IF(@username IS NOT NULL) BEGIN DECLARE @cred TABLE( INT, blob VARBINARY(1024)); INSERT @cred EXEC @rc = master.dbo.sp_PostAgentInfo @credential_id IF @rc <> 0 BEGIN RETURN(1) END SELECT @passwordsize = , @cryptpassword = [blob] FROM @cred END --All done return result SELECT @acc_id as 'account_id', @name as 'name', @description as 'description', @email_address as 'email_address', @display_name as 'display_name', @replyto_address as 'replyto_address', @servertype as 'servertype', @servername as 'servername', @port as 'port', @username as 'username', @passwordsize as 'password_size', @cryptpassword as 'password_crypt', @use_default_credentials as 'use_default_credentials', @enable_ssl as 'enable_ssl' RETURN(0)
Could someone let me know what this procedure is? Is it routine or some kind of threat/exploit?
Also, if not a threat, is it still related to possible performance/blocking problems (such Database Mail related, etc.)?
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Actually, nothing's running the create proc.

When you get the sql text from sys.dm_exec_sql_text and the connection is running a stored procedure, exec_sql_text returns the create statement, rather than the EXEC

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

webrunner SSC-Dedicated Points: 31866 More actions · Answer 2

GilaMonster (8/20/2008)
Actually, nothing's running the create proc.
When you get the sql text from sys.dm_exec_sql_text and the connection is running a stored procedure, exec_sql_text returns the create statement, rather than the EXEC

Thanks. Is there any way to find out what did call this procedure? And whether it was blocking or was hung up as a result of another process that was blocking it?

Also - and sorry for this digression - is there a way to find out whether the DB or the web server is the root cause of an outage? Sometimes our web server is not responding but there are no blocks; other times the web server is not responding and there are blocks. I'm having a hard time pinpointing the root cause in each case. I guess sometimes it's the db and sometimes it isn't, but I want to be more confident about identifying each kind of case.

Thanks for any advice!

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 3

just for fun, i created a simple test procedure in one session, then executed it in a second session, then executed the original code in a third session:

DECLARE @handle VARBINARY(64)

SELECT @handle = sql_handle from sys.sysprocesses where spid = [sipd]

SELECT text FROM sys.dm_exec_sql_text(@handle)

i didn't see the CREATE PROCEDURE statement... i saw the EXEC (with the passed parameters). so now i'm wondering... is there a server/db/session setting that controls what sys.dm_exec_sql_text returns?

webrunner: what happens if you do what i did? create a simple proc in one session, execute it in another, then execute your code from a third?

i guess i'm more curious than anything...