MS SQL Server - User access right:

Author

User access right:

JIM.H.

2006-12-04, 7:12 pm

Is there any way I can enable a user to run a stored procedure but nothing
else in SQL2000, not event select/insert/update/delete on any table.

Tibor Karaszi

2006-12-04, 7:12 pm

Yes, grant EXECute permission on the proc, but do not grant permissions on the tables. But the proc
need to meet certain requirements (like no dynamic SQL, same object owner for proc as for tables).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"JIM.H." <JIMH@discussions.microsoft.com> wrote in message
news:CAD2C517-B001-4302-A73E- 7A3D0ACC7C0E@microso
ft.com...
>
> Is there any way I can enable a user to run a stored procedure but nothing
> else in SQL2000, not event select/insert/update/delete on any table.
>

JIM.H.

2006-12-04, 7:12 pm

Thanks for the reply. Procedure uses to select/insert/update on a table, will
this be working if I do not give access to that table?

"Tibor Karaszi" wrote:

> Yes, grant EXECute permission on the proc, but do not grant permissions on the tables. But the proc
> need to meet certain requirements (like no dynamic SQL, same object owner for proc as for tables).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "JIM.H." <JIMH@discussions.microsoft.com> wrote in message
> news:CAD2C517-B001-4302-A73E- 7A3D0ACC7C0E@microso
ft.com...
>
>

Joel Maslak

2006-12-04, 7:12 pm

>>> On 12/4/2006 at 10:20 AM, in message
<OuEC0h8FHHA.1276@TK2MSFTNGP04.phx.gbl>, Tibor
Karaszi<tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote:
> Yes, grant EXECute permission on the proc, but do not grant
> permissions on the tables. But the proc
> need to meet certain requirements (like no dynamic SQL, same object
> owner for proc as for tables).

True, although the EXECUTE AS construct in 2005 lets you get around some
of this too.

Tibor Karaszi

2006-12-04, 7:12 pm

> True, although the EXECUTE AS construct in 2005 lets you get around some
> of this too.

Absolutely, however, the OP specifically mentioned 2000. Also, one should be careful using EXECUTE
AS in 2005: http://www.sommarskog.se/grantperm.html

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Joel Maslak" <jmasla@state.wy.us> wrote in message news:4573FB5A.BB84.007F.0@state.wy.us...
> <OuEC0h8FHHA.1276@TK2MSFTNGP04.phx.gbl>, Tibor
> Karaszi<tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote:
>
> True, although the EXECUTE AS construct in 2005 lets you get around some
> of this too.

Tibor Karaszi

2006-12-04, 7:12 pm

> Procedure uses to select/insert/update on a table, will
> this be working if I do not give access to that table?

Yes, assuming you don't do dynamic SQL (execute a string). This is very easy to test, so I suggest
you give it a shot...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"JIM.H." <JIMH@discussions.microsoft.com> wrote in message
news:38D2263B-E482-4E56-BFCF- D8F54E25A922@microso
ft.com...[color=darkred]
> Thanks for the reply. Procedure uses to select/insert/update on a table, will
> this be working if I do not give access to that table?
>
> "Tibor Karaszi" wrote:
>

JIM.H.

2006-12-04, 7:12 pm

Thanks for the response. It seems this user that has only exec right is able
to view all other SP codes, is there any way I can prevent that?

"Tibor Karaszi" wrote:

>
> Yes, assuming you don't do dynamic SQL (execute a string). This is very easy to test, so I suggest
> you give it a shot...
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "JIM.H." <JIMH@discussions.microsoft.com> wrote in message
> news:38D2263B-E482-4E56-BFCF- D8F54E25A922@microso
ft.com...
>
>

Tibor Karaszi

2006-12-05, 5:16 am

> It seems this user that has only exec right is able
> to view all other SP codes, is there any way I can prevent that?

Can you explain what you mean by "view other SP codes"? Do you mean view the source code of the
stored procedure? Or discover the names of the other stored procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"JIM.H." <JIMH@discussions.microsoft.com> wrote in message
news:A934C8C1-71DC-437C-BA70- E75015B6EFDB@microso
ft.com...[color=darkred]
> Thanks for the response. It seems this user that has only exec right is able
> to view all other SP codes, is there any way I can prevent that?
>
> "Tibor Karaszi" wrote:
>

JIM.H.

2006-12-05, 7:12 pm

It seems this user can login through Query Analyzer and see the code of all
the SPs. Also the name of all the objects.

"Tibor Karaszi" wrote:

>
> Can you explain what you mean by "view other SP codes"? Do you mean view the source code of the
> stored procedure? Or discover the names of the other stored procedures.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "JIM.H." <JIMH@discussions.microsoft.com> wrote in message
> news:A934C8C1-71DC-437C-BA70- E75015B6EFDB@microso
ft.com...
>
>
>

Tibor Karaszi

2006-12-05, 7:12 pm

You cannot hide existence of objects in SQL Server 2000. You can scramble the source code by
creating the procedure using the WITH ENCRYPTION clause (although a quick search on the Internet
will reveal several ways of decrypt this).

In 2005, you only see existence of objects that you have access to.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"JIM.H." <JIMH@discussions.microsoft.com> wrote in message
news:F1F14751-E533-4440-B69B- 8574686E23C8@microso
ft.com...[color=darkred]
> It seems this user can login through Query Analyzer and see the code of all
> the SPs. Also the name of all the objects.
>
> "Tibor Karaszi" wrote:
>

JIM.H.

2006-12-05, 7:12 pm

Thanks Tibor for your reply, there are many stored procedures, is there any
way I can restrict to login to database for this user. This user has access
to DB1 and a trigger is using the same user to run a SP in DB2. So is there
any way I can restrict the user on DB2 to login to DB2 but let the trigger
still run this SP in DB2.

"Tibor Karaszi" wrote:

> You cannot hide existence of objects in SQL Server 2000. You can scramble the source code by
> creating the procedure using the WITH ENCRYPTION clause (although a quick search on the Internet
> will reveal several ways of decrypt this).
>
> In 2005, you only see existence of objects that you have access to.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "JIM.H." <JIMH@discussions.microsoft.com> wrote in message
> news:F1F14751-E533-4440-B69B- 8574686E23C8@microso
ft.com...
>
>
>

Tibor Karaszi

2006-12-05, 7:12 pm

Read about the Cross DB Ownership Chaining option.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"JIM.H." <JIMH@discussions.microsoft.com> wrote in message
news:7EE5DF82-A8CC-4AED-B4BF- 864DED44B82B@microso
ft.com...[color=darkred]
> Thanks Tibor for your reply, there are many stored procedures, is there any
> way I can restrict to login to database for this user. This user has access
> to DB1 and a trigger is using the same user to run a SP in DB2. So is there
> any way I can restrict the user on DB2 to login to DB2 but let the trigger
> still run this SP in DB2.
>
> "Tibor Karaszi" wrote:
>

Sponsored Links