How To Avoid User Delete Column

Mar 30, 2006

Dear all

I would like to ask one sql question , it's that possible to use statement delete my columns ?

select * from tablename where columna = '--delete from tablename '

View 6 Replies


ADVERTISEMENT

How To Avoid Using Scalar User Defined Functions

Sep 26, 2015

Following the horrendous performance of Scalar UDFs demonstrated in this thread URL....(Scalar UDF 10x slower than writing the code, long hand, in the SELECT) I now have the worry that their widespread usage in my code needs to be refactored. I guess pretty much any single-line non-table-valued scalar-function would do

CREATE FUNCTION dbo.FNSafeString
(
@strValuevarchar(8000),-- STRING Value to convert to SAFE string
@intModeint = 0-- 0=NULL as '[NULL]', 1=NULL as ''
)
RETURNS varchar(8000)
WITH SCHEMABINDING
AS
BEGIN
RETURN COALESCE(@strValue,
CASE WHEN @intMode = 0
THEN '[NULL]'
ELSE ''
END
)
END

Its a little bit too big to really want to have to write it in each place where I need to use it, and its just big enough that it is worth centralising, and it might just be that I could code it more efficiently / different / handle more "edge cases" in the future.I do have even more straightforward UDFs - just to avoid typing something "Lengthy"

CREATE FUNCTION dbo.FNDateMidnight
(
@dtDateTimedatetime-- Date/Time Value to adjust
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
RETURNDATEADD(Day, DATEDIFF(Day, 0, @dtDateTime)+1, 0)
END

Perhaps I should consider changing them to inline table valued functions, or CROSS APPLY them? but I can't think of a way to centralised them and re-use the code WITHOUT a function, can I?

View 9 Replies View Related

How To Avoid The Need Of Adding User In Administrator Group.

Oct 5, 2006

Hi,

I've created an rdl report in reporting services 2005. Report is working fine. I've deployed this report on SQL Server 2005. The problem is this that to access the reports from client, I need to add client's login ID in Administrator user's group os server. If I dont add them in that group, it shows following error:

"The permissions granted to user 'loginname' are insufficient for performing this operation. (rsAccessDenied)".

This solution works fine in development but in actual environment, I can't add users in that group. Can anyone tell me how to view reports without adding user in administrator group. Its urgent.

Looking forward for help.

View 3 Replies View Related

How Can We Avoid Somebody To Access The MDF Data By Doing User Instance Connection?

Dec 11, 2006

I created a database that will be distributed to my customers. This database is running on an Instance of SQL Server 2005 Express edition. I removed the admin logins from my SQL Server Instance so in theory, only my application connecting itself using the Sql Server autenthication will be able to be access the data (using "sa" having a password that I set at the installation).

For now, all this is working fine and after some tests, I haven't been able to access the data in any ways except by using the "sa" and the password my app is the only one to know.

But the problem is coming from a security leak when using User Instance. Indeed, I've been able to create a program getting the content from my MDF file. If somebody try to get connected using User Instance on his own SQL Server instance, he will be able to reach the data.

How could I prevent this to happend? Is there a property or something that could be set into the database that would prevent the database (mdf file) to be used with User Instance?

Thanks!

View 5 Replies View Related

Change Connected User To Avoid Owner Prefix In Queries

Mar 16, 2007

Hi everybody,I've five instances of SQL Server 2000 with the SAME database with aDIFFERENT owner in each server. I, as the administrator, have a lot ofqueries that I have to execute in some or all servers. The problem isthat I have to connect to all servers with MY user, not each of the dbowners...So I have queries this way:select * from mike.table1 t1 join mike.table2 t2 on...And when I connect to another server I have to change mike for jeremyin all the SQLs...And when I connect to another server I have to change jeremy for ninain all the SQLs...I know that there was an old, v7, deprecated way to change the"schema", something likechange current user to kimberlygoselect * from table1 t1 join table2 t2 on...This way, I'll change ONLY once the connected user. I could even do atthe beginning of the script an IF, to change the connected userdepending on @@SERVERNAME !!!Can someone remember this instruction???Thanks in advance for your help !!!

View 1 Replies View Related

[JavaScript Error] Cannot Delete User Or User's Authority In Specific Report After Install SQL SP2

Jan 23, 2007

 Hi,

I have several reports for users to view on our Intranet. After installation of SQL 2005 SP2 patch, I cannot delete user or user's authority from Report in Properties Tab. An error message was shown on the status bar. It indicated that JavaScript Error: 'Return' statement outside of function. Seems something wrong with the 'Delete' funciton in SQL 2005 after update. The other functions worked fine. Could you point me out how to fix it or need to install any updates / hotfix. Thanks a lot!

Regards,

Kenneth Lai
Programmer


Error Pic


Message Box

 

View 1 Replies View Related

Searching A Column For A Value To Avoid Inserting A Duplicate Value

Jul 17, 2007

Hi there, newbie here.
I'm building a web application that allows for tagging of items, using ASP.NET 2.0, C# and SQL Server.
I have my USERS, ITEMS and TAGS separated out into three tables, with an intersection table to connect them.
Imagine a user has found an item they are interested in and is about to tag it. They type their tag into a textbox and hit Enter.
Here's what I want to do:
I want to search the TagText column in my TAGS table, to see if the chosen tag is already in the table. If it is, the existing entry will be used in the new relationship the user is creating. Thus I avoid inserting a duplicate value in this column and save space. If the value is not already in the column, a new entry will be created.
Here's where I'm up to:
I can type a tag into a textbox and then feed it to a query, which returns any matches to a GridView control.
Now I'm stuck... I imagine I have to use "if else" scenario, but I'm unsure of the code I'll have to use. I also think that maybe ADO.NET could help me here, but I have not yet delved into this. Can anyone give me a few pointers to help me along?
 Cheers!

View 3 Replies View Related

Reusing A Generated Column To Avoid Over Processing

Oct 22, 2007

Hi,I'm constructing a query that will performs a lot o datetimecalculumns to generate columns.All that operations are dependent of a base calculum that is performedon the query and its result is stored in a columna returned.I wanna find a way of reusing this generated column, to avoidreprocessing that calculumn to perform the other operations, causethat query will be used in a critical application, and all saving isfew.Thanks a lot.

View 2 Replies View Related

Reporting Services :: How To Avoid Value Overlap On Column Bar Charts

Sep 8, 2015

I have a column bar chart which displays values for each month. As per the requirement, I am displaying the column values by selecting "Show labels" options. I see few values overlap on the column bars.

View 2 Replies View Related

Possible To Avoid The FILLING Of GAP (created By Deleting A Record) In The Identity Column ?

Jun 18, 2007

Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy

View 2 Replies View Related

DB Design :: How To Avoid Similar Entries In Column List In Server

Sep 16, 2015

I am sharing one sql query and o/p:

select distinct  case 
          when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
          when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
          when LastExecutionResult = '17023' then 'User cancelled installation'
          when LastExecutionResult = '17302' then 'Application failed due to low disk space.'

[Code] .....

The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)

Comment  Machine Name
Application will be Installed once machine is Online 4
Application will be Installed once machine is Online 12
Application will be Installed once machine is Online 42
Application will be Installed once machine is Online 120
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60

I need o/p like below:in single line

Application will be Installed once machine is Online 178
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60

View 2 Replies View Related

Cannot Add Windows User, Cannot Delete Sql User

Jul 23, 2005

We were using sql authentication on our sql server 2000 servers, Butneed to use windows authentication. I have an sql user jdoe that ownsmany different objects in multiple databases. I have tried adding thewindows login of jdoe with the exact same permissions as the sql userjdoe, but it complains because it says jdoe already exists. So Iattempt to delete the sql user of jdoe (remembering the permissions soI can reapply them to the windows user soon to be created) But it says"you cannot drop the selected login id because that login id ownsobjects in one or more databases" So now I am trying to figure out howto change the ownership of those many many objects in the manydatabases from jdoe to dbo so I can delete this sql user of jdoe. Doesanyone know of a way to convert an sql login to a windows login? Or ifthat can't be done, how to change the owner of many objects to dbo so Ican delete this user? Maybe a T-SQL script or stored procedure. Thereis a stored procedure called sp_changeobjectowner but that only worksfor one object, how would I do that on all the objects that jdoe owns?As you can see I am a newbie but I appreciate any help you canprovide!!! Thanks in advance!

View 3 Replies View Related

