Executing xp

[sarav2k]

Hi all,

I have a situation. I have a test database and a test user who is dbo to this DB. Now If this user wants to execute SP\XP in master DB , what type of minimal permission should I give him?

One more thing , when I execute xp_sendmail , the batch became indefinite. I checked sql mail with proper profile.

Thanks in advance
Sarav

 

[tlbroadbent]

The user should only need execute permission on the stored procedure.

Explain what you mean by "the batch became indefinite". Do you mean the query did not terminate? Or did it timeout? Or did you get an error message? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sarav2k]

Hi

Well. Can you tell me how to give only execute permissions to sp & xp.

Regarding my 2nd question,
I meant that the bacth file(contains the xp_sendmail statement) didn't terminate at all.

let me explain what I did.

When I tried first time with xp_sendmail command. it said Mail Sent. But the user didn't get it. so I checked sql mail service and found profile was not selected(I don't know how come it returned the msg Mail Sent). So then I selected a profile and tested it successfully. After this only the batch file didn't finish the execution. my basic questions are:


1. In xp_sendmail command, should we give only username or full mail id like xxx@yyy.com

2. Do we need to give xp_startmail before xp_sendmail?

Thanks a lot for your reply.

sarav

 

[osjohnm]

Hi sarav2k

To grant execute permissions on a stored proc, first add the the user to the specific database as a user if the user doesn't exist.

Drill down through databases> database name>users>right click> add new database user.

Stored procs are similiar, find the stored proc, right click> all tasks>manage permissions> tick the execute box of the user.

There are also stored procs that will add users to a database

sp_adduser [ @loginame = ] 'login'
[ , [ @name_in_db = ] 'user' ]
[ , [ @grpname = ] 'group' ]

--use for sql logins

sp_grantdbaccess [@loginame =] 'login'
[,[@name_in_db =] 'name_in_db' [OUTPUT]]

--use for adding nt account logins

Then use the GRANT command to allow execute permissions:

GRANT EXECUTE ON [dbo].[xp_sendmail] TO [login]
GO

I don't use SQLMail that often but from what I understand is you have to execute xp_startmail to start the mail sessions, then xp_sendmail , then xp_stopmail to stop the m,ail sessions again.

Hope this helps

John

 

[tlbroadbent]

In SQL 2000 xp_sendmail will start SQL Mail if it isn't running. Stopping and starting SQL Mail can lead to problems. I recommend having mail start when SQL Server starts and not stopping it at any time.

In SQL 7 you only need to start mail if it isn't already running. Normally, mail should start when SQL Server starts. When we used SQL 7 (SP2) there were bugs that required stopping and starting server from time to time. I don't know if those defects were corected in later service packs.

If sending mail with xp_sendmail you can use the full EMail address of the recipients. If the Outlook client is loaded and you use exchange server you can use the name as it appears in the address books. You must provide enough of the name to allow name resolution without ambiguity.

I'm not certain what causes xp_sendmail to hang in your case. We encountered that problem in SQL 7 and early releases SQL 2000. Mail would hang for one process and all other processes that used SQL Mail would either hang or fail. The only recourse was rebooting the server. Make sure you have installed the latest service pack for your version of SQL Server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.