User That Can Alter And Modify Tables

Feb 7, 2008

What do I have to do in order to create a user that can alter and modify some/all tables?

Thank you.

View 2 Replies


ADVERTISEMENT

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

ALTER TABLE MODIFY

Jan 19, 2006

hi!

i encountered problems when running this code in SQL Query

ALTER TABLE [dbo].[amsSchedule]
MODIFY(CutOff1 datetime NULL,
[FileName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

my aim is to modify the two fields to change its data type. BUt when im trying to run this command in the query analyzer, itsays "incorrect syntax error '(' "

What do i have to do? please help me...thanks

View 3 Replies View Related

ALTER / MODIFY / OverWrite Table?

Apr 10, 2008

Hi all I was wondering how to do an ALTER command on a Table but without specifying Column Names but rather attempting to overwrite the Table itself with the new fields specified? For instance if I have Table_1 consisting of the following fields:

IDFirstNameSurname

Then use the following ALTER command:




Code Snippet
ALTER Table Table_1
(
ID Int,
FirstName VarChar(50)
)
This would then drop Surname from the Table and leave only ID and FirstName inside it. Is this possible? I have been searching google but can't seem to find what I am looking for.

Thank for comments + suggestions. Regards, Onam.

View 2 Replies View Related

How To Set User Who Can't Modify Any StoredProc And Data In Tables But Can Read Data In Tables And Any StoredProc ?

Jul 13, 2007

Please give me advise ครับ
 

View 1 Replies View Related

SQL Server 2008 :: ALTER Database To Modify Log File

Jun 25, 2015

We are running into the following error while changing a column data type from nvarchar (1200) to varchar(8000)
"Msg 1105, Level 17, State 2, Line 1

Could not allocate space for object 'dbo.TBL1 '.'PK_CL_ID' in database 'Client01' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The statement has been terminated."

Now tried to change the filegrowth of the log file to unlimited

ALTER DATABASE Client01
MODIFY FILE ( NAME = Client01_log, MAXSIZE = unlimited);

The query executes without error but I do not see the auto growth as unrestricted. It's still 2GB

View 6 Replies View Related

SQL Server 2014 :: Alter Database Modify Log File

Nov 6, 2015

I can run this command to make changes for 1 DB USE [master]

GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB )
GO

Is it possible to create a script which changes log file size to let's say 100MB for all DBs in all servers instead of running the above command by logging into each server? We have about 200 servers and close to 3000 DBs.

View 2 Replies View Related

Alter Failed Trying To Modify Surface Area Configuration Xp_cmdshell - Help Needed

May 9, 2007

I installed SQL Server 2005 Enterprise Edition (64-bit) and then applied SQL SP2. When I launch the "SQL Server 2005 Surface Area Configuration" and choose "Surface Area Configuration for Features" I'm trying to enable xp_cmdshell.

After ticking the checkbox, and clicking Apply, I receive the error below. Anyone have any ideas on how to fix this?







===================================

Alter failed. (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Configuration&LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.ConfigurationBase.Alter(Boolean overrideValueChecking)
at Microsoft.SqlSac.Public.Smo.SetSetting(Credentials credentials, DatabaseFeature feature, Int32 value)
at Microsoft.SqlSac.MainPanel.UserControlSSxp_cmdshell.ProcessOK(HashKey givenKey, String machineName)
at Microsoft.SqlSac.MainPanel.FormFeatures.commitDBChanges()
at Microsoft.SqlSac.MainPanel.FormFeatures.commitChanges()

===================================

Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.GetStatements(String query, ExecutionTypes executionType, Int32& statementsToReverse)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd)
at Microsoft.SqlServer.Management.Smo.ConfigurationBase.DoAlter(Boolean overrideValueChecking)
at Microsoft.SqlServer.Management.Smo.ConfigurationBase.Alter(Boolean overrideValueChecking)

View 7 Replies View Related

How To Modify Colum (alter Table) Of Table That Is Replicated?

Jul 20, 2005

Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern

View 1 Replies View Related

How To Modify User Defined Datatypes

May 3, 2000

Hi,

I used user defined datatype but when I tried to change the length of the datatype I found out I could not modify the length by going to property of user defined datatype or I could not drop it without dropping the dependent tables. I got a problem. Anybody can help?


Thanks,

Wilson

View 1 Replies View Related

Modify File On User Database

Jan 20, 2008

Hello, I have gone to work for a new company and I have seen this company is running out of drive space on a daily basis. The DBA before me created huge databases. For example we have a database that it's created size is 103 GB. Only about 52 GB is ever needed. I know you can resize the tempdb but how about user databases? I have never tried this. Can this be done for a user database?

For tempdb
Start SQL from cmd line sqlserver -c -f -s%instance_name%
alter databse tempdb modify file

(name='tempdev',size=150)

View 3 Replies View Related

Can We Modify System Tables?

Sep 28, 2007



Hi all,

I am new to SQL Server databases.Here i have a question that can we modify system tables in SQL Server 2000?

Any help would be helpful to me.....:-)

Thanks,
Narsi

View 6 Replies View Related

Unable To Modify Or Create New Tables...

May 22, 2006

Getting this message on my event log:



.NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Failed to compile: Microsoft.ReportingServices.QueryDesigners, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002

View 1 Replies View Related

How To Change The Database Context And Modify Tables?

Oct 4, 2007

I have 30+ databases in sql 2000.
I want to dynalically loop though each of the databases and alter a column named 'EmpName' for all the 'Employees' tables in that database.
Here is my attempt.use Master
go
declare @SQLString Nvarchar(1000)
declare @DBName Nvarchar(100)
declare @SQL2 NVARCHAR(1000)declare @TABLE_NAME NVarchar(100)
DECLARE curDB CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT name FROM sysdatabases
OPEN curDB
FETCH NEXT FROM curDB INTO @dbname
While @@FETCH_STATUS=0
Begin
      SET @SQL2 = 'USE ' + @DBNAME + ' ALTER TABLE ' + @TABLE_NAME + ' MODIFY EmpName varchar(100)'
     from information_schema.tables where table_type = 'BASE TABLE' and table_name like '%Employees%'      PRINT @SQL2
      EXECUTE SP_EXECUTESQL @SQL2
fetch next from curDB into @DBName
end
Close curDB
End
When i execute the above script in master, it says 'Query executed successfully' but when i see the all the Employees table in each of the database, the required change is not made.
Can anybody explain this or give an alternate solution? 
 
 

View 2 Replies View Related

How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?

May 22, 2008



Hi,
Please give the T-SQL script for this ? Thanks

Shanth


View 4 Replies View Related

Joining Two Tables To Modify A Tree Structure With More Information

Jul 30, 2007

I’m trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry for the tabs that won't work)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different


ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
1506 3823136510667
1507 3824136510667
1509 3825136410667
1509 3826136410667

I have tried many ways, but I’m not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable


Thanks :)

View 1 Replies View Related

Error In Microsoft Sql Server Management Studio Trying To Modify Tables Or Columns

May 21, 2008

I get the error below when trying to modify a table or colum with MSSMS.  I can expad out the tree and look at the columns, but I can't change them, view the data in them, or create new ones.
__________________________________________________________________________________________________________________________________
TITLE: Microsoft SQL Server Management Studio
------------------------------
Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)
 I resintallelled .NET Framework, recommended from another post.  I am using .NET 3.5 BETA Frame work, ans SQL Server 2005 V 9.00.139.00

View 2 Replies View Related

Alter Tables

Apr 20, 2006

Dear friends

I would like to alter columns so that I have have empty string records instead of null. How do I query all tables and all columns so I can alter the column properties programmatically?

Thanks

Cm

View 19 Replies View Related

SQL Server 2008 :: Allow User Only Alter Stored Procedure

Aug 10, 2015

I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.

I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.

View 1 Replies View Related

Alter All Tables Script

May 18, 2006

So I needed to add 2 columns to about 20 tables. Not much and I could have easily wrote a script for each table. But thats boring. So I started searching around.

I found up with this from this post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65351

Select 'Alter table '+table_name+' Add yourcol datatype' from information_schema.tables
where table_name<>'dtProperties'



Cool but I wanted it to run and execute at once, not just create the scripts. The only way I could think was is with a cursor. Something I didn't want to do. But did anyway. I came up with this.

DECLARE Alter_tables_cursor CURSOR
FOR
select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
PRINT 'Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime'
EXEC('Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime')
FETCH NEXT FROM Alter_tables_cursor INTO @tablename

