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.
| Author |
Topic |
|
kman23
Starting Member
5 Posts |
 Posted - 2007-09-26 : 08:40:48
|
| I new to SQL 2005 and have couple of questions regarding configuring the user. Let me start by saying what I need to be achieved.1. Create a new user which will be user by couple of people to create and execute stored procedures2. Deny all permissions for this user to access data from certain tables.3. Configure the role and permissions for this user so that the above two is possible.I was able to do the first two steps and when the users uses a query, ( select * from 'that table' ) the result in a permissions error. Also that user cannot view the table in SQL managerThe problem now is that that user can create a stored procedure that has the SQL statement Select * from 'that table' and execute the stored procedure to view the table.The reason I created this user is to allow a team in the company to create stored procedures for Crystal. In theory he should be able to create stored procedures but not get any data from the denied tables. How can I achieve this? I am trying to resolve this issue for a while now and any help is greatly appreciated.Regards, |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 08:49:48
|
| A stored procedure runs with the permissions of the user that created it.So to create a procedure that selects from 'that table' the Author must have SELECT permission on that table.People who then EXECUTE that procedure ONLY need to have Execute permission, on that Procedure, and do NOT need Select permission on the Table.Kristen |
 |
|
|
kman23
Starting Member
5 Posts |
Posted - 2007-09-26 : 14:42:23
|
quote:
Originally posted by Kristen
A stored procedure runs with the permissions of the user that created it.So to create a procedure that selects from 'that table' the Author must have SELECT permission on that table.People who then EXECUTE that procedure ONLY need to have Execute permission, on that Procedure, and do NOT need Select permission on the Table.Kristen
Hi Kristen,Thanks for the post.The problem is that this use was created for creating / developing reports and will be used by couple of people in the company. The same user will be creating procedures and executing it at the same time.This user should be able to execute all procedure he creates or that are already present as long as the stored procedure does not retrieve information from 'that table'.Regards, |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 15:35:41
|
| "as long as the stored procedure does not retrieve information from 'that table'"I may have misunderstood, but if I've understood correctly:Instead of Granting Permissions on various tables, or in "addition" to that as a policy, you can explicitly DENNY permission on specific table(s) and action(s).Might that help in this scenario?Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-26 : 22:37:09
|
| Or put user in different schema. |
|
|
|
kman23
Starting Member
5 Posts |
Posted - 2007-09-27 : 08:53:51
|
quote:
Originally posted by Kristen
"as long as the stored procedure does not retrieve information from 'that table'"I may have misunderstood, but if I've understood correctly:Instead of Granting Permissions on various tables, or in "addition" to that as a policy, you can explicitly DENNY permission on specific table(s) and action(s).Might that help in this scenario?Kristen
Yes and that's exactly what i have done. Denied all permissions on the table in question and any query from that user cannot retrieve information from that table. However a stored procedure created or modified by this user is able to retrieve data from that table. My guess is that the SQL user is not configured correctly. |
|
|
|
kman23
Starting Member
5 Posts |
Posted - 2007-09-27 : 08:55:03
|
quote:
Originally posted by rmiao
Or put user in different schema.
I am new to SQL, I would appreciate it if you could please explain it in a bit more detail.Regards, |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-28 : 00:22:43
|
| Create a schema for thoser users and grant create proccedure permission in the schema, but don't grant them permission to access other schemas in the db. Read books online for details. |
|
|
|
|
|
|
|
|
Configuring user in SQL