"ALTER TABLE" doesn't work from .asp

[mpnut]

I am very new to .asp so any help would be greatly appreciated. I have a .asp page that I am trying to use "ALTER TABLE" to add a column. However, it doesn't seem to want to work for some reason. Here is the .asp:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout=60
conn.Open "DSN=AspTest;uid=aspuser;pwd=aspuser"

sql="ALTER TABLE Recall ADD R10101 bit"

'on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write("Did not process")
else
Response.Write("<h3>" & recaffected & " record updated. Please Wait...</h3>")

end if

%>

Here is the message I get when I debug:


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to perform this operation on table 'Recall'.

/Test/Recall/NewRecPage.asp, line 17


I don't think it's a permissions issue, because I can run update,insert and other things no problem. Also, I know the syntax is right because I can run "ALTER TABLE Recall ADD R10101 bit" from Query Analyser and it works fine, just not through the ODBC connection.

 

[gmmastros]

This is a security issue.

I can run "ALTER TABLE Recall ADD R10101 bit" from Query Analyser

In Query Analyzer....

Click File -> Connect
Select SQL Server Authentication
For Login Name, type: aspuser
For Password, type: aspuser

Now try to run the Alter Table command.

According to Books On Line...

Permissions
ALTER TABLE permissions default to the table owner, members of the [!]sysadmin[/!] fixed server role, and the [!]db_owner[/!] and [!]db_ddladmin[/!] fixed database roles, and are not transferable.

I don't know about you, but I wouldn't want an asp user to have any of those permissions.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I strongly suggest that you don't do this, but you can add the aspuser to one of those roles by running this in query analyzer.

sp_addrolemember 'db_ddladmin', 'aspuser'

You will need to do this from an elevated role (meaning you cannot be logged in as aspuser to run this command). You need to be a sysadmin or db_owner.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mpnut]

Thank you very much for your replies. You are correct, it probably IS a bad idea to let users have that access. It's just nice to finally know WHY it's happening. Thanks so much.