How To Delete Data When Column Have Depency With Other Column?

Dec 2, 2007

in Table A Column is 
PriKey             No       Name
1                      1        Apple
2                      2       Orange
3                      3        Juicy
in Table B column is
Prikey             ColumnA           Price
1                         1                     10
2                          3                     2
3                          2                     5
 
TableA.Prikey have Depency with TableB.ColumnA    
 
when I am trying to Delete data from Table A , I got error message becaue the depency
how to delete data when there have depency?
 
I just know when table have trigger , we can disable trigger before delete Data, and enable trigger when data deleted
does there have a way to disable depency and then enable ?
 
thank you

View 14 Replies View Related

Can't Delete A User!!

Sep 4, 2001

I am trying to recreate a user, using an NT account that changed.

First I got rid of the user using sp_dropuser, and this seemed to work. The user has certainly gone from the sysuser table. However when I try and create a new user it throws an error, telling me that the user already exists.

Any ideas?

View 2 Replies View Related

Delete Everything About A User

May 23, 2007

hey, i'm trying to delete everything record that is connected to a user i want to delete..however, i've been getting error due to the fact that the username also serves as foreign keys..is there anyway i can solve this?

View 3 Replies View Related

Error When Trying To Delete User

Oct 27, 2006

I created many test users in my application while trying to get a custom registration process to work, and now I would like to delete those users so they don't use up space in my database. When I go into the aspnet_Users table and try to delete a user, it gives me the following error:-------------------------------- DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__aspnet_Me__UserI__0F975522'. The conflict occurred in database 'DB_83996', table 'aspnet_Membership', column 'UserId'.DELETE FROM [DB_83996].[dbo].[aspnet_Users] WHERE 1=0 OR ( [UserId] = '{7E864A07-6FE8-46EF-A2B8-354646D57C76}' )-------------------------------- I don't know much about SQL statements, so is that statement it gave me the statement I need to use to delete that particular user? If not, how would I go about deleting these users I don't need anymore?Thanks so much. :) 

View 2 Replies View Related

SQL 2012 :: How To Delete User

Nov 3, 2014

How can I delete user who own a schema in the database? The user is created by myself but I want this to be deleted.

View 2 Replies View Related

Delete Duplicate User

Apr 30, 2008

Hello I am import a file into a SSIS package. Is there a easy way to delete duplicate user in the file. Could i use a sql task or something.

thanks

View 1 Replies View Related

Delete User From Sys.server_principals

Mar 12, 2007

how can we drop an user from sys.server_principals . I did once before , but i forgot the sqls now

thanks

View 1 Replies View Related

Unable To Delete User

Aug 27, 2007

When trying to delete a user on SQL server 2000, we recieve the message "You cannot drop the selected login ID because that login ID owns objects in one or more databases."

We've tried a number of operations to find the objects owned by the user by searching for this error message in Google but, none of those suggestions work.

My questions are; Why does this happen, How can we avoid it in the future?

Our software applicaiton is case sensistive, and if a SQL security login is not created that matches the Domain login to the method used in the application - somehow we are suck. We can't create a new security for the login, nor can we delete or use the existing one.

View 1 Replies View Related

Delete All Sql Database User Objects

Jan 3, 2007

Good afternoon,

I have a little trouble with sql server 2005 express database:

customer need install new web application to hosting, but at hosting is currently exist other web application and it's using DB what I must use. DB contains a big number of tables, views, functions, etc.

I need delete all user objects from this DB, it must be as new created one.

Is any query whitch can do this?

PS: I know, best way for this is delete DB and create new one, but i haven't permissions for these.

Thank's for reply.

View 3 Replies View Related

Delete Database User W/o Login Name

Dec 6, 2006

I have a database user (Maximum). Its default schema is db_datareader. It was created without a login name. When I try and delete this user, i get the following error message:

Drop failed for User 'maximum'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

How do I remove this user?

View 4 Replies View Related

Sa Is Unable To Delete User(urgent Pl. Help)

Dec 10, 2007

Please help, it's urgent!!
We are using sql server 2000 DB,
Sa is trying to delete a user from the Great Plains application and got the follow error:
Deleting the login failed for an unknown reason, contact your sql server administrator for assistance.

View 4 Replies View Related

[*-)]how To Delete All Columns Except One Column For The Corresponding Column Name......?

