April 30, 2009 at 1:11 pm
how to prevent T-sql code from running on production server
April 30, 2009 at 1:35 pm
Hello,
I am guessing that it is only a particular piece of T-SQL that you want to prevent from running on your Production Server e.g. a Stored Procedure; or do you actually mean all T-SQL?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
April 30, 2009 at 1:56 pm
thts right i meant running any tsql code like stored procedure on the production server not the whole tsql
May 1, 2009 at 6:36 am
Well, do you mean having a particular user or set of users run this stored procedure? You can DENY execute privileges to the role or user.
DENY EXECUTION ON schema.proc TO User
You can also look at restricting users access in other ways. We don't allow users to look at structures on the production database. They only have permission to execute procedures and then only the procedures we want them to have.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2009 at 4:53 am
Is there any way to deny all TSQLs on sql server for a particular user or for all.
May 6, 2009 at 5:10 am
DKG (5/6/2009)
Is there any way to deny all TSQLs on sql server for a particular user or for all.
Yes, don't give them a login. :-):-):-)
No seriously, why would you create a user which isn't allowed to do anything ?
[font="Verdana"]Markus Bohse[/font]
May 6, 2009 at 5:40 am
No i didn't mean that way 🙂
The question just came in my mind was, if a user can do other limited operations (can see the DBs by Ent Manager) but could not execute TSQLs etc.
May 6, 2009 at 5:59 am
DKG (5/6/2009)
No i didn't mean that way 🙂The question just came in my mind was, if a user can do other limited operations (can see the DBs by Ent Manager) but could not execute TSQLs etc.
Even viewing a list of databases in EM involves executing TSQL in the background (SELECT * FROM sysdatabases).
If you only want to give read-only access you can do this through the standard databases roles.
[font="Verdana"]Markus Bohse[/font]
May 7, 2009 at 9:42 am
DKG (5/6/2009)
No i didn't mean that way 🙂The question just came in my mind was, if a user can do other limited operations (can see the DBs by Ent Manager) but could not execute TSQLs etc.
MS SQL Server books online (BOL) are very useful... try the following link about the GRANT and DENY statements. Should give you what you are looking for. Also have a read of the article on Database permissions. Good luck!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply