Hi.

Have a database whose owner is named MyDbOwner. Need to give select
access to msdb.dbo.sysmai_account and execute permissions on the send
mail sp.

use MyDb
grant select on msdb.dbo.sysmail_account to MyDbOwner

When I execute the above from the 'sa' account, it says user does not
exist or I don't have permission. What to do?

> use MyDb
> grant select on msdb.dbo.sysmail_account to MyDbOwner
>
> When I execute the above from the 'sa' account, it says user does not
> exist or I don't have permission. What to do?

You need to first add the MyDbOwner login to the msdb database:

USE msdb;
CREATE USER MyDbOwner;

I'm not sure why you are using the undocumented sysmail_account table.
Consider using the documented sysmail__account_sp stored procedure
instead. See the Books Online for details.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Riyaz Mansoor" <riyaz.mansoor@gmail.com> wrote in message
news:37c5cf99-1354-4597-a928-396cd865d67d@59g2000hsb.googlegroups.com...
>
> Hi.
>
> Have a database whose owner is named MyDbOwner. Need to give select
> access to msdb.dbo.sysmai_account and execute permissions on the send
> mail sp.
>
> use MyDb
> grant select on msdb.dbo.sysmail_account to MyDbOwner
>
> When I execute the above from the 'sa' account, it says user does not
> exist or I don't have permission. What to do?
>
>
>

> You need to first add the MyDbOwner login to the msdb database:
>
> USE msdb;
> CREATE USER MyDbOwner;
>
> I'm not sure why you are using the undocumented sysmail_account table.
> Consider using the documented sysmail__account_sp stored procedure
> instead. See the Books Online for details.

Thanks.

I'm using sysmail_account to check if the mail account has already
been created. if not then create the necessary accounts etc.

if exists(select * from sysmail_account where name = 'myaccount')

> I'm using sysmail_account to check if the mail account has already
> been created. if not then create the necessary accounts etc.

In that case, you might try the method below. Although the undocumented
table method might work today, it could change in later versions or service
packs and break your code.

BEGIN TRY
EXEC sysmail__account_sp @account_name = @account_name
END TRY
BEGIN CATCH
IF ERROR_MESSAGE() = 'account name is not valid'
BEGIN
EXEC sysmail_add_account_sp @account_name = @account_name...
END
END CATCH

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Riyaz Mansoor" <riyaz.mansoor@gmail.com> wrote in message
news:952d7a0c-7db4-4dba-ada7-380452a6e522@c65g2000hsa.googlegroups.com...
> You need to first add the MyDbOwner login to the msdb database:
>
> USE msdb;
> CREATE USER MyDbOwner;
>
> I'm not sure why you are using the undocumented sysmail_account table.
> Consider using the documented sysmail__account_sp stored procedure
> instead. See the Books Online for details.

Thanks.

I'm using sysmail_account to check if the mail account has already
been created. if not then create the necessary accounts etc.

if exists(select * from sysmail_account where name = 'myaccount')

is there a sp that checks if a login exists?
master.sys.sp_logins <- does not throw an error.


> In that case, you might try the method below. Although the undocumented
> table method might work today, it could change in later versions or service
> packs and break your code.
>
> BEGIN TRY
> EXEC sysmail__account_sp @account_name = @account_name
> END TRY
> BEGIN CATCH
> IF ERROR_MESSAGE() = 'account name is not valid'
> BEGIN
> EXEC sysmail_add_account_sp @account_name = @account_name...
> END
> END CATCH
>

is there a sp that checks if a login exists?
master.sys.sp_logins <- does not throw an error.

You can check sys.server_principals:

IF NOT EXISTS(
SELECT * FROM sys.server_principals
WHERE name = 'MyDomain\MyLogin'
AND type = 'U' --type U=Windows, S=SQL
)
BEGIN
CREATE LOGIN [MyDomain\MyLogin] FROM WINDOWS;
END;


--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Riyaz Mansoor" <riyaz.mansoor@gmail.com> wrote in message
news:f5c3d112-81d4-4e33-97f5-f8fd9266b676@27g2000hsf.googlegroups.com...
is there a sp that checks if a login exists?
master.sys.sp_logins <- does not throw an error.


> In that case, you might try the method below. Although the undocumented
> table method might work today, it could change in later versions or
> service
> packs and break your code.
>
> BEGIN TRY
> EXEC sysmail__account_sp @account_name = @account_name
> END TRY
> BEGIN CATCH
> IF ERROR_MESSAGE() = 'account name is not valid'
> BEGIN
> EXEC sysmail_add_account_sp @account_name = @account_name...
> END
> END CATCH
>

Hi.

I'm running this thru osql and the *exec* statement pretty much screws
up the output file (listing the table). I have looked at both osql and
sqlcmd but there seems to be no option to hide a table listing. What
can I do here?