Jul 15, 2007

hi friends,          i've a table with (columns) username, content,data,........... i need to delete all column names(i.e.,content,data,........) except username for the specified username. eg: consider username=mahendran. i've to delete the values in the content,data,...............for the username=mahendran. but username should exist.how to do that?pls help me...... 

View 3 Replies View Related

User BUILTINAdministrators Was Delete. I Can't Use SQL Enterprise Manager. Help Me!

Aug 18, 2003

My os:
Window2000 + SQL Server 2000 Enterprise

User BUILTINAdministrators was delete. I can't use SQL Enterprise Manager.
Thank you.

View 2 Replies View Related

SQL Server 2012 :: Keep Only Top 50 Records For Every User And Delete Remaining

Aug 4, 2015

I have a table 'library' with two columns

user_name, access_time

every user is having more than 500 entries, i want to keep only top 50 records for every user and delete the remaining records.

View 9 Replies View Related

SQL Server Express 2005 - How To Delete/drop A User Instance

May 23, 2008

Hi,

I have been working around with SQL Server Express 2005 on VISTA. I created a small C++2008 application which interface with SSE 2005. When running my application, it automatically created a user instance which I would like to get rid of. I just forgot from start to set "User Instance = False" in my connectionstring. (oups...)

I'm aware, from readings I did, that a couple of tables as well as log files, etc. are created under directory "utilisateurs...AppDataLocalMicrosoftMicrosoft SQL Server DataSQLEXPRESS". SQLEXPRESS is the instance name chosen during install of SSE2005.

Also using following SQL Statement:

select owning_principal_name, instance_pipe_name, heart_beat
from sys.dm_os_child_instances
I got following result:
owning_principal_name: PC-DE-STEPHANEStéphane
instance_pipe_name: \.pipe6172F4E8-622E-4A sqlquery
heart_beat: dead (at this moment...)

==> How can I make a good cleanup of all this as well as get rid of the user instance itself?


Thanks in advance for any help.

Stéphane

View 7 Replies View Related

Create User Only With Permissions, To Select, Insert, Update, Delete, And Exec Sps

May 18, 2006

Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.

Is any easy way to do this?

A database role and then assing that role to the user?

View 4 Replies View Related

Delete/Remove Column

Oct 18, 2000

Hello people

Can someone tell me if, and then how I can delete/remove a column from a table in SQL6.5. (I know how to add a column)


Regards
De Waal

View 2 Replies View Related

Delete Partial From Column

Jan 30, 2006

Guys, i have a table that one of the columns (Email To) is
a concatenated list of email addresses separated by semi colons ";".

i.e.:

rrb7@yahoo.com;richard.butcher@sthou.com;administr ator@sthou.com

etc like that.
each row varies with one exception. administrator@sthou.com is in each one.

is there a simple way thru sql or T-SQL to delete that "administrator@sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
and then looping thru and updating each row?

thanks again for any easy answer
rik

View 7 Replies View Related

Delete A Column Value With Respect To Other

Jul 5, 2007

Hi
I have to delete the value of acktitle where ackby = null
I tried using update but its not helping me i tried doing

update incidents(table name)
set acktitle = null where ackby = null

its not giving me any results thats why i thought of delecting but delete syntax removes the whole row
How should i go about it

View 2 Replies View Related

How To Delete A Column Using Script Code

Nov 2, 2006

hi all iam having a  query , c guys iam having a table in sql with some coulmns in it , i have a column named as country in the table , now wat i want to be done is , i want to delete the column country based on some conditions , i ve to write a script code as : i ve to check if the column is there already or not if its there it shld delete the column or if its not there it shld not show any error and just return empty handed thats all , i dont know wat to be done , so if anyone knows abt it pls do send as soon as possible guys , hopefully waiting for a reply Note : any dbts pls do mail me again  Venkatesan 

View 4 Replies View Related

Delete Output Into &<xml Column Of Audit Table&>

Mar 31, 2008

Currently running Sql Server 2005Is it possible to issue the delete command and capture the affected rows asxml types that will be stored in an audit table with an xml column?Something along the lines of:delete from source_tableoutput(deleted.*into audit_table (xml_audit_column)for xml auto)where source_table.column = @delete_value

View 1 Replies View Related







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