none
Need help with granting acces to a procedure

    Question

  • Hi,

    I am  beginner in SQL server world. Logging in as SA, I would like to grant access for one of the system procedures to another user id ? Can anyone help me here with correct syntax/ way to do the same ?

    Thanks,
    Arg82
    Thursday, February 25, 2010 5:42 AM

Answers

  • No need to grant access to this. What yu do is to add the login who is supposed to use sp_send_dbmail as a user in msdb, and then give this user the DatabasMailUserRole (in msdb). Also, make sure that you have created a mail profiler which this user has permissions to use.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, February 25, 2010 11:02 AM
  • Thanks alot TiborK. It has worked. Below are the supporting queries.

    To add user to a DATA base –

     

    Use <Data base to which user has to be added>

     

    EXEC sp_adduser '<user name>'

     

    To give/ add a role to the user who has got added –

     

    sp_addrolemember @rolename = 'DatabaseMailUserRole',

    @membername = '<user name>'

    Friday, February 26, 2010 2:06 PM

All replies

  • You can do something like this

    GRANT EXECUTE ON procedure_name to user_name


    Thanks, Leks
    Thursday, February 25, 2010 5:54 AM
  • I tried this already but doesn't work.. Below is the error.

    "Msg 15151, Level 16, State 1, Line 1

    Cannot find the user 'wfm_user', because it does not exist or you do not have permission."

    I tried doing this after logging in as system administrator

    Thursday, February 25, 2010 6:06 AM
  • make sure the user is already added as a user in the database.

    USE DATABASE
    GO
    CREATE USER username FROM LOGIN loginname

    Which stored proc you are trying to grant access to ?
    Thanks, Leks
    Thursday, February 25, 2010 6:20 AM
  • I tried a query from administrator login to change the user name but even SA is not having enough permission. I am trying to grant access to msdb.dbo.sp_send_dbmail.

     

     

    Thursday, February 25, 2010 9:07 AM
  • No need to grant access to this. What yu do is to add the login who is supposed to use sp_send_dbmail as a user in msdb, and then give this user the DatabasMailUserRole (in msdb). Also, make sure that you have created a mail profiler which this user has permissions to use.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, February 25, 2010 11:02 AM
  • Thanks alot TiborK. It has worked. Below are the supporting queries.

    To add user to a DATA base –

     

    Use <Data base to which user has to be added>

     

    EXEC sp_adduser '<user name>'

     

    To give/ add a role to the user who has got added –

     

    sp_addrolemember @rolename = 'DatabaseMailUserRole',

    @membername = '<user name>'

    Friday, February 26, 2010 2:06 PM
  • Glad it worked. FWIW, as of 2005, we are recommended to use CREATE USER instead of sp_adduser.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Friday, February 26, 2010 4:45 PM
  • I was able to fidn the way to send mails but for some reason my mails are getting queued. Below is the error it says. ----- The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-03-01T11:32:42). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 10.232.5.105:25). ) ----- The IP address of SQL server is already added/ mapped to email server. Still I face this issue. Can any one help me to find if I have to do anything from SQL server set up side ? FYI - I have also configured database mail.
    Tuesday, March 09, 2010 11:34 AM
  • You should talk to your AMTP/mail server people about this. Clearly (see error message) SQL Server tries to connect to the SMTP server, which doesn't allow this access. Your mail server people should know what priviliges etc are needed for this (if not, you need new mail server people).
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, March 09, 2010 2:36 PM
  • I already talked to them and requested them to add the ip addreess of computer where SQL Server resides to mail server. They came back saying that configuratio is complete and they can receive test mail from that computer. So, computer in which SQL server resides (as server) is all set for sending mails. So, do you think that I need to change some thing here ?
    Thursday, March 11, 2010 8:43 AM
  • Any luck on this.. ??
    Wednesday, March 17, 2010 9:21 AM
  • Show that mail error you posted to your mail experts, along with how you configured the SMTP Account (server name, credential etc). This should allow them to make sure you have the right info along with them configuring the right stuff.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, March 17, 2010 9:47 AM
  • The highlighted error is same as what was mentioned above. Moreover I have given the IP address of the computer where SQL server resides and requested them to map that Ip to mail server. They say that they are receiving test mails from machine mapped to that IP given.

    Anyways thanks alot for great involvement in cracking this issue. Your above explanation tells me that there are no issues with SQL server coding but an issue with mapping the SQL server to mail server.

    Please let me know if you have any ideas on this.

    Thursday, March 25, 2010 10:29 AM
  • Just as an FYI: As of 2005, MS recommend that we use CREATE USER instead of sp_adduser (which is there for back-compatibility).
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, August 26, 2010 6:51 AM