Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267

    Unanswered: User Permission Issue

    To All--

    Complete SQL Server 2012 newbie so please bear with me. I have a database with 2 tables. I created a server login and then a user which I assigned to the database. The user should have full access (SELECT, UPDATE, DELETE, etc) to both tables.

    The user can do everything to table 1 but can only SELECT and INSERT into table 2. I did not do any GRANT/REVOKE statements on either table.

    Any ideas? Does this even make sense? Thanks for reading.

    C

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing more about what is happening I can't be certain, but I'd be willing to bet that this user created the table that they can access and another user created the table that this user can't access.

    If you show us some code that succeeds and some that fails with the error numbers/messages that indicate failure, we can make better guesses about how to fix your problem or problems.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Thanks for the reply but I guess I didn't explain this situation correctly.

    I use MS Access as a front end to the SQL server. I created both tables and then created a user login using SQL SMS that should have read, write and delete permissions. When connecting to table 1 with the user login I created you are able to do everything on the table. However, when connecting to table 2 you can only SELECT and INSERT. I didn't do anything different with permissions for table 1 or 2.

    To confuse me even more I started over and created another database with the same structure - two tables. I used an INSERT INTO query to populate the tables with the data from the original database. Table 1 has around 79 rows and table 2 has around 850,000. Again I gave the user login rights to the new database. And again I'm able to do everything in table 1 but only SELECT and INSERT on table 2.

    Any thoughts? Thanks for your time.

    C

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Are you getting any errors? What happens when you execute said code directly in SSMS. Other than that, I am going to need to see some code and maybe some DDL.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •