Delete Duplicate Record

I have Changed my database schema and transform Data with new keys using
this Query :
SELECT Case_No, Case_Date, Ma7akem_ID, COUNT(*) AS Expr1
FROM Master_Ahkam
GROUP BY Case_No, Case_Date, Ma7akem_ID
HAVING (COUNT(*) > 1)
Due to a Design error in the old one there was a "ModifyDate" in the
primarykey. so the whole record are the same except the Modifydate.. I need
to delete these rows..
Is there anyway to do so'Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. If you had a proper relational
design in the first place, you could not have dups in the first place.
Can you kill the guy that did this? That will improve the overall
quality of your software.|||Hiiiiiiii
The one who designed this is my boss so if I Kill him I 'll Have to Find new
Job, Can you help -;)
Here is the DDL
----
--
CREATE TABLE [AH_Tasneef] (
[ID] [PKInt] NOT NULL ,
[Parent_ID] [PKInt] NOT NULL CONSTRAINT [DF__Tasneef__Parent___0519C6AF]
DEFAULT (0),
[Ma7kama_ID] [PKInt] NOT NULL ,
[Text] [varchar] (200) COLLATE Arabic_BIN NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_Tasneef] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_AH_Tasneef_AH_Ma7akem] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_TasnFakaraat] (
[Tasneef_ID] [PKInt] NOT NULL ,
[Master_ID] [PKInt] NOT NULL ,
[Fakra_No] [int] NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_TasnFakaraat] PRIMARY KEY NONCLUSTERED
(
[Tasneef_ID],
[Master_ID],
[Fakra_No]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_SubMaster_AH_Master] FOREIGN KEY
(
[Master_ID]
) REFERENCES [AH_Master] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_SUBMA foreign key (Master_ID,
Fakra_No)
references dbo.AH_SubMaster (Master_ID, Fakra_No)
go
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_TASNE foreign key (Tasneef_ID)
references dbo.AH_Tasneef (ID)
go
----
--
I have duplicate records in table AH_TasnFakaraat which i need to clean...
how could i do so'
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133664266.448284.115290@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. If you had a proper relational
> design in the first place, you could not have dups in the first place.
> Can you kill the guy that did this? That will improve the overall
> quality of your software.
>|||On Sun, 4 Dec 2005 11:38:16 +0200, Islamegy wrote:

>Hiiiiiiii
>The one who designed this is my boss so if I Kill him I 'll Have to Find ne
w
>Job, Can you help -;)
Hi Islamegy,
I can't help you killing your boss or finding you a new job, but I might
be able to help with the duplicates.
I'm not sure exactly which columns are duplicated and which are not, nor
how to decide which rows to retain and which to remove, so I'll post a
generic example for you to modify.
CREATE TABLE Test
(Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
CONSTRAINT TestKey PRIMARY KEY (Col1, Col2, Col3)
)
INSERT INTO Test (Col1, Col2, Col3)
SELECT 1, 2, 3
UNION ALL
SELECT 1, 2, 4
go
The primary key should have been (Col1, Col2). This means that the data
above is incorrect. The lowest value for Col3 should be retained, all
others removed. In the end, only the (1, 2, 3) row should survive.
-- Remove unwanted data
DELETE FROM Test
WHERE EXISTS
(SELECT *
FROM Test AS t2
WHERE t2.Col1 = Test.Col1
AND t2.Col2 = Test.Col2
AND t2.Col3 < Test.Col3)
-- Change key
ALTER TABLE Test
DROP CONSTRAINT TestKey
ALTER TABLE Test
ADD CONSTRAINT TestKey PRIMARY KEY (Col1, Col2)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Delete Database User w/o Login name

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?

It seems like your user owns some schema, most likely the default schema that gets created for the user. You will need to use the "DROP SCHEMA" statement to first drop the user's schema (if it is the default schema, the schema's name will most likely be the same as the user's name). Otherwise you can use "ALTER AUTHORIZATION" to change to the owner of the schema to someone else if you want to keep it. After that you should be able to drop the user.

Thanks,

Sung

|||

You can use the following query to help you identify the schema you need to drop.

select schm.name as 'schema_name'

FROM sys.schemas schm, sys.database_principals usrs

WHERE schm.principal_id = usrs.principal_id AND usrs.name = 'dbo'

-Raul Garcia

SDE/T

SQL Server Engine

Default Schema within storded procedure

I'm migrating a dotnetnuke website from SQL Server 2000 to SQL Server 2005 and have run into a problem with one of the stored procedures.

The database objects seem to have upgraded successfully to use the db schema identifer from the dbowner identifier. However I am having a problem with a particular stored procedure trying to execute another stored procedure.

