SELECT permission denied on object

SELECT permission denied on object

• michivo

  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

  
  
  

• Mark64

  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

  
  
  

• Martin Vobr

  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.
  
  
  
  
  

• ershad

  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

  
  

• Lakshmana Kumar K

  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

  
  
  

• Chris Wr

  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.
  
  --Viraj
  
  

• Per Bröms

  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.
  

  
  

• Xinirnix

  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.
  

• Iktikhan

  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

  
  
  

• Xero2007

  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.

  
  

• Anonymice

  You can check the security login, check if the user has appropriate rights, such as db_datareader rights...
  
  

• jjohn

  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!!

  
  
  

• m_3ryan

  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

  
  

• weehyong

  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.
  
  
  
• SELECT permission denied on object