END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor

I'm sure there are better ways. If so post them.

http://www.jiltedcitizen.com

View 5 Replies View Related

SQL Security :: How To Find Who Alter Database User Access Changes In Server

Oct 6, 2015

someone was alter user  databases owner access.

how to find  who alter evoke  the database user access changes in sql server.

View 5 Replies View Related

Need To Alter Data Type On All Tables..

Jul 17, 2003

I need to alter datatypes from
numeric(18,0) to 'int' type across all the tables in the database of about 200 tables. Nearly 200 PK/FKs will be affected with this change..How can I do this with a script? Can anyone help witha script which drops all the constraints/indexs etc and then alters the datatype before re-creating the constraints/indexs..?

P.s. No rounding problem is expected as data is already in whole numbers.

Thanks,
Di.

View 1 Replies View Related

Alter Index For All Tables/indexes In A DB

Oct 22, 2007



In SQL Server 2000 one could DBReindex every index that exists in a given database. You can do the same in SQL Server 2005. But how can this be done with the new Alter Index command? It does not allow me to pass in a variable for the object. Any ideas on how to get this done in with Alter Index in 2005? Thanks!

This I can't get to work:

DECLARE
@TableName nvarchar(100)
SET @TableName = 'Account'

USE database;
GO
ALTER INDEX ALL ON @TableName
REBUILD
GO




USE RZTQ5OL02

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT Name from sys.sysobjects where type = 'U'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC DBREINDEX(@TableName,' ',90)



FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

View 3 Replies View Related

Hi Greg , Any Solution For Alter Tables On Publisher And Subscriber End As Well ....?

Dec 11, 2006

Hi Greg Y and seniors ones,

I am working with replication on sql server 2005 (standard edition sp1).There is scenario that some time one of the team of coders want to alter objects mostly tables being replicated on publication database but unable to do that due to error on adding column "Cannot add columns to table 'table1' because it is being published for merge replication.." in sql server 2000.

While other one want to alter replicated objects on subscriber end (like name of objects, add columns in replicated table etc).

We was working on sql server 2000 and for implementing this scenario I always use mechanism disabling/reconfigure the replication setup by the mean of long exercise.

After that, In order to alter the objects in publication database simple DDL script was executed after disabling the replication.

While manipulating the requirements on subscriber end, I created tables with same structure as replicated tables and replicate the data on self created tables by customizing code in triggers (ins_C9D57350-605A-4D87-85C0-0DB645F1CEC8 etc.) of replicated tables.

Also I have script of all replicated tables€˜s triggers but when I rerun snapshot agent it replaces the name of triggers with new one so at that time I lost my mind and I put code again in all tables€™ triggers. IS there any way to force sql server 2000/2005 generate and rerun snapshot but use already generated guid of articles instead created new one .





Let me know Plz, is there any solution/feature or any easy way in sql server 2005 to avoid this annoy exercised. I could implement this scenario.

View 6 Replies View Related

SQL 2012 :: How To Grant A New User Same Direct Privilege To Tables As Existing User

Feb 18, 2014

I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.

View 2 Replies View Related

How To Create Multiple Tables On The Fly So That Every User Each Has His/her Own Set Of Tables?

Aug 12, 2006

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 6 Replies View Related

Alter Table Alter Column In MSACCESS. How Can I Do It For A Decimal Field?

Jul 23, 2005

Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Granting Permission To A Database User To Alter Database Role

Sep 5, 2006

I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??

View 1 Replies View Related

Is There A Tool (or Script) That Will Compare 2 Tables And Create A Script To Alter The First To Match The Second?

Dec 30, 2004

Does that make sense? I have 2 tables, one is an older version, one newer. The newer version has some additional fields, indexes, etc. I'd love to have a tool to run against them that would generate an ALTER TABLE script to modify the old table so that it matches the new table.

Is there anything like that out there?

View 4 Replies View Related

Alter Table Alter Column

Jul 20, 2005

I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO

View 2 Replies View Related

Alter Table Alter Column...

Oct 8, 2007

I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?



my code:
Alter table customers alter column age

View 7 Replies View Related

List User Tables

Apr 14, 2001

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 1 Replies View Related

How To Copy All The Tables Into The Same Db With A Different User Name?

Nov 2, 2001

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 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved