I have a bunch of DDL's that are to be run on a db.All the script is doing is dropping a bunch of tables, constraints and then creation of some tables. The problem is when i run the script all together as a single script, i get the error
"Cannot find the object " dbo.example" because it does not exist or you do not have permissions"
I see a bunch of these errors when i run it altogether.. to trouble shoot.. ran each single script to drop or create the constraint, table separately, it works fine. Even the execution order also does not change.. but still.. I dont understand .. why does this happen?
Answer by David Wimbush ·
Clearly, for some reason, either you haven't created
example at all or it isn't in the schema
dbo. Either you gave it a different schema or you didn't specify a schema and it defaulted to something other than dbo. If you create an object without specifying the schema it gets added to the schema associated with the current user.
Run the create part and check what schema the object is in.
Maybe you used credentials that have DDL permissions but isn't a member of the server role sysadmin or of the database role db_owner. Try executing
select current_user. If it doesn't return dbo then this could be the problem.