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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-11-08 : 08:12:55
|
| John writes "I am trying to write a stored procedure (non stored proc solutions also welcome) to allow a user of my SQL 2000 database to add a user specified column to a pre-determined table. I would prefer for the user running the stored proc to not be able to issue alter table statements in the databse outside of the scope of this stored procedure.For example (psuedo-code)myProc (input parameter @FieldName as Varchar) ALTER TABLE MyTable ADD COLUMN @FieldName varchar(100)Problem 1: The above code obviously won't work, the best alternative I could find is to build a dynamic SQL statment and use the EXEC method to run it. Problem 2: Using the EXEC method in a stored proc causes the statement to be executed in the context of the user calling the stored procedure rather than the owner of it (who has rights to alter this table)Problem 3: I am out of ideas. If anyone has any ideas with regard to how to write this stored procedure without dynamic SQL or knows of an alternate way to accomplish what I am looking for I would love to hear it." |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-11-08 : 08:16:53
|
| What are you trying to accomplish though? You've pretty much answered your own question in that, if you want to use dynamic SQL, then the users need to have alter table rights. But then you *are* letting them alter the tables. To be honest I think going in that route is a path to a world of pain, even if your intention is to somehow log the changes through the stored procedure. You could end up with added columns all over the place. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-08 : 08:23:18
|
| You could put the alter table request into a table and have a scheduled task that executes it as sa. There will be a slight delay but you can have it run every few seconds if you wish.This is not a good idea though as it can have interesting effects onn already compiled SPs and anything that happens to be running at the time.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jfuex
Starting Member
29 Posts |
Posted - 2002-11-08 : 10:07:15
|
| Let me clarify based on the feedback so far. Quick and dirty explanation. The point of this stored procedure is that I need for users of my web application to be able to customize the database by adding an extra column to one of the tables in that database. Unfortunately SQL Server doesn't appear to have granular enough rights to limit this to a single table. I don't want the users to have full DDL_Admin rights to the whole database, just the ability to add columns through this stored procedure (note that only the column name is passed in, not the table name). Additionally the stored procedure also sets some other settings in my application so it will recognize the new column in the app. This particular table is designed to be customizable to some extent and thus I have been very careful to avoid using any stored procedures or views based on this table that might break if it changes. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-08 : 10:39:48
|
| This sort of thing can be done by having a table to define the column and the data.tbl ExtraColsid, fldname, fldtypetbl ExtraDataExtrColsID, SourceTablePK, DataThen any SP that accesses the original table also gets the data from this dynamic table. It will probably need some dynamic sql or a temp table to return the dynamic column name - but you could return the display names separately or in the first row.This is the way a some configurable crm systems handle it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jfuex
Starting Member
29 Posts |
Posted - 2002-11-08 : 14:25:20
|
| Not a bad Idea. Let me tinker with this approach a bit. If it works I will come back and post the code for the benefit of the group.ooo O ooo O |
|
|
|
|
|
|
Stored procedure to dynamically alter a table.