Post reply

LOGIN OR ROLE SETUP HELP

  • January 11, 2008 at 12:46 pm

    #178098

    hi everone.

    let me first say i play a developer on TV. I am not an expert but have a good grasp.

    Here is what i need help with. I have a SQL 2005 DB with an MS Access2002 front end. In code through a dsnless connection string i create a connection object:

    cnSqlData.ConnectionString = "Provider=SQLOLEDB;Server=myServer;Database=mktbsk;Trusted_Connection=yes;"

    i am trying to execute a stored proceedure on the server with the following:

    cmd.CommandText = "procSkuAttach"

    * cmd.Parameters("@SkuNum").Type = adBigInt

    cmd.Parameters("@FromDate").Type = adBigInt

    cmd.Parameters("@ToDate").Type = adBigInt

    cmd.Parameters("@SkuNum").Direction = adParamInput

    cmd.Parameters("@FromDate").Direction = adParamInput

    cmd.Parameters("@ToDate").Direction = adParamInput

    cmd.Parameters("@SkuNum").Value = txtSkuNum.Value

    cmd.Parameters("@FromDate").Value = fIntDate(cboTimeSelect.Column(1))

    cmd.Parameters("@ToDate").Value = fIntDate(cboTimeSelect.Column(2))

    rsRptData.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

    this all works fine when I run this (click event code on a form) i believe because i am set up with the server role of sysadmin and a database role of db_owner.

    when i add a login and set the login with a db_datareader role on the database and a blank server role the code breaks on the line asterixed telling me the ordinal doesn't exist. When i change this login for this user to a db_owner on the database, the code executes.

    again, not a DBA but it seems like i don't want users to be db_owners.

    are there roles, logins. etc properties i need to define to make this work.

    Let me know if you need any more info. I am sitting at my desk anxiously awaiting some help.

    thanks

    Peter

  • January 11, 2008 at 1:15 pm

    #767299

    You should give execute permission to the stored Proc that the new user is trying to execute.

    -Roy

  • January 11, 2008 at 2:00 pm

    #767317

    That did the trick my friend.

    I owe u a beer!!!!!

    😀

    tyvm

    Peter

  • January 11, 2008 at 2:04 pm

    #767319

    Glad I could be of help

    -Roy

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

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