When the following procedure is called, it seems that the db engine has forgotten the schema context and therefore can't find the called procedure. Has anyone come across this before and is there a workaround other than modifing every SP that uses EXEC?

ALTER PROCEDURE [myschema].[dnn_Forum_StatisticsGet]

(

@.ModuleID int,

@.UpdateWindow int = 12,

@.TabId int

)

...

BEGIN

EXEC dnn_Forum_AA_StatisticsSiteUpdate 0, 0, @.ModuleID, @.TabId

END

...

What about full qualifying the procedure call or using the EXECUTE AS OWNER predicate ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

What about full qualifying the procedure call or using the EXECUTE AS OWNER predicate ?

Thanks for responding Jens. I've considered this approach but it is a last option. It would require modification of a number of procedures and I can't predict where the problem might occur. Ideally I need a solution where the behaviour of the EXEC statement defaults to what you have suggested.

Rgds

Colm

|||

Yep, this is the same question as someone asked me during the last session about Best Practices and Performance Tuning. I would not use implicit naming or schemas, do everything explicitly. Although this is just a bit more work and testing to do for you, you will be later on the safe side.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

The problem is that we are including components from 3rd parties that are expecting the default behaviour of the EXEC statement to default the schema correctly according to the invoking user. From what I can see this is not happening in SQL2005. In SQL 2000 the default was the dbo. Is this a bug in SQL 2005 or normal behaviour?

Rgds

Colm

Default Schema on SQL Server 2005 with Server Role "sysadmin"

Hi,
I have a user with a default database and a default schema that is not
"dbo". I noticed that, if I assign this user the server role "sysadmin"
the default schema does not seem to be honored any more. Instead
whenever I use an unqualified table name "dbo" is searched first. When
I remove the role, the user's default schema is searched as first
schema. Is there any way to make SQL 2005 honor the default schema when
assigning role "sysadmin" to a user? I could not find anything in the
docs about this.
Kind regards
robertRobert,
Sysadmins always map to dbo in a database. Being a sysadmin trumps any
other settings for the user.
RLF
"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:55svpoF24qv1vU1@.mid.individual.net...
> Hi,
> I have a user with a default database and a default schema that is not
> "dbo". I noticed that, if I assign this user the server role "sysadmin"
> the default schema does not seem to be honored any more. Instead whenever
> I use an unqualified table name "dbo" is searched first. When I remove
> the role, the user's default schema is searched as first schema. Is there
> any way to make SQL 2005 honor the default schema when assigning role
> "sysadmin" to a user? I could not find anything in the docs about this.
> Kind regards
> robert|||On 15.03.2007 14:53, Russell Fields wrote:
> Sysadmins always map to dbo in a database. Being a sysadmin trumps any
> other settings for the user.
Ah! Thanks a lot!
Kind regards
robert

Default Schema on SQL Server 2005 with Server Role "sysadmin"

Hi,
I have a user with a default database and a default schema that is not
"dbo". I noticed that, if I assign this user the server role "sysadmin"
the default schema does not seem to be honored any more. Instead
whenever I use an unqualified table name "dbo" is searched first. When
I remove the role, the user's default schema is searched as first
schema. Is there any way to make SQL 2005 honor the default schema when
assigning role "sysadmin" to a user? I could not find anything in the
docs about this.
Kind regards
robertRobert,
Sysadmins always map to dbo in a database. Being a sysadmin trumps any
other settings for the user.
RLF
"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:55svpoF24qv1vU1@.mid.individual.net...
> Hi,
> I have a user with a default database and a default schema that is not
> "dbo". I noticed that, if I assign this user the server role "sysadmin"
> the default schema does not seem to be honored any more. Instead whenever
> I use an unqualified table name "dbo" is searched first. When I remove
> the role, the user's default schema is searched as first schema. Is there
> any way to make SQL 2005 honor the default schema when assigning role
> "sysadmin" to a user? I could not find anything in the docs about this.
> Kind regards
> robert|||On 15.03.2007 14:53, Russell Fields wrote:
> Sysadmins always map to dbo in a database. Being a sysadmin trumps any
> other settings for the user.
Ah! Thanks a lot!
Kind regards
robert

default schema not working?

I am confused.

I added my NT account to the sql server logins for my sql server (2005), then I added a corresponding user account to my database. I then set my default schema. I connect to the database, and the default schema seems to be set to dbo.

Can anyone thing of a reason why this might be happening? Is there some sort of override if I have additional privledges on the server?

I appreciate any thoughts...

-Mike Graham

AH HA !!!

In the BOL, it says "you cannot change the default schema for a user that is mapped a windows group" - my account was in the administrators group which had been added to the logins for the sql server.

I remove the group and it started working without even closing the query window.

YES !!!!!!!!!!!!

|||

