Procedures security issue - 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 2005 Forums

Transact-SQL (2005)

Procedures security issue

Author

Topic

zymantas
Starting Member

3 Posts

Posted - 2010-06-02 : 17:13:45

Hello,

I have a lot of procedures, in witch is used a "sp_executesql" procedure, or insert, update...
For example: I have procedure "spProc" witch forms dynamic sql wich updates table "tA" and then executes "sp_executesql"
The problem is user of that database, can't do anything except what i define. So if i grant execute on spProc to student (student is a role)
i can run "spProc" but it can't update table "tA". What is wrong?
How do i grant permision to do what is nessary in that procedure, but do not let to students view, insert, delete of other tables, object, unless i define so.
Please help, it is very urgent, i don't have time to read securities manuals :).

How do i properly define permission?

p.s. and it is the simpliest situation i have more complicated: for example:

I have table A, B, C
I have procedures spXXX, spYYY
I have trigger trB for table B insert

procedure spXX is called
1. forms update sql to update table C
2. executes dynamic update sql
3. inserts a row in to table B
3.1 a triger trB is called
3.2 trigger calls spYYY
3.2.1 procedure spYYY forms dynamic to update table A sql executes sp_executesql
4.end

Well, it is a concept.

p.p.s i forgot the reason why i use "sp_executesql" but prety sure i have a good one :).

THANKS IN ADVANCE.

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-02 : 17:17:44

"I forgot the reason"

Well the reason is probably poor database design

In ANY case...

You need to Grant ISUD to the Role for all tables

STOP USING DYNAMIC SQL

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

Add yourself!
http://www.frappr.com/sqlteam

zymantas
Starting Member

3 Posts

Posted - 2010-06-02 : 17:53:25

quote:
Originally posted by X002548

"I forgot the reason"

Well the reason is probably poor database design

In ANY case...

You need to Grant ISUD to the Role for all tables

STOP USING DYNAMIC SQL

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

Add yourself!
http://www.frappr.com/sqlteam

I am sorry but it is lazy answer. No, database design is not poor. Maybe you didin't understood problem? I am not native english.
I can't stop using dynamic sql, it is nessary to my system.
I am programming procesor emulator, and database provides all functionality.

zymantas
Starting Member

3 Posts

Posted - 2010-06-02 : 19:00:55

http://www.sommarskog.se/grantperm.html

I found this very helpful article on dynamic sql, i found out i could use certificates, or impersonation. But in some procedures i identify user by its login, so it's not suited for me...i think this problem could not be solved, in my case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources