April 16, 2003 at 5:02 pm
Hello,
I have a web developer who belongs to the administrator group on the sales server and I want enforce preventing him to write directly to the tables of a db that I manage. I gave him select rights to the tables but he has rights to write to the table as well, possibly because he belongs to the admin group, would revoking his rights to the tables still enable him to write to a table via a sproc (which is ok with me)?
Thanks
-Francisco
-Francisco
April 16, 2003 at 5:08 pm
Anyone that has execute permissions on a stored proc can update the tables the SP updates, even if the user does not have permissions to update the table.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 16, 2003 at 5:22 pm
quote:
would revoking his rights to the tables still enable him to write to a table via a sproc (which is ok with me)?
With the proviso you stay away from dynamic SQL. If you use dynamic SQL within the stored procedure, it'll execute in a separate security context from the stored procedure and the query itself will be checked.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
April 16, 2003 at 5:42 pm
thanks for the help.
So... it will be safe for me to revoke his table write access, w/o affecting his ability to write sprocs that write to the table.. (correct?)
-Francisco
-Francisco
April 22, 2003 at 1:34 am
HI,
from my understanding if a developer belongs to the local or domain admin group he is by default member of the SQL dbo group. So revoking any rights from his own account will not work, in this case the dbo account will apply. The only thing I can think of is to remove the BUILTIN/Administrator account from SQL Server logins, add the developers account back again and give him the appropriate rights.
NOTE: Removing the BUILTIN/Administrator group is not a trivial thing to do. You should consider the pro and cons. For you might need to change SQL Server agent job etc... There are some article about this on MS knowledge base. Search for BUILTIN/administrators..
HTH
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply