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

I forgot to mention that you change change schema ownership as follows:

ALTER AUTHORIZATION ON SCHEMA::CRM to dbo;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:OfQE2C4uJHA.1088@xxxxxxxxxxxxxxxxxxxxxxx
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.

It depends on the purpose of the schema. If you are using schema only as a namespace (logical grouping), then you can change the owner of the CRM schema to 'dbo' so that the ownership chain is unbroken. This will allow CRM schema procedures to call dbo schema stored procedures without special permissions.

If you also want a separate security boundary for the schemas, then you need different schema owners and the additional security administration that goes along with that.

3. What about CREATE PROCEDURE WITH EXECUTE AS OWNER? Is that totally wrong?

EXECUTE AS OWNER will elevate the executing user's permissions. I think it would be better to either leverage ownership chaining, create a minimally privileged user (e.g. CRMProcExecutor) for EXECUTE AS or use certificates. See Erland's article (http://www.sommarskog.se/grantperm.html) for more information and examples of these techniques.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Omid Golban" <OmidGolban@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:D249B38E-9F36-4842-AC43-1ECED37D2783@xxxxxxxxxxxxxxxx
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: 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: Database connection issue using SQL schema user account
    ... They can simplify permissions insofar as being able to have ... new objects created inside of a schema inherit permissions assigned to ... --The dbo user account is not the same thing as the dbo default ... The dbo user maps to db_owner/sysadmin. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Alter User for domain user
    ... connect to the server with Windows Authentication, the default schema ... (I used to have the dbo role, ... role on the server. ... schema different from dbo for a Windows login. ...
    (microsoft.public.sqlserver.security)
  • Re: object cant be accessed unless owner is specified - SQL 2000
    ... To access objects in Sune schema, ... Sune was not mapped to dbo - nothing at all was, not even sa (odd, I know, ... Erland - is now the database owner on the SQL 2000 taskpad. ...
    (microsoft.public.sqlserver.security)
Quantcast