> > BEGIN TRY
> > EXEC sysmail__account_sp @account_name = @account_name
> > END TRY
> > BEGIN CATCH
> > IF ERROR_MESSAGE() = 'account name is not valid'
> > BEGIN
> > EXEC sysmail_add_account_sp @account_name = @account_name...
> > END
> > END CATCH

Also, i'm running osql from a dos batch file (below). Problem is after
the the first osql call (and completes successfully) batch processing
stops. I have tried adding a 'start' command infront of the 'osql'
command but that does not work either. 'osql' (or sqlcmd) has no
related switch. Am out of ideas. What to do?


cls

sqlrun.bat ..\base\setup.sql
sqlrun.bat ..\base\functions.sql
sqlrun.bat ..\base\sps.sql

sqlrun.bat ..\base\setting\tables.sql
sqlrun.bat ..\base\setting\functions.sql
sqlrun.bat ..\base\setting\sps.support.sql
sqlrun.bat ..\base\setting\sps.logic.sql

type sqlrun.txt

**********
sqlrun.bat:
osql -Usa -Psa -n -S RIYAZ-MANSOOR\SQLEXPRESS -d TradeNet -i %1 >>
sqlrun.txt

> I'm running this thru osql and the *exec* statement pretty much screws
> up the output file (listing the table). I have looked at both osql and
> sqlcmd but there seems to be no option to hide a table listing. What
> can I do here?

A workaround is to insert the EXEC results into a table:

SET NOCOINT ON
DECLARE @results TABLE
(
account_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),
use_default_credentials bit,
enable_ssl bit
)
BEGIN TRY
INSERT INTO @results
EXEC msdb.dbo.sysmail__account_sp @account_name = @account_name
END TRY
BEGIN CATCH
IF ERROR_MESSAGE() = 'account name is not valid'
BEGIN
EXEC sysmail_add_account_sp @account_name = @account_name...
END
ELSE
BEGIN
--your error handling code for unexpected errors
END
END CATCH


--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Riyaz Mansoor" <riyaz.mansoor@gmail.com> wrote in message
news:57b7b2d3-9407-407c-bba7-e042e2552c40@k37g2000hsf.googlegroups.com...
> Hi.
>
> I'm running this thru osql and the *exec* statement pretty much screws
> up the output file (listing the table). I have looked at both osql and
> sqlcmd but there seems to be no option to hide a table listing. What
> can I do here?
>
>> > BEGIN TRY
>> > EXEC sysmail__account_sp @account_name = @account_name
>> > END TRY
>> > BEGIN CATCH
>> > IF ERROR_MESSAGE() = 'account name is not valid'
>> > BEGIN
>> > EXEC sysmail_add_account_sp @account_name = @account_name...
>> > END
>> > END CATCH
>
> Also, i'm running osql from a dos batch file (below). Problem is after
> the the first osql call (and completes successfully) batch processing
> stops. I have tried adding a 'start' command infront of the 'osql'
> command but that does not work either. 'osql' (or sqlcmd) has no
> related switch. Am out of ideas. What to do?
>
>
> cls
>
> sqlrun.bat ..\base\setup.sql
> sqlrun.bat ..\base\functions.sql
> sqlrun.bat ..\base\sps.sql
>
> sqlrun.bat ..\base\setting\tables.sql
> sqlrun.bat ..\base\setting\functions.sql
> sqlrun.bat ..\base\setting\sps.support.sql
> sqlrun.bat ..\base\setting\sps.logic.sql
>
> type sqlrun.txt
>
> **********
> sqlrun.bat:
> osql -Usa -Psa -n -S RIYAZ-MANSOOR\SQLEXPRESS -d TradeNet -i %1 >>
> sqlrun.txt

For Info. I'd just like to add that the following problem is not a
osql/sqlcmd problem. Its a Batch programming problem. To solve it,
just add 'CALL' in front of 'sqlrun.bat' statement.

> Also, i'm running osql from a dos batch file (below). Problem is after
> the the first osql call (and completes successfully) batch processing
> stops. I have tried adding a 'start' command infront of the 'osql'
> command but that does not work either. 'osql' (or sqlcmd) has no
> related switch. Am out of ideas. What to do?
>
> cls
>
> sqlrun.bat ..\base\setup.sql
> sqlrun.bat ..\base\functions.sql
> sqlrun.bat ..\base\sps.sql
>
> sqlrun.bat ..\base\setting\tables.sql
> sqlrun.bat ..\base\setting\functions.sql
> sqlrun.bat ..\base\setting\sps.support.sql
> sqlrun.bat ..\base\setting\sps.logic.sql
>
> type sqlrun.txt
>
> **********
> sqlrun.bat:
> osql -Usa -Psa -n -S RIYAZ-MANSOOR\SQLEXPRESS -d TradeNet -i %1 >>
> sqlrun.txt