IUSR_ To Run Function, Drop Table..
Jul 15, 2007
Helo all,
i dont know if that is the right way, but i was wanted to make kind of sql profiler on a webpage :) and cool features would be to usa ajax to fire it async in some time secvence.. ok, ok, here goes my code and expla... i have sql2005 (express edition,, cose i think its so cool and for a lot of projects can be used for), attached startup procedure, whit> sp_procoption 'kreni_instanco_moja', startup, true, and kreni_insta... is like:
----------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[kreni_instanco_moja]
AS
BEGIN
exec kp.dbo.start_trace
END
----------------
ok and my start_trace is:
----------------
ALTER PROCEDURE [dbo].[start_trace]
AS
BEGIN
DECLARE
@traceidnum INT,@on BIT,@file_path NVARCHAR(50),@maxsize bigint;
SET @on = 1;SET @maxsize =5;
SET @file_path = 'O:BU_DATABASE' + convert(varchar,getdate(), 102);
EXEC sp_trace_create @traceid = @traceidnum OUTPUT, @options = 2, @tracefile = @file_path, @maxfilesize = @maxsize, @stoptime=null, @filecount =0;
EXEC sp_trace_setevent 2, 12, 1, @on -- ili 12 ili 13
EXEC sp_trace_setstatus 2, 1 -- 1 = START, 0 = STOP
END
----------------
actual code that brings table and shows sql executed is:
----------------
use kp
drop table trace
select textdata into trace from
fn_trace_gettable(N'O:BU_DATABASE' + convert(varchar,getdate(), 102) + '.trc',1)
select textdata from trace
go
----------------
and it works fine,, but when its fired from my local admin connection, and try that whit IUSR*???? not going... ok, i think that IUSR have to have privilage to open that O:BU_DATABASE ... file, and to have priv to drop tabel and to fire that function..fn_trace_get ... ?? so, that is where i got stuck up... any help would be veryyyy apreciated... hey, we can put some kind of project if anybody is interested! :) to have admin page and to monitor sql code that is executed,,, and remotely
if i was not clear in some explaining, you can ask me and ill try better
thenx, kpendic
View 2 Replies
ADVERTISEMENT
Jul 26, 2006
Dear all,
Basically I want to set chain up the rights so that the anonymous web user IUSR_ .. can execute the new .NET subs, functions etc in the assembly, just as the anonymous web user can execute Stored Procedures when granted. In this way, it should be possible to call the .NET assembly just as classic stored procedures from ASP/ASP.NET.
I have written a .NET function which I can successfully execute if I log on to the database as an administrator by sending this T-SQL query; it returns the result of a given string:
select dbo.CLRHTMLString('abc')
The scenario is now to try to grant access to this assembly for a different role (webuser), which the classic IUSR_MYSERVERNAME is a login of, so that I can call the .NET Assembly when I am authenticated as the anonymous web user (e.g. via ASP, etc.).
To test access, I created a login (webusertest) for a user (webusertest) in the same role (webuser) on the database. But when I use this login, which supposedly has the same rights as the IUSR_, execution right is denied:
EXECUTE permission denied on object 'CLRHTMLString', database 'adt_db', schema 'dbo'.
Note: The 'webuser' database role has Execute permission on the Assembly.
I have also tested this from my actual web page, with the following results:
(1) IUSR_MYSERVER member of db_owner role: Web page has right to call assembly.
(2) IUSR_MYSERVER not member of db_owner role: Web page does not have right to call assembly.
Further test results:
(3) Function can be called when making the user "webusertest" member of the "db_owner" role, which is too much rights to grant for the anonymous web user.
(4) When adding the user 'webusertest' to get 'Execute' permissions on the assembly, it does not get added. After clicking OK, there is no warning message, but when opening the Assembly Properties -> Permission dialog box the same time, the 'webusertest' user does not appear in the list.
Thankful for any advice on this matter.
View 4 Replies
View Related
Oct 9, 2006
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies
View Related
Apr 18, 2007
Hi
I spent the whole week trying to find a solution with a problem of authorization/access on a new website (aspnet2 + slqserver 2005)
The site was running well in the test environnement but once deployed the production site behavior was quite erractic. some clients run ok, others not and that changes for the same client with each session.
it appeared that the problem came from the access to stored procedures. sometime, theirs accesses were authorized sometime denied.
finally, tests showed that the client's userid was either aspnet or iusr_
by putting the same permissions on these 2 accounts, the problem is solved and all the clients are running fine.
now the question : for which reason the client connect randomly to one or the other of these 2 id ? is this normal ?
best regards
View 1 Replies
View Related
Aug 28, 2015
I have a temptable with a list of user IDs that I want to drop so I created a script to do a cursor and run through my drop functions. The drops work by themselves and the ver check works with them but when I wrap them in the cursor all i get is an output for each user in the results window in ssms. why it's not setting the variable and instead outputting to results?
DECLARE @ver nvarchar(128);
DECLARE @UserName nvarchar(50);
DECLARE @UserD nvarchar(80);
DECLARE @LoginD nvarchar(80);
-- Initialize the variable.
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
[code]...
View 7 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Mar 14, 2007
which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.
View 7 Replies
View Related
Dec 9, 2007
Hi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
View 8 Replies
View Related
Jun 23, 2006
Hello !
I try to drop a table, but I get this message :
Could not drop object 'dbo.cs_Users' because it is referenced by a FOREIGN KEY constraint
This message does not tell me which is the Foreign key constraint so that I may disable it.
Any solution to force the dropping?
Thank you very much!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
View 3 Replies
View Related
Dec 29, 2006
hi, is it possible to recover the dropped table?
if means please tell me how.
View 3 Replies
View Related
Jan 14, 2008
hi everyone,
I have a couple questions. I'm very new to SQL and I have this problem:
I need to be able to drop the contents of Existingtable_B into Newtable_A - I found this command (below) that will make a 'copy' but I don't want to keep the contents of Existingtable_B. Is it possible to drop them into Newtable_A instead of copy? Also, I want to do this for 5 tables on Sundays at midnight.. how could I schedule that? Finally, what happens if there is not enough space or some other critical error happens during this procedure? I don't want to lose the data.
Any help is greatly appreciated.
View 4 Replies
View Related
Dec 12, 2005
Hi,When I drop a table in Sqlserver 2000 database, The following error occurs:Server: Msg 1204, Level 19, State 1, Line 1The SQL Server cannot obtain a LOCK resource at this time. Rerun yourstatement when there are fewer active users or ask the system administratorto check the SQL Server lock and memory configuration.What's wrong? Any help is greatly appreciated, thanks.
View 4 Replies
View Related
Jul 20, 2005
Hi everybody,I need some help in SQL Server. I am looking for a command that will "Dropall user table" in auser database.Can anyone help me?Thank you very muchSabrina
View 1 Replies
View Related
Jan 20, 2006
I am unable to get the table in the following VB code to actually DROP. A straight SQL version with literals (no variables) runs "successfully" as a query but the table also fails to DROP. Can anybody explain what I'm doing wrong?
sSQL = ""
sSQLExists = ""
sSQLExists = "IF OBJECT_ID (N'ImEx.dbo." & TableName & "', N'U') IS NOT NULL"
sSQLExists = sSQLExists & " DROP TABLE ImEx.dbo." & TableName & ";"
sSQL = "CREATE TABLE [" & TableName & "] (" & sFieldInfo & ");"
ConnectSQLExpress "ImEx.mdf"
DbConn.Execute sSQLExists
DbConn.Execute sSQL
DbConn.Close
View 3 Replies
View Related
Jul 27, 2006
What kind of problems may I see if a process drop and re-create a set of tables every night?
View 1 Replies
View Related
Feb 9, 2008
i am using vb.net and ms sql server 2005 express.....what is the syntax for dropping a table if existsi have used this but it says incorrect syntax near if Dim cmda As New SqlCommand("drop table " + test + " if exists", New SqlConnection(strdb)) cmda.Connection.Open() cmda.ExecuteNonQuery() cmda.Connection.Close()any solutions???? plz only answer in vb.net and sql server express
View 8 Replies
View Related
Apr 30, 2008
I'm trying to drop a temporary table. I keep getting this error:
Cannot drop the table '#temp_table', because it does not exist in the system catalog.
I tried the following but it did not work, so help would be appreciated.
if object_id('tempdb..#temp_table') is not nulldrop table #temp_table
View 1 Replies
View Related
Mar 15, 2001
I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.
What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error
'tblProducts' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.
So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).
Does anyone know why? We are running SQL7 SP2.
Email me on ftowicz@icontact.com.au
View 4 Replies
View Related
Jun 13, 2001
We have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
Anyone know what could be the problem here?
View 2 Replies
View Related
Aug 2, 2001
Hi all,
how can I drop a column in a table.
View 1 Replies
View Related
Oct 19, 2001
Hello,
What's the command for dropping an existing Foreign key on a table and reacreating it in SQL Server 6.5
Thanks
Sri
View 1 Replies
View Related
Nov 13, 2000
We have a publication from database a to database b. Database a containts table1 to be published to databasae b. Database b contains a publication of table1 to database c. When we go to rerun the publication from database a it errors saying cannot drop table1 since it is part of database b's publication. how do you do this.
Thanks in Advance,
Phillip M. Triocli
View 1 Replies
View Related
Mar 17, 2000
Hi,
I had a question and did not obtain an answer. So I am trying to rephrase and ask again, in case I was not clear the first time.
When we drop and recreate a table, do we always have to recompile the stored procedures that reference the table? Or is only under certain scenarios that we need to do so- like if an index on the table is changed
Please let me know
Thanks in advance
Kiran
View 1 Replies
View Related
Jul 12, 2000
I have a group that has select, insert, update, delete permissions and I have a user in that group that
needs permissions also to drop tables.
does that login need to be aliased to dbo - which is in public -- do I then to give puboic all the other permissions.
Is there a way to just give one login all permissions including dropping tables??
Help!!
View 1 Replies
View Related
Jan 30, 2008
I want to get rid of a huge table. DELETE FROM would take for-freaking-ever, so I was thinking of doing DROP TABLE. Would this be any faster?
If not, would doing a TRUNCATE TABLE and then DROP TABLE be fast? What's the best way to nuke a big table?
Thanks.
View 4 Replies
View Related
Feb 18, 2004
in mysql i can drop a table or db if it currently exists using
drop table if exists [table1] or
drop database if exists [db1]
is there an equalivant in ms sql
thanks
View 4 Replies
View Related
Jul 21, 2004
I created the #Temporary table in MS SQL. Now i want to drop this #Temporary table, but i want to check first before i drop the this table. How to check the Temporary table exist or not in MS SQL?
View 4 Replies
View Related
Feb 20, 2005
Hi all,
I have 2 questions:
1. How can I drop /remove a complete db from MSDE desktop engine?
2. After exporting my db onto MSDE server and get connected those db tables in FE (adp), how can I create a new table in the same back end db?
I know these are basic questions but since I am new to MSDE I hope the forum would bear me.
With kind regards,
Ashfaque
View 5 Replies
View Related
Sep 20, 2005
is there a way to drop all the indexes on a table other than listing them out separately in a drop index statement?
View 2 Replies
View Related
Mar 4, 2006
Hi,
Here is what I have done:
1. I created two tables using:
CREATE TABLE CursorTest
(
RowID INT,
RowText CHAR(4)
)
GO
CREATE TABLE CursorTestOdd
(
RowID INT,
RowText CHAR(4)
)
GO
I then populate the tables using:
SET NOCOUNT ON
DECLARE @intCounter INT
DECLARE @chrTextOdd CHAR(4)
DECLARE @chrTextEven CHAR(4)
SELECT @intCounter = 1
SELECT @chrTextOdd = 'Odd'
SELECT @chrTextEven = 'Even'
WHILE (@intCounter <= 200000)
BEGIN
IF (@intCounter % 2) = 0
BEGIN
INSERT INTO CursorTest VALUES (@intCounter,
@chrTextEven)
END ELSE
BEGIN
INSERT INTO CursorTest VALUES (@intCounter,
@chrTextOdd)
END
SELECT @intCounter = @intCounter + 1
END
GO
3. I then tried to use cursor to insert and delete rows from the tables
DECLARE @intRowID INT
DECLARE curOddRows CURSOR FOR
SELECT RowID
FROM CursorTest
WHERE RowID % 2 = 1
OPEN curOddRows
FETCH NEXT FROM curOddRows INTO @intRowID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
BEGIN TRANSACTION
INSERT INTO CursorTestOdd
SELECT *
FROM CursorTest
WHERE RowID = @intRowID
DELETE CursorTest
WHERE RowID = @intRowID
COMMIT
FETCH NEXT FROM curOddRows INTO @intRowID
END
CLOSE curOddRows
DEALLOCATE curOddRows
GO
Here comes the mess:
I have no problem with codes in step 1&2,but when I tried to execute the codes in step3, I got no error, however, when I checked the two tables, nothing has been changed which means that my insert and delete are not working on the two tables. SO I tried to debug the script and found out that the code:
WHERE RowID % 2 = 1
is not working correctly, it seems that the code returns nothing, if I changed it to
WHERE RowID % 2 = 0
it returns the correct answer.
I then tried to execute the modified codes in step3, I got a message saying that
"A cursor with the name 'curOddRows' already exists."
If I changed the name to 'curOddRows1' and execute, it will say:
"The cursor is already open."
But I execute the code again whatever, and it takes a long time for it to finish such that I cannot wait. So I clicked the 'stop' button to cancel the execution.
Now I want to drop the tables and got following error:
**************************
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Table 'dbo.CursorTest'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exceptio nTemplates.FailedOperationExceptionText&EvtID=Drop+Table&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
===================================
Drop failed for Table 'dbo.CursorTest'. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exceptio nTemplates.FailedOperationExceptionText&EvtID=Drop+Table&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Dr opImpl()
at Microsoft.SqlServer.Management.Smo.Table.Drop()
at Microsoft.SqlServer.Management.SqlManagerUI.DropOb jects.DoDropObject(Int32 objectRowIndex)
at Microsoft.SqlServer.Management.SqlManagerUI.DropOb jects.DropAllObjects(Boolean stopOnError)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Dr opImplWorker(Urn& urn)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Dr opImpl()
===================================
Lock request time out period exceeded. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
Server Name:
Error Number: 1222
Severity: 16
State: 56
Line Number: 2
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
**************************
So how can I drop the table, right now I can not even
view the tables' contents, cannot use 'SELECT' etc.. commands, whenever I tried to access the two tables, the program will be keeping doing the execution without stop.
Your comments and suggestions are greatly appreciated!
Thanks,
View 5 Replies
View Related
Sep 26, 2007
Using SQL Server 2005. I have a stored prod that uses a temp table. I need to test at the start of the prod to see if the temp table is there. Using the following code at the start of my prod, but does not run.
IF exists(select * from ##TO_STATUS_TBL)
DROP TABLE ##TO_STATUS_TBL
What is the best way to check and/or drop the temp table.
Thank you, David
View 10 Replies
View Related
Feb 15, 2004
Hi,
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how....
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":
Dim db as ADODB.Connection
'... open connection to database
Dim strCmd As String
strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"
db.Execute strCmd
Thank you in advance
View 1 Replies
View Related
Jun 26, 2015
When I drop a table should I also drop it's FK ? or they keys are removed with the table?
View 2 Replies
View Related