Ok - little more info:

I also noticed that if i am in the server role: sysadmin, the default schema assignment doesn't work, but if I remove myself, then it works.

|||

If you are a sysadmin, you are also a member of db_owner, so your default schema will be dbo.

The BOL quote refers to the fact that you cannot set a default schema for a database principal that is mapped to a Windows group, NOT that you cannot set it for a database principal who is mapped to a Windows account that belongs to some group.

So, what prevented the default_schema setting from working was the fact that you were a sysadmin.

Thanks
Laurentiu

default schema for NT-group not possible

We have a new development SQL2005 server.
To implement secutiry on this new server we decided to grant rights to NT-groups.
We defined an NT-group Programmersmembers and gave this group the necessary rights.
The problem now is when a member of this NT-group creates a table (this group is dbo in the development database) everything works fine as expected.
But when a member of this group tries to make a new table through 'Microsoft SQL server management studio' they receive the error that there is no default schema available.
But it seems to be impossible to map a default schema to an NT-Group.

=> Does this means we have no other way than to give each individual programmer the necessary rights and assign them to a default schema.
It is no option to force our programmers to create each table through a script on the development database.

Or is there another option ?

For the general issue of setting a schema for a group, see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1.

If you have a limited number of operations that you want your users to perform, you could expose them through stored procedures that use the EXECUTE AS to run under a context with a well defined default schema. See http://msdn2.microsoft.com/en-us/library/ms188354.aspx for more information on EXECUTE AS.

Thanks
Laurentiu

|||

Setting a default schema for a group is not supported in SQL Server. The root cause of the problem in this case seems to be a bug in Microsoft SQL Server management studio that is already in our radar.

For more details, you can see the original discussion for this bug at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=79418&SiteID=1 .

I hope this information can help you.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Just to clarify the SQL Server Management Studio bug. If you create the table directly using TSQL, the operation should succeed and create an implicit user. Management Studio's bug is that it requires the user to be already created.

-Raul Garcia

SDE/T

SQL Server Engine

-

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Raul & Laurentiu

thx for the explination and the link to the other thread

Default Schema and Synonyms

database0.User1 has a default schema of database0.Schema1 and also belongs to database1.

I have the following synonyms in database0:

Schema1.Customers - points to database1.dbo.customers

dbo.Customers - point to database2.dbo.customers

User1 has execute rights in database0. I have a stored procedure in database0 that says

select * from Customers

I thought since User1's default schema was Schema1, it would automatically see select * from customers as select * from database1.dbo.customers. It keeps going to the dbo.Customers schema.

Isn't it supposed to go to the default schema first, then try dbo?

What schema is the stored procedure in database0 defined?|||DBO|||

In that case, the behavior you see is expected. sql server always resolves to the container schema first, if there is a container for the object.

In this case I believe the pseudo code for scenario looks like this.

create proc dbo.Proc1 {select from customers}

For the object customers, the container is Proc1. So the resolution for customers goes to the schema of the container procedure Proc1 which is dbo. If you define the procedure Proc1 in the schema Schema1, then the resolution for customers will go to the schema schema1.

hope that helps.

|||

Then we would have to have a separate set of SP's for all schemas. We were hoping to use the synonyms to dynamically point users to the correct database that contains their tables.

If we have to have separate SP's then we might as well store them along with each database. We were trying to have just one set of SP's that could "figure out" which database they needed to be in based on the schema the user belongs to.

Does that make sense?

|||

If a user connects to a database and just runs "select * from customers" then sql server resolves to the default schema of the user. So it will resolve to the synonym in the default schema of the user.

When you wrap that t-sql statement in a SP then the resolution changes to the container schema first.

I am not sure if this information is useful to you in solving this problem but I am posting it just in case.

|||

That won't work. Here is what we are trying to accomplish.

We have one database that will be our "controller" database. It will house all the SP's (all code). There will be multiple customer database that will have identical tables. Basically each customer will have their own database with their own data.

We want to controller database to figure out based on the user which customer database to use for their data. We were hoping the Synonyms linked to a schema would work for us. But we hit a road block with the SP belonging to dbo.

Do you see any solution for us? We would like to have one set of code and differents sets of databases for our customers.

|||

Inside the SP body make a call to the builtin SCHEMA_NAME() to figure out the default schema of the caller. Once you have the default schema of the caller you can use 2 part names to call the correct synonym from inside the procedure.

PSEUDO CODE:

create procedure p_Controller as

declare @.defaultschema sysname

SET @.defaultschema=SCHEMA_NAME()

EXEC ('SELECT * FROM ' + @.defaultschema + '.Customers')

--Calls the synonym in the default schema of the caller

go

