Answered by:
SELECT permission denied on object

-
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
Question
Answers
-
-
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 = 1This 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 -
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 -
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
-
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
-
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.
All replies
-
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 = 1This 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 -
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. -
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. -
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 -
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?
-
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
-
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.
-
-
-
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!!
-
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.
-
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
-
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
-
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.
-
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
-
-
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 = 1This 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 -
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:-)
-
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.