Can't insert rows in tables from Management Studio

Question

I am trying to run some insert/update queries on the tables in a DB from management studio for testing. I am loged in using windows authentication. I do have all the access and I am also in the users group. The checkbox for Insert and Update are checked under the Grant column in permissions. I am able to create new tables too. But when I try to insert rows, it gives me following error:

"The INSERT permission was denied on the object 'TableName', database 'DBName', schema 'dbo'."

I can insert data if I use a stored procedure.

Anybody knows what might be causing this?

I can run the insert/update queries when I loged in as admin but for that I have to login on the server (not able to login as admin from management studio on my machine).   Also I can run insert queries on other databases on the same SQL server. Only this one database is not working.

Thank you!

Answer 1

You have simply not the necessary permissions to do this.

You can do it by using the stored procedure when it was created with execute as owner.

Seems that you need to take a closer look at your permissions.

Answer 2

From the description it sound like you have ALTER permission on the dbo schema and also CREATE TABLE.

But there is the catch: when you create an object in a schema, the owner of the object is not you, the creator, but it is always the schema owner. This makes it perfectly possibly to create an object you don't have acces to yourself.

When you execute the stored procedure it is different. There are no permission checks in a stored procedure against objects that have the same owner as the procedure.

To make it more confusing, there may also be a DENY in the mix somewhere. Here is how you can script some of your permissions:
http://sqlity.net/en/2584/script-database-permissions/
(The query does not consider role membership in for instances db_denydatawriter, nor permissions you get through Windows groups.)