change permiision of a STORED PROCEDURE to all use

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

change permiision of a STORED PROCEDURE to all use

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-29 : 06:50:24

i want to prevent a stroced procudure access to all users and ive it a DENY-how can i do this whithought the need to type in the sql syntax al lthe users name?

DENY EXECUTE ON SP_xxx TO ...???

thnaksi n advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-29 : 07:15:29

One way to overcome this is to group your users in role and then DENY permission to the role, in effect denying permission to all the users under that role.

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-11-29 : 09:15:03

By default no one is allowed to execute the SProc (are they?), so by just Granting Execute Permission to people who SHOULD have it solves the problem, doesn't it?

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-02 : 15:25:36

kristen if the sql user ids defined a db_owner he can run a SP withought any problem:)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2006-12-03 : 02:35:41

Sure, but I wouldn't use db_owner for application users - any more than I would SA!

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-04 : 00:06:02

you could try...

deny execute on sp_name to public

--------------------
keeping it simple...

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-04 : 02:47:35

if i make deny on public
but for one of the users i give permit will it block him anyway?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-04 : 03:00:04

and 1 more questio whre i do
select name from master.dbo.sysobjects i dont see all the SP
that are in the master Extended Storeed Procedures why is that?
hiow can i see them in a select too??
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-04 : 03:10:09

a deny supercedes all... except for SA direct queries and execution

i can see the extended SPs when I run the same tsql you did...

--------------------
keeping it simple...

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-04 : 04:38:00

i add checki n the select:
select name from master.dbo.sysobjects WHERE TYpe='X' OR type='P'
P=SP
X=EXTENDED SP

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources