Prev: MS SQL table update using view
Next: Optional IN Clause parameter
From: Roy Goldhammer on 18 Jul 2010 06:15
Hello there

I have one database with 3 schemas: Public, Program, Product.

there are 2 users: User1 - default schema Program, User2 - default schema
Product

None of these users are sysadmin or dbowner. they only grant to run
procedures.

Each of the procedures relate to schema are doing select from schema Public.
This option is not possible if i not grant the select on schema Main. (which
i don't want to do).

Is there a way to allow user1 and user2 to run select on each of their
schemas and do select from schema Public without grant select?



__________ Information from ESET NOD32 Antivirus, version of virus signature database 5287 (20100717) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




From: Erland Sommarskog on 18 Jul 2010 08:41
Roy Goldhammer (royg(a)hotmail.com) writes:
> I have one database with 3 schemas: Public, Program, Product.
>
> there are 2 users: User1 - default schema Program, User2 - default schema
> Product
>
> None of these users are sysadmin or dbowner. they only grant to run
> procedures.
>
> Each of the procedures relate to schema are doing select from schema
> Public. This option is not possible if i not grant the select on schema
> Main. (which i don't want to do).
>
> Is there a way to allow user1 and user2 to run select on each of their
> schemas and do select from schema Public without grant select?

As long as the schemas all have the same owner, all you need is to grant
Execute on the stored procedure. Or more precisely, the object owner should
be one and the same for all procedures and tables.

If this is not feasible, you will need to work with certificate signing.
I have an article on my web site that discusses this topic in detail:
http://www.sommarskog.se/grantperm.html.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: MS SQL table update using view
Next: Optional IN Clause parameter