Sign in
 
HomeLibraryWikiLearnGalleryDownloadsSupportForumsBlogs

none
SELECT permission denied on object

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    I'm trying to upgrade my SQL 2000 to 2005 and use it with a web site. I've copied the DB from a SQL 2000 server machine to a 2005 machine, attached the DB to the SQL server using the relative function in Management Studio, but I still continue to get the same error:
    [Microsoft][SQL Native Client][SQL Server]SELECT permission denied on object 'Users', database 'YouPlayIt', schema 'dbo'.

    using this query: SELECT UserId FROM USERS.

    Querying the DB from an ASP page with the query "SELECT CURRENT_USER", the system return the expected value: NKNLEPETD0\IUSR_NKNLEPETD0

    In SQL server, I've created a user with this name (taking it from the users list), and granted full access to all the tables of the DB.

    In the permission Tab of the USERS table the NKNLEPETD0\IUSR_NKNLEPETD0 have all the grant checked.

    Which other permission do I have to specify in order to have access to the data ???

    Thank you,
    Nicola Lepetit.
    www.youplay.it

    Tuesday, February 07, 2006 1:42 PM
    |

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote
    Hi,

    I fixed the error by assigning the default schema for teh database to DBO.
    eg: Login to SQL using SA > Security > User > select username > properties
    Make sure that 'Users = username' & 'Schema = DBO'

    Let me know if you have any queries. Smile

    --Viraj
    Sunday, July 29, 2007 11:45 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Can you execute the following query in the YouPlayIt database:

    select object_name(major_id) as object,
     user_name(grantee_principal_id) as grantee,
     user_name(grantor_principal_id) as grantor,
     permission_name,
     state_desc
    from sys.database_permissions
     where major_id = object_id('Users')
     and class = 1

    This should provide the permissions granted on the database. If there are permissions that are denied on the table, check what is the principlal to whom the permission is denied (the grantee). If it is a role or group, make sure that the IUSR_NKNLEPETD0 user is not a member of it. Denies take precedence over grants.

    The user only needs SELECT permission but it looks like either he has not granted permission or he has been denied permission (maybe not explicitly, but the permission might have been denied to a role in which the user belongs).

    Thanks
    Laurentiu

    Tuesday, February 07, 2006 9:27 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    That's rather extreme for a problem that could have been fixed by just dropping and recreating the database. I'm also surprised it took you only 25 minutes to do all those steps.

    If you hit such problem in the future and want to continue the investigation, let us know.

    Thanks
    Laurentiu

    Thursday, February 09, 2006 6:52 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

      I would recommend running SQL Server Profiler and run the infopath form with the non-admin credentials again.

    ·         Verify that the user identity is what you expected (i.e. make sure the login and user in the DB is what you expected).

    ·         Check for any permission or other security check failure and on what object

     

      From there you can check in SQL Server if the permissions for this principal are correct, it may be possible that there is a secondary identity under that context (i.e. a group membership) that is being evaluated as an explicit denied permission to access the resources in the database.

     

      Let us know if you have any questions, we will be glad ot help.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, June 06, 2007 5:56 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

      From your description it seems like you were relying on ownership chains for the select statements, but ownership chains (thankfully) are not honored when using dynamic SQL, as is the case with sp_executesql.

     

     BTW, Lurentiu wrote a very interesting article on this topic: http://blogs.msdn.com/lcris/archive/2007/09/13/basic-sql-server-security-concepts-ownership-chaining-good-and-evil-schemas.aspx

     

      Thanks,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, February 12, 2008 12:35 AM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Okay.  I read some OC stuff.
    Here is what I am doing to have both execute permission for stored procedure  SP1 for User 1 and use the dynamic sql.

    Create another user -  User 2.

    Then create stored procedure SP1
    with execute as 'User 2'.

     

    User 2 has the permissions to select etc on the tables.

    So sp_executesql session is also using User 2.

     

    The trick is that you don't have to give out the login for User 2.   Secured.

     

    Any problems with this??

    Thanks.

    Thursday, February 14, 2008 1:47 AM
    |

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Can you execute the following query in the YouPlayIt database:

    select object_name(major_id) as object,
     user_name(grantee_principal_id) as grantee,
     user_name(grantor_principal_id) as grantor,
     permission_name,
     state_desc
    from sys.database_permissions
     where major_id = object_id('Users')
     and class = 1

    This should provide the permissions granted on the database. If there are permissions that are denied on the table, check what is the principlal to whom the permission is denied (the grantee). If it is a role or group, make sure that the IUSR_NKNLEPETD0 user is not a member of it. Denies take precedence over grants.

    The user only needs SELECT permission but it looks like either he has not granted permission or he has been denied permission (maybe not explicitly, but the permission might have been denied to a role in which the user belongs).

    Thanks
    Laurentiu

    Tuesday, February 07, 2006 9:27 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    the query returns me this lines:

    Users    NKNLEPETD0\IUSR_NKNLEPETD0    dbo    ALTER    GRANT
    Users    NKNLEPETD0\IUSR_NKNLEPETD0    dbo    CONTROL    GRANT
    Users    NKNLEPETD0\IUSR_NKNLEPETD0    dbo    DELETE    GRANT
    Users    NKNLEPETD0\IUSR_NKNLEPETD0    dbo    INSERT    GRANT
    Users    NKNLEPETD0\IUSR_NKNLEPETD0    dbo    REFERENCES    GRANT
    Users    NKNLEPETD0\IUSR_NKNLEPETD0    dbo    SELECT    GRANT
    Users    NKNLEPETD0\IUSR_NKNLEPETD0    dbo    TAKE OWNERSHIP    GRANT
    Users    NKNLEPETD0\IUSR_NKNLEPETD0    dbo    UPDATE    GRANT
    Users    NKNLEPETD0\IUSR_NKNLEPETD0    dbo    VIEW DEFINITION    GRANT

    As you can see all the permissions are set to the IUSR user but, still I get the same error message: SELECT permission denied on object 'Users', database 'YouPlayIt', schema 'dbo'.

    I'm continuing to add permissions to everyone but I continue to get this error... maybe is something related with IIS ??

    Nick.


    Thursday, February 09, 2006 10:30 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Ok, I solved the problems using the SMP approach "Standard Microsoft Procedure":
    1. Completely clear the server with FDISK
    2. Reinstall Windows
    3. Reinstall everything else
    4. Add your data, configure the SQL server.

    Now everything is fine. I gave up tring to find the real issue, better the Brute Force attack... as usual.

    Have fun,
    Nick.
    Thursday, February 09, 2006 10:55 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    That's rather extreme for a problem that could have been fixed by just dropping and recreating the database. I'm also surprised it took you only 25 minutes to do all those steps.

    If you hit such problem in the future and want to continue the investigation, let us know.

    Thanks
    Laurentiu

    Thursday, February 09, 2006 6:52 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    I agree. That seems a little over the top to fix a small problem. But I guess if you have to ability to completely reinstall everything then go for it. I on the other hand am having the same "select permission is denied" error and cannot reinstall everything.

    I have an infopath form that submits data to the sql database on the sharepoint box (single server setup) and the data submits fine when I am logged onto a computer with a username that is an adminstrator on the sharepoint box. But when I am logged onto the computer with any other username I get the error. I gave the select permission to all authenticated users for the whole "form data" database and that didn't change anything. Does anyone have any ideas on what to do and how to get around this?

    Wednesday, June 06, 2007 4:53 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

      I would recommend running SQL Server Profiler and run the infopath form with the non-admin credentials again.

    ·         Verify that the user identity is what you expected (i.e. make sure the login and user in the DB is what you expected).

    ·         Check for any permission or other security check failure and on what object

     

      From there you can check in SQL Server if the permissions for this principal are correct, it may be possible that there is a secondary identity under that context (i.e. a group membership) that is being evaluated as an explicit denied permission to access the resources in the database.

     

      Let us know if you have any questions, we will be glad ot help.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, June 06, 2007 5:56 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for you quick response. After doing what you said I figured out I made a pretty simple mistake. Because I was submitting data to the sharepoint box I should have been submitting it to the database called sqlexpress instead of the sqlofficeserver database. I am using the sqlexpress database now and it works perfectly. I guess the officerserver database is just for sharepoint stuff.
    Wednesday, June 06, 2007 7:27 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote
    Hi,

    I fixed the error by assigning the default schema for teh database to DBO.
    eg: Login to SQL using SA > Security > User > select username > properties
    Make sure that 'Users = username' & 'Schema = DBO'

    Let me know if you have any queries. Smile

    --Viraj
    Sunday, July 29, 2007 11:45 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    You can check the security login, check if the user has appropriate rights, such as db_datareader rights...
    Thursday, August 23, 2007 8:49 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Let me explain my case and how I solved,

     

    I have a user that only has executing permission on stored procedures, but one of those stored procedures executes a dynamic SQL Statement using sp_executesql. Then when i tried to execute that particular stored procedure i always get that message.

     

    What I did is to give select permissions on tables involved in those particular stored procedures and works. Take care because this procedure could open a backdoor to expose your database to sql injection.

     

    I think that my user has not enough permission to execute a dynamic sql statement. Who knows?

     

    Experts suggest do not use dynamic sql statements but sometimes is needed.

     

    Happy coding!!

    Thursday, January 31, 2008 10:49 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    I have the same issue.   User has only execute permission on stored procedures.

    One of the stored procedures uses  sp_executesql. 

    It appears that sp_executesql creates a session with the same rights as user.

    Then when it executes the sp_executesql  it does not have Select permission on any of the tables.

    I had to add select permission to the tables.

     

    I will have issues with this later on in production.

    Suppose it is update, delete, insert permissions that are needed,  basically you have opened it up for

    sql injection anyway.  what???  what's up with that??

     

    So how do you allow the user to have only execute permissions

    and have the developer be able to use sp_executesql??

     

    Thanks.

     

     

     

     

     

    Tuesday, February 12, 2008 12:08 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

      From your description it seems like you were relying on ownership chains for the select statements, but ownership chains (thankfully) are not honored when using dynamic SQL, as is the case with sp_executesql.

     

     BTW, Lurentiu wrote a very interesting article on this topic: http://blogs.msdn.com/lcris/archive/2007/09/13/basic-sql-server-security-concepts-ownership-chaining-good-and-evil-schemas.aspx

     

      Thanks,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, February 12, 2008 12:35 AM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

              Let me explain the situation a little more.  I may be missing something about sp_executesql.

     

              User A is allowed execute permission on Stored Procedure S.

              If I simply put a statement in Stored Procedure S like "Select * from dbo.Table T",   it runs fine and

              User A is able to see Table T values  even though User A has no select permission on Table T.

     

              However, when instead of a simple Select,  I use  sp_executesql with the same "Select * from dbo.Table T";

              Now it asks for select permission on Table T.

     

              Is that the expected behavior from OC? 

              In other words,  in the first case,  there is OC.

              But when using sp_executesql  there is no OC.

              Is that it?

     

              By the way, do you any articles showing a design to where the user is only allowed to execute

              stored procedures and be able to use dynamic sql and be secured against sql injection?

              Or is there such a thing?

     

              Thanks.

     

    Alex

     

     

    Tuesday, February 12, 2008 8:14 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Okay.  I read some OC stuff.
    Here is what I am doing to have both execute permission for stored procedure  SP1 for User 1 and use the dynamic sql.

    Create another user -  User 2.

    Then create stored procedure SP1
    with execute as 'User 2'.

     

    User 2 has the permissions to select etc on the tables.

    So sp_executesql session is also using User 2.

     

    The trick is that you don't have to give out the login for User 2.   Secured.

     

    Any problems with this??

    Thanks.

    Thursday, February 14, 2008 1:47 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

      It sounds like a good solution,  I just have a few recommendations:

    ·         If you are using this user only for the module EXECUTE AS context, I would recommend using a user without login, that way you don’t have to worry about the login creation.

    ·         Follow the least privilege principle and make sure the EXECUTE AS user has been granted the minimum permissions needed (in this case select on that particular table)

    ·         If using dynamic SQL, verify the procedure is not subject to SQL injections.

    ·         If you use any kind of custom auditing mechanism (such as trigger based audits, etc.) consider using signatures. Laurentiu wrote a great article on this topic.

     

      I hope this information helps.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Thursday, February 14, 2008 7:18 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    please, try the following:

    1- Add asp.net as data base user

    2- use db_owner as default schema for this user.

    3- select db_owner in Owned Schemas section.

    4- select db_owner in Roles Schemas section.

     

    i think it will work.

    Best regards

    Monday, June 09, 2008 11:08 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Laurentiu Cristofor said:

    Can you execute the following query in the YouPlayIt database:

    select object_name(major_id) as object,
     user_name(grantee_principal_id) as grantee,
     user_name(grantor_principal_id) as grantor,
     permission_name,
     state_desc
    from sys.database_permissions
     where major_id = object_id('Users')
     and class = 1

    This should provide the permissions granted on the database. If there are permissions that are denied on the table, check what is the principlal to whom the permission is denied (the grantee). If it is a role or group, make sure that the IUSR_NKNLEPETD0 user is not a member of it. Denies take precedence over grants.

    The user only needs SELECT permission but it looks like either he has not granted permission or he has been denied permission (maybe not explicitly, but the permission might have been denied to a role in which the user belongs).

    Thanks
    Laurentiu



    Hello,

    This query does not return any row...?

    Any idea?

    Thanks,
    Dom

    Altiris Support / System Center Configuration Manager Support
    Friday, January 23, 2009 7:03 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    It worked with the following script:

    select object_name(major_id) as object,
     user_name(grantee_principal_id) as grantee,
     user_name(grantor_principal_id) as grantor,
     permission_name,
     state_desc, class
    from sys.database_permissions
     where user_name(grantee_principal_id) = 'UserName' AND class=1

    :-)

    Tuesday, June 25, 2013 2:08 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear Laurentiu,

    I am facing a similar problem.

    My system has a MS Access FE with a SQL Server BE.

    The user have the INSERT permission and as I log on with the user, by means of the SQL Server Management Studio Express, an insertion query works fine.

    The problem is when a connection is made through MS Access. An error message like "The INSERT permission was denied on the object ... database ... schema 'dbo'".

    I'd appreciate any help,

    Celio.

    Tuesday, June 25, 2013 2:20 PM
    |