Ensure that the right permissions are available on the procedure and the synonyms. Synonyms are separate object types themselves so you need to grant the correct permissions on them for users to be able to use them.

Hope this helps.

|||

I can see how that would work. But our performance will go way down when we go and update all stored procedures SELECT's to run inside EXEC(). We don't want to do that.

|||Unfortunately , I cannot think of any other way to achieve your objective without using dynamic sql.

default schema (SQL Server 2005)

Is there anyway to change the default schema for a user who is a
member of "sysadmin". It appears that Microsoft has made the default
schema for this role "dbo" and our application has been writtem not to
include owner/schema name.A sysadmin works within databases in the dbo context, so it will use the dbo
schema (which has always been the default schema for sysadmins, except that
is was called 'owner' in SQL ).
So, I don't think you have an option on this.
RLF
<dnlgauthier@.hotmail.com> wrote in message
news:1187272016.929096.136070@.22g2000hsm.googlegroups.com...
> Is there anyway to change the default schema for a user who is a
> member of "sysadmin". It appears that Microsoft has made the default
> schema for this role "dbo" and our application has been writtem not to
> include owner/schema name.
>|||And I did try changing the dbo user's default schema to something else, but
SQL Server will prevent that with:
Msg 15150, Level 16, State 1, Line 1
Cannot alter the user 'dbo'.
(And, if it did work, it would be a bad idea.)
RLF
<dnlgauthier@.hotmail.com> wrote in message
news:1187272016.929096.136070@.22g2000hsm.googlegroups.com...
> Is there anyway to change the default schema for a user who is a
> member of "sysadmin". It appears that Microsoft has made the default
> schema for this role "dbo" and our application has been writtem not to
> include owner/schema name.
>|||On Aug 16, 1:26 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> A sysadmin works within databases in the dbo context, so it will use the d
bo
> schema (which has always been the default schema for sysadmins, except tha
t
> is was called 'owner' in SQL ).
> So, I don't think you have an option on this.
> RLF<dnlgauth...@.hotmail.com> wrote in message
> news:1187272016.929096.136070@.22g2000hsm.googlegroups.com...
>
>
> - Show quoted text -
I agree with the statement that a sysadmin works with in teh database
as "dbo" context, so how can I provide user access via an application
using Windows authenticate and make it so that he/she is a user not a
member of sysadmin? I hope this is not confuising ?|||dnl,
So the answer to your first question was "sqladmin" will always be "dbo" in
a database.
The answer to your second question comes in two parts.
1 - General users should NEVER be sysadmins.
2 - Grant them specific rights either as their personal login or by granting
rights to a Windows Group in which they are a member. (I prefer assigning
rights to Windows groups over individual logins.)
E.g.
1. MikeR needs rights to MyDatabase.
2. MikeR is a member of the Windows group Domain\MyDatabaseUsers
3. Add Domain\MyDatabaseUsers as a login on your SQL Server.
4. In MyDatabase create a security role named GeneralUsers
5. Add Domain\MyDatabaseUsers as a user of MyDatabase and a member of
GeneralUsers.
6. All objects in the database that GeneralUsers should need need to have
rights granted to that role.
a. GRANT SELECT ON ViewA TO GeneralUsers, etc.
b. GRANT EXECUTE ON UpdateSomethingProc TO GeneralUsers, etc.
In the future, when MikeR moves on, simple remove him from the Windows
group. When LisaK takes his place, add her to the Windows group. And so on
and so on. Here is a whitepaper on SQL Server 2005 Security Best Practices.
http://download.microsoft.com/downl...c#_Toc160646532
RLF
<dnlgauthier@.hotmail.com> wrote in message
news:1187292090.432045.126570@.19g2000hsx.googlegroups.com...
> On Aug 16, 1:26 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> I agree with the statement that a sysadmin works with in teh database
> as "dbo" context, so how can I provide user access via an application
> using Windows authenticate and make it so that he/she is a user not a
> member of sysadmin? I hope this is not confuising ?
>|||(dnlgauthier@.hotmail.com) writes:
> I agree with the statement that a sysadmin works with in teh database
> as "dbo" context, so how can I provide user access via an application
> using Windows authenticate and make it so that he/she is a user not a
> member of sysadmin? I hope this is not confuising ?
Use EXECUTE AS to impersonate a different login or user.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Default permissions for schema owner

Hi,
Can someone please list down the set of default permissions that the
owner of a schema gets by default ? Atleast the very basic and
important ones.
Thanks
AdyIn SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
"audipen" <audipen@.gmail.com> wrote in message
news:1173160732.973389.165550@.64g2000cwx.googlegroups.com...
> Hi,
> Can someone please list down the set of default permissions that the
> owner of a schema gets by default ? Atleast the very basic and
> important ones.
> Thanks
> Ady
>