0

I created a simple script to send us disk alerts when a disk is almost full ( i know there are better ways to do this, this is just a simple question )

EXECUTE AS login = 'SA'
CREATE TABLE ##DiskSpaceSendMail (
    drive char(1),
    [MB free] int
    )
INSERT INTO ##DiskSpaceSendMail 
EXEC xp_fixeddrives 

IF EXISTS ( SELECT * FROM ##DiskSpaceSendMail /*where [MB free] < XXXX */)
BEGIN
    DECLARE @VERSIONSQL VARCHAR(100)
    DECLARE @MSG VARCHAR(500)
    DECLARE @SUBJ varchar(500)

    SET @VERSIONSQL = (SELECT @@SERVERNAME)
    SET @MSG =+ 'A disk on '+@VERSIONSQL+' is almost without space.'
    SET @SUBJ = (SELECT 'Low disk space on '+@VERSIONSQL+'.')

    EXEC msdb.dbo.sp_send_dbmail
    @recipients='EMAILEMAILEMAIL@EMAIL;',
    @subject = @SUBJ,
    @body = @MSG,
    @profile_name='profile_name',
    @query = 'SELECT * FROM ##DiskSpaceSendMail'
END
GO
DROP TABLE ##DiskSpaceSendMail

Without the EXECUTE WITH SA I receive this error:

(6 row(s) affected)
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Msg 15404, Level 16, State 19, Server SQLPRODCL, Line 1
Could not obtain information about Windows NT group/user 'zzzzzz\xxxxxx', error code 0x5.

but how can I execute EXECUTE AS LOGIN ='SA' ? the error says it can't find information about my login, so I should not be able to execute as SA right?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.