Re: cross chema calls fail: schema owner doesn't have execute priv

1. Doesn't changing the owner to dbo defeat the purpose of having the schema?
2. The brass wants explicit separation of these procedures - a logical
grouping where all procedures for an external entity will be together inside
their own schema.
3. What about CREATE PROCEDURE WITH EXECUTE AS OWNER? Is that totally wrong?

Thank you,
Omid

"Jeffrey Williams" wrote:

Try changing the ownership on the schema to dbo.

While you are at it, if your procedures are nothing more than a shell to
call a procedure in the 'dbo' schema then I would consider using synonyms
instead. For example:

CREATE SYNONYM crm.MyProcedure FOR dbo.MyProcedure;
GO

EXECUTE crm.MyProcedure @param1, @param2, ...;

Jeff

"Omid Golban" <OmidGolban@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:39ACE9A1-DA87-42D4-9899-F05473ED692C@xxxxxxxxxxxxxxxx
Hi folks,

I am using SQL 2005 and have created a schema:
CREATE SCHEMA [CRM];

Then I created a bunch of stored procs under this schema. These CRM procs
call other [dbo] procs:
CREATE PROCEDURE CRM.Contact_Update
AS
... EXEC dbo.SomeProcedure ...
END

We are logically grouping all CRM procs under this schema. I don't know
the
commands used, but DBA Created:
1. Role ScribeAccess
owner: dbo
owned schemas: CRM
role members: scribe
2. user scribe
default schema: CRM
role members: db_owner, ScribeAccess

DBA had to grant db_owner access to the DatabaseRole "ScribeAccess"
because
CRM user was getting following error when calling a dbo procedure:
[42000] SQL call failed. Uncommittable transaction is detected at the
end
of the batch. The transaction is rolled back.

42000 may be the error that SCRIBE throws, but they have verified problem
was that user CRM does not have permission to execute dbo procs.

Currently I have PROCs under this schema. I don't plan on having anything
else.

how do I provide for cross schema calls?
I am currently changing the procs to "EXECUTE AS OWNER" but I am probably
breaking security rules.
I also do not want to go through each procedure and explicitly grant
execute
access to each schema because in next phase of the project we're going to
have 15 such schemas.

Thank you,
Omid

.

Relevant Pages

  • Re: Stored Procedure Disappearing
    ... Did you use dbo. ... Qualifier to dbo or if you have used something else for the schema (owner) ... summary report which obtains data from a large stored procedure containing ...
    (microsoft.public.access.reports)
  • Re: Access 2007, SQL 2000
    ... Since users are not dbo, and SQL 2000 used the user name as the schema identifier, you have to make sure that every reference to a SQL Server object is fully qualified with the dbo. ... Normally a user should not be able to create any new objects in the db, but if they do, those new objects will be in the userName schema, not the dbo schema, assuming the user is not a db owner. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: exporting into a SQL Server schema
    ... inside of the schema. ... The thing that's most confusing is that the dbo schema isn't ... take a look at the way the objects in the AdventureWorks database are ... It turns out that I'm moving some tables to a web hosted solution with sql ...
    (microsoft.public.access.externaldata)
  • Re: cross chema calls fail: schema owner doesnt have execute privileg
    ... if your procedures are nothing more than a shell to call a procedure in the 'dbo' schema then I would consider using synonyms instead. ... Then I created a bunch of stored procs under this schema. ... role members: db_owner, ScribeAccess ...
    (microsoft.public.sqlserver.security)
  • Re: SP
    ... long as the owner (schema) is different. ... procs, each with the same owner and name but different numbers. ... "Ramesh" wrote in message ...
    (microsoft.public.sqlserver.programming)