locked
Creating Separate database to hold objects for multiple databases? RRS feed

  • Question

  • I am designing a database and struck at following point:

    I want to create a database say db1 that holds the USPs , UFns.

    Other databases say db2 and db3 has their own set of tables with similar structure.

    Now I want to call the db1 sps from db2 or db3.

    And these sps should perorfrom ddl/dml operation on the tables of the calling Dbs.

    Let say if I call db1.usp_xyz1 from db2 then the tables of db2 should be operated by the Sp.

    And Let say if I call db1.usp_xyz1 from db3 then the tables of db3 should be operated by the Sp.

    Any help will be highly appreciated

     


    ~Technology Analyst~
    Tuesday, January 4, 2011 8:46 AM

Answers

  • Hi psingla,

    If I understand you correctly, you want to create a single procedure
    in a single database, that can be called from any database, and that
    will operate on objects in the database where it is called from, not
    on objects in the database is is created in. Correct?

    Up until SQL Server 2005, this was possible by creating a stored
    procedure in the master database and marking it as a system object.
    You did that by running the undocumented system stored procedure
    master.dbo.sp_MS_upd_sysobj_category (for SQL Server 2000) or
    sys.sp_MS_marksystemobject (SQL Server 2005). For more information and
    an example, see
    http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx
    But please do note that there are lots of reasons why you should
    better not do this!

    In SQL Server 2008, the stored procedures mentioned above no longer
    exist. I have no idea if there is not another way to mark a user-built
    stored procedure as a system object, and my search on internet did not
    help either.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Wednesday, January 5, 2011 10:54 PM
  • Hi Hugo

    >>Up until SQL Server 2005, this was possible by creating a stored
    >>procedure in the master database and marking it as a system object.

    If we run that 'global' stored procedure from master  we need at least an EXECUTE permission and more over SELECT permission on that table 't'

    create proc sp_test as select * from t
    GO
    use AdventureWorks
    create table t(c1 varchar(50))
     insert t values('adventureworks')
    use pubs
    create table t(c1 varchar(50)) insert t values('pubs')
    use pubs
    exec sp_test --returns 'master'
    use master
    exec sp_MS_marksystemobject sp_test
    use pubs
    exec sp_test --returns 'pubs'
    use northwind
    exec sp_test --returns 'northwind'

    Msg 229, Level 14, State 5, Procedure sp_test, Line 1

    The EXECUTE permission was denied on the object 'sp_test', database 'master', schema 'dbo'.

    use master

    GRANT EXEC ON sp_test TO test

    Msg 229, Level 14, State 5, Procedure sp_test, Line 1

    The SELECT permission was denied on the object 't', database 'AdventureWorks', schema 'dbo'.

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 6, 2011 6:13 AM

All replies

  • Hi

    Let say you have a stored procedure that does below

    CREATE PROCEDURE sp1
    AS
    CREATE TABLE t20(c INT)
    INSERT INTO t20 VALUES (1)
    ALTER TABLE t20 ADD col1 CHAR(1)

    Now  you have user 'test' in both databases

    db2 user has to be granted to db1  and has an EXECUTE PERMISSIONS

    GRANT EXEC ON sp1 TO test -- Grant permission on a single procedure.

    But  you will get an error that you do not have CREATE TABLE permission

    GRANT CREATE TABLE TO test

    Ok, now you do not have permissions on dbo schema

    GRANT ALTER ON SCHEMA::dbo TO test

    Ok, now it should work EXEC sp1

     

     


    GRANT EXEC ON SCHEMA::dbo TO kalle -- Grant perpmission on all proce

     

    dures in
                                          the dbo schema
    GRANT EXEC TO putte -- Grant EXEC permission all procedures in the database.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, January 4, 2011 9:08 AM
  • thanks Uri,

    But In this case db2 user will end up in creating  table on db1 database.

    but i want to the table created on db2 using proc sp1.

     


    ~Technology Analyst~
    Tuesday, January 4, 2011 10:38 AM
  • So then what  problem?... User_db2 executes sp1 on db2 to create tables?

    >>>Let say if I call db1.usp_xyz1 from db2 then the tables of db2 should be >>>operated by the Sp.

    What do you mean by "operated by the SP" Can you show sample SP?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, January 4, 2011 12:55 PM
  • Hi psingla,

    If I understand you correctly, you want to create a single procedure
    in a single database, that can be called from any database, and that
    will operate on objects in the database where it is called from, not
    on objects in the database is is created in. Correct?

    Up until SQL Server 2005, this was possible by creating a stored
    procedure in the master database and marking it as a system object.
    You did that by running the undocumented system stored procedure
    master.dbo.sp_MS_upd_sysobj_category (for SQL Server 2000) or
    sys.sp_MS_marksystemobject (SQL Server 2005). For more information and
    an example, see
    http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx
    But please do note that there are lots of reasons why you should
    better not do this!

    In SQL Server 2008, the stored procedures mentioned above no longer
    exist. I have no idea if there is not another way to mark a user-built
    stored procedure as a system object, and my search on internet did not
    help either.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Wednesday, January 5, 2011 10:54 PM
  • Hi Hugo

    >>Up until SQL Server 2005, this was possible by creating a stored
    >>procedure in the master database and marking it as a system object.

    If we run that 'global' stored procedure from master  we need at least an EXECUTE permission and more over SELECT permission on that table 't'

    create proc sp_test as select * from t
    GO
    use AdventureWorks
    create table t(c1 varchar(50))
     insert t values('adventureworks')
    use pubs
    create table t(c1 varchar(50)) insert t values('pubs')
    use pubs
    exec sp_test --returns 'master'
    use master
    exec sp_MS_marksystemobject sp_test
    use pubs
    exec sp_test --returns 'pubs'
    use northwind
    exec sp_test --returns 'northwind'

    Msg 229, Level 14, State 5, Procedure sp_test, Line 1

    The EXECUTE permission was denied on the object 'sp_test', database 'master', schema 'dbo'.

    use master

    GRANT EXEC ON sp_test TO test

    Msg 229, Level 14, State 5, Procedure sp_test, Line 1

    The SELECT permission was denied on the object 't', database 'AdventureWorks', schema 'dbo'.

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 6, 2011 6:13 AM