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 |
|
lotek
Starting Member
42 Posts |
 Posted - 2005-11-17 : 16:03:55
|
| I have a table variable and some dynamic sql. Is there anyway to execute the dynamic sql in such a way that is only has access to read the table variable. I assume i would need to create a user with no permissions and somehow execute the dynamic sql using that query...Ideas? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-18 : 01:48:51
|
| "table variable"You mean like:DECLARE @MyTableVariable TABLE ...because that will go out of scope after the current statement ends, so can't be referenced by anything else.Kristen |
 |
|
|
lotek
Starting Member
42 Posts |
Posted - 2005-11-18 : 11:43:13
|
| Well basically all i want to do is execute dynamic sql against two views. Read permission only. Its something like'SELECT * FROM view WHERE' + UserSQLI have a table full of these dynamic sql statements and each night they need to be executed via dts.I realize this has security implications, and i want to ensure that the sql can only be executed (read only) against the two views.Ideas on the easiest way to accomplish this?Thanks! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-18 : 12:02:42
|
Provided the user only has SELECT permission on VIEW_1 and VIEW_2 you should be OK (or do users need SELECT permission on the underlying tables that the VIEW points to? I don't think so ... could be wrong though!You could DENY SELECT, INSERT, UPDATE, DELETE, etc.!! on the Tables for good measure ...  Kristen |
|
|
|
lotek
Starting Member
42 Posts |
Posted - 2005-11-18 : 12:43:53
|
| Ok great. Is there anyway to execute a procedure using a different user than the current one.Eg: I am logged in as user1 and i want to exec sp_some_procedure using user2??User2 would of course be the one with limited access.Thanks! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-18 : 12:56:48
|
| If you have execute permission on a stored procedure you can execute it. Your permission (on tables) have no bearing on what the Sproc can/cannot do.Unless the SProc uses dynamic SQL - in which case the user calling the SProc has to have the necessary permissions on the tables / views / etc. that are referenced in the Dynamic SQL.(The later is also true if the SProc references tables in another database; if the Sproc calls another SProc in another database then the current user has to have execute permissions on the target Sproc, but the SProc will then run with its own permissions)Kristen |
|
|
|
|
|
|
Dynamic SQL Permissions