R2 Security - SQL Server Forums

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 2008 Forums

SQL Server Administration (2008)

R2 Security

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-27 : 18:14:21

OK..lazy and offline

Did they EVER set it up that an ID can be granted EXEC on a sproc without needing GRANTS to ISUD to the tables?

Thank you...thank you very much....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-27 : 19:37:48

Ummm, that wasn't required in 2005 or 2000. It was required if you were using dynamic SQL since those ran outside of the context of the stored procedure. But if you don't have any dynamic SQL and everything is through stored procs, then exec on stored procs is all the userids (or better yet roles!) need.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-27 : 21:12:59

OK...so you are saying

Windows login X002548

Can have EXEC to a sproc that updates tableX

And Doesn't need modify to tableX

I am a dinosaur

I'll 2x check

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-27 : 21:15:34

my mind...has been occupied .....the last 4 years...

Sorry ladies and gentlemen

Thanks for everything...

:-(

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-27 : 21:17:41

And yes that's exactly what I'm saying.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-27 : 21:26:23

2k? you sure?

I'm gonna head down to the old man sql tavern...maybe merkin will be there with pix from bondi

see we don't look good in skirts...well..maybe Rob does

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-27 : 21:46:58

Yes I'm positive. And it was like that even before 2000.

Rob looks great in a skirt! So does Bill!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-01-27 : 23:10:42

If you are using SQL 2005 or above, the user doesn't need access to the underlying tables even if the stored procedure uses dynamic SQL if you add an EXECUTE AS clause to the stored procedure and the tables are in the same database.

CREATE PROCEDURE dbo.MyProcedure
WITH EXECUTE AS 'dbo'
AS
... rest of procedure...

You can even access tables in another database with just the access of the stored procedure, but it is a bit more complex to setup. The link below explains how that can be done using a stored procedure signed with a certificate.

SQL Server Books Online
Tutorial: Signing Stored Procedures with a Certificate
http://msdn.microsoft.com/en-us/library/bb283630.aspx

CODO ERGO SUM

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-28 : 05:05:51

Yes, 2000 and probably SQL 7 too.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources