Prevent User From Deleting Tables
Hi,
How can I prevent a colleage to delete tables in a specific database.
Yes he has access to Enterprise Manager. We would like to allow him read only to the live databases.
Is this possible?
View Complete Forum Thread with Replies
Related Forum Messages:
Archive Data Instead Of Deleting It To Prevent 4GB Data Limit
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data. Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days. We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok. Any other solutions that would allow us to archive data instead of deleting it? Thanks.
View Replies !
Prevent ODBC By USER/APP
Is there a way to monitor all ODBC connections to a MSSQL server and prevent a username/ODBC combination. My problem is that we have many frontends for viewing reports, but we managed them all and users are not allow to make their own connections. Some users now uses MSAccess over ODBC to draw their own reports - they have all the permissions as neededd by the other apps. thanx
View Replies !
Deleting From 2 Tables?
Hi, I am pretty new to SQL and know that I am probably going around this the wrong way. I want to make a stored proc that deletes rows from table 1 and delete rows from table 2 where the common link is the id. Any help would be greatly appreciated! Many thanks moop
View Replies !
Deleting Tables
I am new to SQL server and have just installed SQL Server 2005 (came withOffice 2007- think it's Express edition).I am trying to get my head around this.Just been working on an Access data base and have upsized to SQL.Having made several changes I have done this a number of times - linkingboth direct to file and creating a new application Access SQL project.Now I would like to know how to delete any tables or databases I have placedon the SQL before my system gets too messy.Furthur information - This is on my personal computer and is not shared orused by other people.Any ideas?Scott
View Replies !
Deleting Tables
Does anyone know why it takes a long time (Approx 10 minutes) to delete a DB. I have 2 identical environments for test and prod. I can delete DBs from my test environment quickly but it takes forever to delete a DB from the production environment. I am running SQL2000 SP3a. There are about 25 DBs in the environment with their sized ranging from 100meg to 2 gig. The only differnece in the environments is all test DBs are simple recovery mode and modt prod DBs are full. Thanks, Ken
View Replies !
Deleting Tables
hi, How can I delete two tables with a WHERE condition. That is I can't do this -What can be an alternate of this statement - Delete from Tab1,Tab2,Tab3 where Tab1.name = Tab2.Name and Tab2.name = Tab3.name TIA
View Replies !
Deleting From Three Tables
I'm triyng to delete from three tables. Issue Table: Column Name Data Type Allow Nulls issueID int Unchecked name varchar(50) Unchecked title varchar(100) Checked description varchar(500) Checked crntIssue bit Checked frntPage int Checked archived bit Checked navOrder int Checked dateCreate datetime Unchecked Outlook Table: Column Name Data Type Allow Nulls ID int Unchecked menu bit Checked mnuOrder int Checked mnuLevel int Checked parent int Checked issueID int Unchecked masterPage varchar(100) Unchecked visible bit Checked name varchar(50) Checked title varchar(250) Unchecked description varchar(1000) Checked summary text Checked contents text Checked image varchar(50) Checked imgCaption varchar(1000) Checked approve bit Checked createDate datetime Unchecked Links Table: Column Name Data Type Allow Nulls lnkID int Unchecked linkFromID int Unchecked linkToID int Unchecked Issue table contais all the magazine issues. Outlook table contains all the pages of the Issue table. And the Links table contains the links or connection between parent page and child page. So here's what I wanted to do. When I click the delete issue button, I want to delete any pages, links, and issue from three tables that matches the issue ID that I wanted to delete. So for example, if I wanted to delete issueID 2, all the pages in the Outlook table and any links of those pages(lnkFromID) that are in the Links table should be deleted too. The lnkFromID and lnkToID are foriegn key of Outlook.ID table. The lnkFromID is the parent and lnkToID is the child. I was wondering that maybe I can three delete statements for three tables. Is this a possibility? So any help is much appreciated.
View Replies !
Deleting From 3 Tables
Hello everyone, I have to delete some rows from three different tables. My tables are: - VERSION_1 - ID - VERSION_2 - ID_VERSION FOREIGN KEY - VERSION_3 - ID_VERSION FOREIGN KEY The 3 tables are related to each other by the VERSION_1 ID. what i have to do is deleting first from VERSION_3, then from Version_2 and finally from VERSION_1. This is what i got so far: DELETE FROM VERSION_3, VERSION_2, VERSION_1 WHERE VERSION_3.ID_VERSION = ? AND VERSION_2.ID_VERSION = ? AND VERSION_1.ID = ? I cannot make it work, could someone please help me? i am stuck at this. Thanks in advance
View Replies !
Deleting From Several Tables
How do I delete from multi tables? There are no issues with keys. Will this work? DELETE MC.Document FROM dbo.dw_MasterClaim MC, dbo.dw_MasterClaim_Checks CHK, dbo.dw_MasterClaim_ChgDate,dbo.dw_MasterClaim_Diagnosis, dbo.dw_MasterClaim_InsNo, dbo.dw_MasterClaim_Pay, dbo.dw_MasterClaim_ProcLine, dbo.dw_MasterClaim_ProcLine2, dbo.dw_MasterClaim_ProcLine3, dbo.dw_MasterClaim_RiskPool, dbo.dw_MasterClaim_SuspHist WHERE MC.Document IN (Select Document FROM fl1stDwImport.dbo.raw_MasterClaim WHERE Document = MC.Document)
View Replies !
JavaScript Error When Deleting User From Report
Hello, Have a problem here needs urgent help. We are on SQL Server 2005, just recently installed SP2. Not sure if the problem is related to that but just now I tried to delete a user from a report (Properties > Security > Delete) I got a JavaScript error exclaimation mark at the bottom of my browser, when I clicked on it, it showed javascript error: 'return' statement outside of function. The delete user function worked well all along until today, our IT installed SQL 2005 SP2 last week, not sure if it is related. Any solution? (uninstalling SP2 is not an option). and all other javascript functions worked well except the remove user in the security setting..I have no idea what is wrong.. Please advise!! HELP!!!!! Thanks in advance. casey
View Replies !
Deleting Rows From Many Tables
What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SoderquistString %>" DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID" SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @State)"> <DeleteParameters> <asp:Parameter Name="UserID" Type="Int32" /> </DeleteParameters> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment: DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID DELETE FROM [photo] WHERE [UserID] = @UserID; DELETE FROM [album] WHERE [UserID] = @UserID; DELETE FROM [comment] WHERE [UserID] = @UserID; ...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!
View Replies !
Automatically Deleting Tables
Is there a way to automatically delete tables in a database? I want to delete tables with a specific prefix in their names (that I assign to them) based on their age. Is there a way to do this automatically while keeping the tables that I don't want to touch? joe
View Replies !
Deleting # NOT ##temp Tables
Hi how can I delete a local temp table? I know a golbal is deleted by if exists(select * from tempdb..sysobjects where name='##MyTemGlobalTable' and type='U')drop table ##MyTemGlobalTable' but how do I drop a #MyTemGlobalTable' ? thank you
View Replies !
Deleting Records From 2 Tables At The Same Time
Hello all, I have a DTS package set up to import a text file on a daily basis. I need to dump the data in 2 table after 7 days of the last import .this is the code that I have Delete From TblTemp date(Day(-7), CurrentStamp). But for some reason it deleting the data right after it imports it. And it doesn't delete anything out of the other table. Thanks in advance
View Replies !
Ran Out Of Ideas. Please Help In Deleting From Multiple Tables.
want to delete rows from two tables after a join. in Access, here's something we can do: delete table_A.*, table_B.* from table_A left join table_B on ... but in ms sql, it appears (to me) that you can only delete from one table at a time. how would i accomplish what i want to do? also, is there a distinctrow equivalent in ms sql server? thanks
View Replies !
Deleting Frm Multiple Tables At A Time
I have 3 tables . iwant to delete rows from all the three tables at same time using single statement.All the 3 tables have a unique column which will be supplied ny the user. DELETE FROM T1,T2,T3 WHERE column1='1' how do i do it.
View Replies !
Updating/Deleting Multiple Tables Simultaneously
Hi, I'm using ASP with a JScript variant and MSSQL Server 2000. I would like to write a script that basically erases all data except for a few things. Is there a way to update multiple tables at once without having to write lines and lines of code? I tried UPDATE tbl1,tbl2 SET uid='asc', but to no avail. It gave me a syntax error. My thinking behind it is something like... UPDATE dbo.* SET uid='mferguson' and after that I can delete stuff like DELETE dbo.*... Any ideas? I know the above is ASP, I've tried this thread in the ASP forum with no avail... they referred me to this forum.
View Replies !
Deleting Rows From Multiple Tables On A Condition
Hi, I have different tables with the same schema as follows ID Name ----------------- <Table 1> ID Name ------------------- 1 Name1 2 Name2 3 Name3 <Table 2> ID Name ------------------- 1 Name1 4 Name4 5 Name5 I just want to delete the row where ID = 1 from these tables in one query ? Is it possible?? Thanks ~Mohan
View Replies !
Deleting Records From Multiple Tables In SQL Server
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem. I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names): Table 1: Classes --Columns: ClassID, ClassName Table 2: Roster--Columns: ClassID, StudentID, Student Name Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName Table 4: Scores--StudentID, AssignmentID, Score What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement. What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3. The stored procedure I created was DELETE FROM Classes WHERE ClassID=@classid I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said: The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'. The statement has been terminated. What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting? I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error. Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old. Further, is there something else I need to do besides assigning primary keys and foreign keys?
View Replies !
Deleting Duplicate Records From Lots Of Tables
Hi All, So.. I'm a complete newb to SQL stuff. I managed to find the 'Deleting Duplicate Records' from SQLTeam.com (thanks, by the way!!).. I managed to modify it for one of my tables (one of 14). -- Add a new column Alter table dbo.tblMyDocsSize add NewPK int NULL go -- populate the new Primary Key declare @intCounter int set @intCounter = 0 update dbo.tblMyDocsSize SET @intCounter = NewPK = @intCounter + 1 -- ID the records to delete and get one primary key value also -- We'll delete all but this primary key select strComputer, strATUUser, RecCount=count(*), PktoKeep = max(NewPK) into #dupes from dbo.tblMyDocsSize group by strComputer, strATUUser having count(*) > 1 order by count(*) desc, strComputer, strATUUser -- delete dupes except one Primary key for each dup record deletedbo.tblMyDocsSize fromdbo.tblMyDocsSize a join #dupes d ond.strComputer = a.strComputer andd.strATUUser = a.strATUUser wherea.NewPK not in (select PKtoKeep from #dupes) -- remove the NewPK column ALTER TABLE dbo.tblMyDocsSize DROP COLUMN NewPK go drop table #dupes Now that I've got that figured out, I need to write the same thing to fix the other 13 tables (with different column info)- and I'll need to run this daily. Basically I've put together some vbscript that gathers inventory data and drops it into an MSDE db (sorry - goin for 'free' stuff right now). Problem is it has to run daily so that I'm sure to capture computers that turned on at different times etc which ever-increases my database 'till I bounce off the 2GB limit of MSDE. So the question is, what would be the best way to do this? Can I put the code into a stored procedure that I can execute each day? Thanks for your help....
View Replies !
Stored Procedure For Deleting Multiple Tables/rows
Hi, I have a relational database with the primary table, table01. And 2 child/foreign tables, table02 and table03. All 3 tables shared the same key - [ID]. I am not sure if this is the correct approach but I am trying to create a stored procedure where if I were to delete a the row in table01 (primary), the procedure will automatically delete the common row in both table02 and table03. I have come up with something like that but it does not seems to be correct. CREATE PROCEDURE [sp_delete_test01_1] (@id [int]) AS DELETE [test01] DELETE [test02] DELETE [test03] WHERE ( [id] = @id)GO Your advise please. Many Thanks.
View Replies !
How To Create Multiple Tables On The Fly So That Every User Each Has His/her Own Set Of Tables?
Hello all, Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients. Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result. What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1 Please help :-D Greetingz, DJ Roelfsema
View Replies !
List User Tables
I'm brain-dead today, sadly. If it weren't for IE remembering previous entries, I don't know if my name and email would have made it into the header correct :-) I want the SQL command that lists the names of all user tables. Alternatively, I have the following problematic Access 2000 code: Public Sub ListAllTables() Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim cnn As New ADODB.Connection Dim i As Integer, j As Integer Dim vgRet As Variant Dim intPrefixLen As Integer Dim strAppend As String DoCmd.Hourglass True cnn.Open CurrentProject.Connection cat.ActiveConnection = CurrentProject.Connection intPrefixLen = Len(CON_pkgPrefix) Debug.Print cat.Tables.Count For j = (cat.Tables.Count - 1) To 0 Step -1 Set tbl = cat.Tables(j) With tbl Debug.Print tbl.Name vgRet = SysCmd(acSysCmdSetStatus, tbl.Name) End With Next Set tbl = Nothing Set cnn = Nothing Set cat = Nothing DoCmd.Hourglass False vgRet = SysCmd(acSysCmdSetStatus, " ") End Sub This code runs fine against an MDB but against SQL it includes all the views, rather than just the tables. If you have a fix for this, that will do just fine! Arthur
View Replies !
How To Copy All The Tables Into The Same Db With A Different User Name?
I need to duplicate all the tables owned by dbo to another object ownername in a database within the same database.That means,if there are 100 table owned by dbo in a 'Database A'. I need to have a total of 200 tables in the same database 100 each for dbo and 100 for another user.They should have same schema including the keys etc and data.How is this possible?Through DTS we can't copy them to the same databse.We do not want to have a temp db inbetween to complete this process.Any ideas on how to do this? NOTE:This is not a one time job. Thanks. Sheila.
View Replies !
Filter Tables According To User
Hi, Im new to SQL Server and Im having trouble to decide wich is the best way to filter the tables that one user can read and write. For example the table Sales as a field named Branch (SMALLINT) with values 1 or 2 (the store that made the sale). User "A" can see and write only records where Branch = 1. Im developing a windows program that shows all the purchased orders in a DataGridView. So should I make the filters (permissions) in the program or in the SQL Server?. I hope I made my self clear (my english is not so good)
View Replies !
User Tables Really Accesed By SQL
I administre a DB that has about 2000 user tables, unfortunatly I haven´t documentation, and the people that create thouse tables are no more in the company. How can I identify the user tables that really are used by SQL today? I used the Profiler to identify the Stored Procedures that are called recently, but when I try to use Object:Opened event class to look the tables that are opened, it dosn´t display anything. I hope you can help me.
View Replies !
Finding Out All The User Tables Being Used By An Application
Here's the situation, developers inherit a web app from someone,backend SQL db has about 120 user tables and the db is also being usedby other apps. Developers don't have a list of user tables being usedby this app, now, I need to create a new db based on this one, whichwould be used by this app only. So, I intend to find all the usertables being used by this app, then copy its schema and possibly dataas well to a new db. FYI, current ERD is not this app.One option to find out all the user tables being used by this app is todo recursive search for FROM and JOIN against the full spectrum of theuncompiled source code, then, weed through such an extracted list whenin doubt about a particular table, one caveat is a portion of code withsql stmt could have already been commented (no longer being used thoughstill in the code).Better option?TIA.
View Replies !
Allowing A User Access To Only A Few Tables
With MS SQL 2000 Enterprise Manager, is there a way to allow a user accessto only a few tables, but deny the user access to the rest without having togo to all of the tables and denying access? The database has roughly 50tables, but only 3 should be granted to the new user, so as you can see itwould be a painstaking task to manually do this with the *cough* mouse. Or,if I can run some sort of grant script, that would work too. Thank you!
View Replies !
Query To Get All User Tables With Columns
Hi,I tried to create a simple view as followsCREATE VIEW V_ALL_USERTABLE_COLUMNSAS(SELECTOBJ.NAME as TableName,COL.NAME as ColName,TYP.NAME AS TYPEFROMSYSOBJECTS OBJ,SYSCOLUMNS COL,SYSTYPES TYPWHEREOBJ.TYPE = 'U'AND OBJ.ID = COL.IDAND COL.TYPE = TYP.TYPE)Combined with consistent naming conventions I will use this view toeasily find foreign keys; a laSELECT *FROM V_ALL_USERTABLE_COLUMNSWHERE ColName LIKE ('%user_id')There is something wrong with my view definition that I don't getthough; it doesn't return all the columns. I have a table with thefollowing definitionCREATE TABLE [dbo].[c_messages]([cid] [int] IDENTITY (1, 1) NOT NULL ,[touser_id] [int] NULL ,[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fromuser_id] [int] NOT NULL ,[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[message_read] [bit] NOT NULL ,[logout] [bit] NULL) ON [PRIMARY]GOThe problem is that the select I used to define the view doesn'treturn the touser_id column. I have sort of a sneaking suspicion thatthe problem has to do with joining syscolumns.type to systypes.type,but I don't know what to do instead (I'd really like to include thetype; it's useful if I ever changed the type of a primary key and wantto check that I also changed all the foreign keys).Any help would be appreciated!
View Replies !
JDBC API To Fetch Only User Tables
Hi, I am using JDBC DatabaseMetaData's getColumns() to get the table names, column names and datatype of the columns. As a result, I am getting user tables as well as the system tables. How to fetch only the user tables excluding the system tables like sysindexes, etc. I know this is possible by executing a select query (using SYSOBJECTS and xtype = 'U'). But, is there anyway to fetch only the user tables using the databasemetadata API. Please advice, MiraJ
View Replies !
Dropping Statistics From User Tables
Hi, I have founde such a nice code to create statements for dropping all statistics in a database. DECLARE @tblname sysname, @statname sysname, @sql nvarchar(2000) DECLARE c CURSOR FOR SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1 OPEN c FETCH NEXT FROM c INTO @tblname, @statname WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'DROP STATISTICS [' + @tblname + '].[' + @statname + ']' PRINT @sql FETCH NEXT FROM c INTO @tblname, @statname END CLOSE c DEALLOCATE c Please help me with changing this code to take care only on indexes from my own tables (no system ones). Thanks for your help. Przemo
View Replies !
JDBC API - Fetching Only User Tables
Hi, I am using JDBC DatabaseMetaData's getColumns() to get the table names, column names and datatype of the columns. As a result, I am getting user tables as well as the system tables. How to fetch only the user tables excluding the system tables like sysindexes, etc. I know this is possible by executing a select query (using SYSOBJECTS and xtype = 'U'). But, is there anyway to fetch only the user tables using the databasemetadata API. Please advice, Smitha
View Replies !
Database Tables And User Names
We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do. For example, to query a table called Page we would nee to do this on the new box. SELECT * FROM webdev.page unfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix? Thank you for your help...sorry for the newbie type question.
View Replies !
Size Of User Tables In A Database
The following procedure will display the size of all the user tables in a database. CREATE proc sp_tablesize as if exists (select * from sysobjects where name = 'sp_tablesize') begin goto calculate_tablesize end else begin CREATE TABLE #SpaceUsed ( TableName sysname, TableRows int, TableSize varchar(10), DataSpaceUsed varchar(10), IndexSpaceUsed varchar(10), UnusedSpace varchar(10) ) goto calculate_tablesize end calculate_tablesize: declare @tablename nvarchar(50) declare @cmd nvarchar(50) declare c1 cursor for select name from sysobjects where xtype='u' open c1 fetch c1 into @tablename while @@fetch_status = 0 begin set @cmd='exec sp_spaceused['+@tablename+']' insert into #SpaceUsed exec sp_executesql @cmd fetch next from c1 into @tablename end select * from #SpaceUsed drop table #SpaceUsed deallocate c1
View Replies !
|
TRACKER
