cannot edit text cell

I am trying to edit a long text cell in Enterprise Manager but when I select
"<long text>" and try to delete it, I get an error saying "cannot edit this
cell".

Could anyone tell me how to edit this cell?

Thanks, AmandaHi

When your text column has more than 900 characters it will not allow you to
enter it via EM.

You can use Query Analyser (which is usually easier and safer IMO) to issue
an update statement, or for more complicated changes look up UPDATETEXT in
books online.

John

"Amanda H" <amanda2@.southwind.org> wrote in message
news:K58Pd.1784$VI1.215392@.twister.southeast.rr.co m...
>I am trying to edit a long text cell in Enterprise Manager but when I
>select
> "<long text>" and try to delete it, I get an error saying "cannot edit
> this
> cell".
> Could anyone tell me how to edit this cell?
> Thanks, Amanda|||"Amanda H" <amanda2@.southwind.org> wrote in message
news:K58Pd.1784$VI1.215392@.twister.southeast.rr.co m...
>I am trying to edit a long text cell in Enterprise Manager but when I
>select
> "<long text>" and try to delete it, I get an error saying "cannot edit
> this
> cell".
> Could anyone tell me how to edit this cell?
> Thanks, Amanda

Use an UPDATE statement in Query Analyzer is probably the best thing to do -
EM isn't really a data manipulation or development tool, and it has a number
of 'features'.

http://www.aspfaq.com/show.asp?id=2455

Simonsql

Cannot edit jobs

i am get this error message every time I try to remove some old jobs that
were created on a different server:
error 14274: cannot add, update,or delete a job (or its step or
schedules) that originated from an msx server
What do I need to do to remove these jobs?
Either remove them from the master server. Or hack msdb..sysjobs (originating_server column to the
name for your server). Some info found here (adapt to your needs):
http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Takia" <Takia@.discussions.microsoft.com> wrote in message
news:527F2E42-BDF8-4968-B717-4BDA27347FDB@.microsoft.com...
>i am get this error message every time I try to remove some old jobs that
> were created on a different server:
> error 14274: cannot add, update,or delete a job (or its step or
> schedules) that originated from an msx server
> What do I need to do to remove these jobs?
>

Cannot edit jobs

i am get this error message every time I try to remove some old jobs that
were created on a different server:
error 14274: cannot add, update,or delete a job (or its step or
schedules) that originated from an msx server
What do I need to do to remove these jobs?Either remove them from the master server. Or hack msdb..sysjobs (originatin
g_server column to the
name for your server). Some info found here (adapt to your needs):
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Takia" <Takia@.discussions.microsoft.com> wrote in message
news:527F2E42-BDF8-4968-B717-4BDA27347FDB@.microsoft.com...
>i am get this error message every time I try to remove some old jobs that
> were created on a different server:
> error 14274: cannot add, update,or delete a job (or its step or
> schedules) that originated from an msx server
> What do I need to do to remove these jobs?
>

Cannot drop the database 'RMAModule' because it is currently in use

How can I stop getting this error when I try to delete a database
dynamically?
I am using this code...
DROP DATABASE RMAModule
If I just wait a while and try it again it will work - not urgent, but a
hastle. Thanks!!Hi,
Execute the below code from Query Analyzer,
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
drop database dbname
Replace the dbname with the actual dbname which you have to drop.
Thanks
Hari
MCDBA
"michaaal" <res0gyio@.verizon.net> wrote in message
news:#EwrBBlDEHA.2424@.TK2MSFTNGP09.phx.gbl...
> How can I stop getting this error when I try to delete a database
> dynamically?
> I am using this code...
> DROP DATABASE RMAModule
> If I just wait a while and try it again it will work - not urgent, but a
> hastle. Thanks!!
>sql

Cannot Drop Table?

I have a table that is not functioning properly, so I have recreated it under a different name. Now I want to delete the problem table but it wont let me. This table has no dependencies, but I still can't drop it.

Here is the error message:

Lock request Time out period exceeded, error 1222

Any ideas on how to drop this table?

Did you check for locks with sp_who2 or Activity Monitor/Process Info (2005) Current Activity/Process Info (2000)? If there is a lock KILL the SPID(connection).

See:

http://msdn2.microsoft.com/en-gb/library/aa213032(SQL.80).aspx

|||

check whether this table is reference by any other table.... use sp_help/sp_fkeys etc....

Run DBCC CheckTable to check for any corruption.

Madhu

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.
What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com
|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:

> What does "cannot" mean? Do you get an error message? If so, what is it?
>
|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:

> CR wrote:
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:
> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:
> CR wrote:
>> I have a table with no dependencies. I cannot drop the table. I
>> tried from EM to delete it. I tried from Query Analyzer. I cannot
>> even delete an index that it has. It's not a big table and it only
>> has about 6,000 rows. Any ideas where to look? Thanks.
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:

> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:

> CR wrote:
>
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>sql

Cannot Drop Index and PK Constraints

I am trying to create a new column on a table, but realized that there is already an PK index created. I am unable to drop the PK, or even delete the table. I also am unable to delete the index.Please be more specific - what error messages are you receiving ? Is it because of a system error or a constraint error (like you are deleting a primary key which is a foreign key in another table(s)) ?

cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
Bing
For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
WHERE clause (find the rows).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:

> For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>
|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/
|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:

> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>
|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:

> Check to ensure the user's login is correctly mapped to the desired database
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Online
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>
>
|||bing skrev:

> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson
|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.

> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...[vbcol=seagreen]
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
|||"impslayer" wrote:

> bing skrev:
>
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bing

cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
BingFor some strange reason, you need SELECT as well. I guess it is because SQL
Server need to match the
WHERE clause (find the rows).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
I
> looked, he did not have DELETE on that table. Then I checked DELETE for h
is
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:

> For some strange reason, you need SELECT as well. I guess it is because SQ
L Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:

> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:

> Check to ensure the user's login is correctly mapped to the desired databa
se
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Onlin
e
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>
>|||bing skrev:

> Hello,
> One of our users said he was not able to delete records in a table. When
I
> looked, he did not have DELETE on that table. Then I checked DELETE for h
is
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.

> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...[vbcol=seagreen]
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
>|||"impslayer" wrote:

> bing skrev:
>
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bingsql

cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
BingFor some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
WHERE clause (find the rows).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:
> For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When I
> > looked, he did not have DELETE on that table. Then I checked DELETE for his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
>|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:
> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:
> Check to ensure the user's login is correctly mapped to the desired database
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Online
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When
> > I
> > looked, he did not have DELETE on that table. Then I checked DELETE for
> > his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
>
>|||bing skrev:
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.
> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
>> Check to ensure the user's login is correctly mapped to the desired
>> database
>> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
>> mapping is incorrect following a database restore/attach. If that is you
>> problem, you can correct using sp_change_users_login. See the Books
>> Online
>> for usage details.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>> > Hello,
>> >
>> > One of our users said he was not able to delete records in a table.
>> > When
>> > I
>> > looked, he did not have DELETE on that table. Then I checked DELETE
>> > for
>> > his
>> > account. But he still cannot delete any records. I don't think there
>> > is
>> > anything more I need to do to make the permission change take effect.
>> > Correct me if I'm wrong. So what I'm missing here?
>> >
>> > Thanks in advance,
>> >
>> > Bing
>>|||"impslayer" wrote:
> bing skrev:
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When I
> > looked, he did not have DELETE on that table. Then I checked DELETE for his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bing|||bing skrev:
> This user did not tell me the
> exact error message he got if there was any. I need to check back with him
> on that.
Ah, here's the problem, I guess :)
/impslayer

Cannot delete users in SQL 2005?

Hi,

Running multiple SQL 2000 servers and on each can easily go under Enterprise Manager>>ServerXYZ>>Security>>Logins>>Delete and delete user regardless users DB membership etc...now installed SQL 2005 (MS SQL Server Management Studio) and trying to do the same (attached to server running SQL 2000) and I cannot do it. Each time I try it, I am getting:

TITLE: Microsoft SQL Server Management Studio

Drop failed for Login 'testdelete'. (Microsoft.SqlServer.Smo)

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

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Login 'testdelete' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (Microsoft SQL Server, Error: 15175)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=15175&LinkId=20476

BUTTONS:

OK

If I go and manually remove user from all databases and then try to delete it only then I can delete such user...this is not the solution for me as we have some servers where users can be associated up to different 300+ databases.

I would like to move from 2000 and start using 2005 tools exclusively and this is only reason I cannot.

Any fix, suggestion or solution is appreciated. Thanks.

I believe the error is by default as you can see that user is mapped/aliased to other databases, if you are using linked servers then thsi is expected. As a matter of access can you try revoking the permissions for that user and then drop it.|||

Thanks for your reply.

Yes I know what the message is meaning but what I do not understand is how I can just go to any user setup with access to multiple databases on same server (not linked) and select "Delete" using SQL 2000 Enterprise Manager and it drops it w/o any issues but when I go to the same server and user with same setup under SQL 2005 Server Managment Studio it tells me that user is associated to other databases and it cannot delete it w/o me first revoking all the access just then to drop it (that works)...this is lot more complex and requires extra steps?

|||

The issue is not so much that the login has mapped users, but that the mapped users own something in a database. In SQL Server 2005, we made security enhancements that require every object to be owned by some security principal. A user can no longer simply relinquish ownership. Instead, ownership must be positively taken by some other principal. Once all the users mapped to the login are no longer owners of anything, you should be able to drop the login without error.

This sounds like a pain point for you. If you could file a suggestion to make this easier at http://connect.microsoft.com/sqlserver, that would help. The development team gives additional weight to defect reports and feature requests submitted by customers when we are prioritizing future work.

Hope this helps,
Steve

Cannot delete users in SQL 2005?

Hi,

Running multiple SQL 2000 servers and on each can easily go under Enterprise Manager>>ServerXYZ>>Security>>Logins>>Delete and delete user regardless users DB membership etc...now installed SQL 2005 (MS SQL Server Management Studio) and trying to do the same (attached to server running SQL 2000) and I cannot do it. Each time I try it, I am getting:

TITLE: Microsoft SQL Server Management Studio

Drop failed for Login 'testdelete'. (Microsoft.SqlServer.Smo)

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

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Login 'testdelete' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (Microsoft SQL Server, Error: 15175)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=15175&LinkId=20476

BUTTONS:

OK

If I go and manually remove user from all databases and then try to delete it only then I can delete such user...this is not the solution for me as we have some servers where users can be associated up to different 300+ databases.

I would like to move from 2000 and start using 2005 tools exclusively and this is only reason I cannot.

Any fix, suggestion or solution is appreciated. Thanks.

I believe the error is by default as you can see that user is mapped/aliased to other databases, if you are using linked servers then thsi is expected. As a matter of access can you try revoking the permissions for that user and then drop it.|||

Thanks for your reply.

Yes I know what the message is meaning but what I do not understand is how I can just go to any user setup with access to multiple databases on same server (not linked) and select "Delete" using SQL 2000 Enterprise Manager and it drops it w/o any issues but when I go to the same server and user with same setup under SQL 2005 Server Managment Studio it tells me that user is associated to other databases and it cannot delete it w/o me first revoking all the access just then to drop it (that works)...this is lot more complex and requires extra steps?

|||

The issue is not so much that the login has mapped users, but that the mapped users own something in a database. In SQL Server 2005, we made security enhancements that require every object to be owned by some security principal. A user can no longer simply relinquish ownership. Instead, ownership must be positively taken by some other principal. Once all the users mapped to the login are no longer owners of anything, you should be able to drop the login without error.

This sounds like a pain point for you. If you could file a suggestion to make this easier at http://connect.microsoft.com/sqlserver, that would help. The development team gives additional weight to defect reports and feature requests submitted by customers when we are prioritizing future work.

Hope this helps,
Steve

cannot delete user & shrink log file

Hello... I'm facing two problems:
1. When I try to delete a user from a specific database,
it reports that that user cannot be dropped as it owns
objects. The db_owner is 'sa' not this user.
2. When I try to shrink the size of my log file, it
reports, "Cannot shrink log file 2 (cms1_log) because all
logical log files are in use."
Please help. Thanks.1. My guess is that the user own some objects. If you are used to
Enterprise Manager then expand each objects list and see if the user own
anything like "tables", "views", "store procedure", "user defined functions"
...
2. What is your recovery model? Right click server in EM, then click
properties, then options
"Rob" <rhchin@.hotmail.com> wrote in message
news:5d8901c35784$5b35fbf0$a001280a@.phx.gbl...
> Hello... I'm facing two problems:
> 1. When I try to delete a user from a specific database,
> it reports that that user cannot be dropped as it owns
> objects. The db_owner is 'sa' not this user.
> 2. When I try to shrink the size of my log file, it
> reports, "Cannot shrink log file 2 (cms1_log) because all
> logical log files are in use."
> Please help. Thanks.|||Rob,
#1..It could also be that that the user owns other objects like table,
stored procedures etc.Using the below query, you can find out the objects:
SELECT [name]
FROM sysobjects
WHERE uid=USER_ID('<user_name>')
Once there, try to change the ownership to another user, using
sp_changeobjectowner.
#2.Refer
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/support/kb/Articles/q256/6/50.asp
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/support/kb/Articles/q272/3/18.asp
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <rhchin@.hotmail.com> wrote in message
news:5d8901c35784$5b35fbf0$a001280a@.phx.gbl...
> Hello... I'm facing two problems:
> 1. When I try to delete a user from a specific database,
> it reports that that user cannot be dropped as it owns
> objects. The db_owner is 'sa' not this user.
> 2. When I try to shrink the size of my log file, it
> reports, "Cannot shrink log file 2 (cms1_log) because all
> logical log files are in use."
> Please help. Thanks.|||Thanks for your response...
>Once there, try to change the ownership to another user,
using
>sp_changeobjectowner.
I can see many objects owned by the user I wish to drop.
However, when I try to use the sp_changeobjectowner sproc,
it tells me that the object does not exist or is not a
valid object for this operation.
Thanks again.

Cannot Delete user

Here's a catch-22 with the new SQL 2005. I've attached some SQL 2000 db's t
o
the new server. When I went into Mgmt Studio I realized that the old
security settings associated with those db's carried over to the new server
when attached. However, they were no good because the user "abc" did not
exist as a valid login on this new server. I tried to delete it but it won'
t
allow it because there is no "login" associated with the user. Of course
there is none. it's a bogus record. For that same reason the Login screen
is grayed out and Mgmt Studio won't let me change it. I see absolutely no
way to remove it now (short of removing the security settings while it's
still in SQL 2000 before moving to 2005). Any suggestions on a way to
cleanup the security info and get it all redefined after moving to 2005?
Thanks
--Perhaps one of these will offer you a clue:
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Spicie Mikie" <Maz@.newsgroups.nospam> wrote in message
news:7B7F056C-5EB2-4548-BB47-4D58F024860B@.microsoft.com...
> Here's a catch-22 with the new SQL 2005. I've attached some SQL 2000 db's
> to
> the new server. When I went into Mgmt Studio I realized that the old
> security settings associated with those db's carried over to the new
> server
> when attached. However, they were no good because the user "abc" did not
> exist as a valid login on this new server. I tried to delete it but it
> won't
> allow it because there is no "login" associated with the user. Of course
> there is none. it's a bogus record. For that same reason the Login
> screen
> is grayed out and Mgmt Studio won't let me change it. I see absolutely no
> way to remove it now (short of removing the security settings while it's
> still in SQL 2000 before moving to 2005). Any suggestions on a way to
> cleanup the security info and get it all redefined after moving to 2005?
> Thanks
> --
>|||That article about orphaned logins was exactly what I needed to read. I'm
still somewhat surprised that the UI gets "caught" by this situation and
cannot be fixed without using a stored procedure. However, this works, so
I'm happy
Thanks
--
Maz
"Arnie Rowland" wrote:

> Perhaps one of these will offer you a clue:
> http://www.sqlservercentral.com/col...
ses.asp
> Moving Users
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
> http://www.support.microsoft.com/?id=240872 Resolve Permission
> Issues -Database Is Moved Between SQL Servers
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "Spicie Mikie" <Maz@.newsgroups.nospam> wrote in message
> news:7B7F056C-5EB2-4548-BB47-4D58F024860B@.microsoft.com...
>
>sql

cannot delete subscriptions

To test SQL2000 replication, I enabled replications for NorthWind between
ServerA and ServerB. ServerA was the publisher and distributor, ServerB was
the subscriber.
After test, I deleted everything about replication on ServerA, at last, I
disabled replication. However, when I ckeck ServerB, in the
Replication\Subscriptions container, There is a "ServerA:NorthWind:" object.
I cannot remove it. If I right click this item, it only has a "set update
method..." menu which is grayed out, and another "help" menu. It looks like
a phantom object.
I then re-configure the server as a subscriber again (subscribe test DB and
set ServerA as a publisher), the Replication\Subscriptions container now has
another object "ServerA:test:test", and the content menu looks normal, which
includes "New pull subscription...", "Reinitialize", "delete"...etc. menu.
How can I delete the "ServerA:NorthWind:" object?
OK, it looks like you have lingering metadata. If you can, can you disable
replication one more time.
then run the following script
create table whack_me ([database] sysname, [table] sysname, constraint
Whack_me_pk primary key([database], [table]))
GO
insert into whack_me([database],[table])
exec sp_msforeachdb 'select [database]=''?'',[table]=''
MSreplication_subscriptions'' from ?.dbo.MSreplication_subscriptions where
publisher=@.@.servername'
insert into whack_me([database],[table])
exec sp_msforeachdb 'select [database]=''?'',[table]=''
MSsubscription_agents'' from ?.dbo.MSsubscription_agents where
publisher=@.@.servername'
whack_me will have a list of the lingering subscriptions and the databases
they are in. Delete the contents of these tables where the publisher
=@.@.servername
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MLi" <lige888@.gmail.com> wrote in message
news:uxGiwzXEGHA.3004@.TK2MSFTNGP15.phx.gbl...
> To test SQL2000 replication, I enabled replications for NorthWind between
> ServerA and ServerB. ServerA was the publisher and distributor, ServerB
> was the subscriber.
> After test, I deleted everything about replication on ServerA, at last, I
> disabled replication. However, when I ckeck ServerB, in the
> Replication\Subscriptions container, There is a "ServerA:NorthWind:"
> object. I cannot remove it. If I right click this item, it only has a "set
> update method..." menu which is grayed out, and another "help" menu. It
> looks like a phantom object.
> I then re-configure the server as a subscriber again (subscribe test DB
> and set ServerA as a publisher), the Replication\Subscriptions container
> now has another object "ServerA:test:test", and the content menu looks
> normal, which includes "New pull subscription...", "Reinitialize",
> "delete"...etc. menu.
> How can I delete the "ServerA:NorthWind:" object?
>
|||I ran the script on ServerA and got a bunch of errors:
Invalid object name 'Northwind.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'model.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
....
I just want to delete everything about NorthWind replication, any easy way?
like delete all rows about NorthWind in some particular table?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
> OK, it looks like you have lingering metadata. If you can, can you disable
> replication one more time.
> then run the following script
> create table whack_me ([database] sysname, [table] sysname, constraint
> Whack_me_pk primary key([database], [table]))
> GO
> insert into whack_me([database],[table])
> exec sp_msforeachdb 'select [database]=''?'',[table]=''
> MSreplication_subscriptions'' from ?.dbo.MSreplication_subscriptions
> where publisher=@.@.servername'
> insert into whack_me([database],[table])
> exec sp_msforeachdb 'select [database]=''?'',[table]=''
> MSsubscription_agents'' from ?.dbo.MSsubscription_agents where
> publisher=@.@.servername'
> whack_me will have a list of the lingering subscriptions and the databases
> they are in. Delete the contents of these tables where the publisher
> =@.@.servername
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "MLi" <lige888@.gmail.com> wrote in message
> news:uxGiwzXEGHA.3004@.TK2MSFTNGP15.phx.gbl...
>
|||in the table whack_me you will find a list of databases and tables. Go
through these databases and tables and delete the contents of them.
Ignore the error messages.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MLi" <lige888@.gmail.com> wrote in message
news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>I ran the script on ServerA and got a bunch of errors:
> Invalid object name 'Northwind.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'master.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'model.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> ...
> I just want to delete everything about NorthWind replication, any easy
> way? like delete all rows about NorthWind in some particular table?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||sp_removedbreplication
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"MLi" <lige888@.gmail.com> wrote in message
news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>I ran the script on ServerA and got a bunch of errors:
> Invalid object name 'Northwind.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'master.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'model.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> ...
> I just want to delete everything about NorthWind replication, any easy
> way? like delete all rows about NorthWind in some particular table?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||With all due respect Mike the OP did this to begin with.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> sp_removedbreplication
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "MLi" <lige888@.gmail.com> wrote in message
> news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>
|||I'm sorry Hilary, I guess I was just too stupid to realize that. Thank you
for helping me understand my stupidity.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ulLILIsEGHA.3708@.TK2MSFTNGP10.phx.gbl...
> With all due respect Mike the OP did this to begin with.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
>
|||Just found another way to delete this lingering object-- by SQL 2005
Management Studio
The object has a "delete" context menu
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> sp_removedbreplication
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "MLi" <lige888@.gmail.com> wrote in message
> news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>

Cannot delete SQL Job

I have several jobs in SQL 2005 that I cannot delete. I get "The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id" the conflict occured in database "msdb", table "dbo.sysmaintplan_subplans" columd 'job_id'.

How can I fix this?

TIA <JP>

Hello my friend,

It looks like the job you are trying to delete is used in another job. Delete this other job and then it should let you delete it.

Kind regards

Scotty

|||

Scotty,

How do I identify other dependencies? I see nothing obvious poking me in the eye. I have several of these and can't figure it out. Could it be because I'm running in a cluster?

Cannot delete SQL database user

Dear all,
I was trying to delete a user of a SQL database. But it cannot be deleted
and the error message is: "Error 15008: User 'username' does not exist in the
current database."
The database had some problem yesterday and I restored the backup
successfully. But the database user login cannot be deleted. How to solve
this problem?
Thanks in advance.
IvanThere is a big difference between a user and a login. Is this a user or a
login?
On 3/3/05 11:43 PM, in article
80C03227-032B-45D7-B821-6EFE7D89A54B@.microsoft.com, "Ivan"
<Ivan@.discussions.microsoft.com> wrote:
> Dear all,
> I was trying to delete a user of a SQL database. But it cannot be deleted
> and the error message is: "Error 15008: User 'username' does not exist in the
> current database."
> The database had some problem yesterday and I restored the backup
> successfully. But the database user login cannot be deleted. How to solve
> this problem?
> Thanks in advance.
> Ivan|||Hi,
It is a "Database User"
Ivan
"Aaron [SQL Server MVP]" wrote:
> There is a big difference between a user and a login. Is this a user or a
> login?
>
> On 3/3/05 11:43 PM, in article
> 80C03227-032B-45D7-B821-6EFE7D89A54B@.microsoft.com, "Ivan"
> <Ivan@.discussions.microsoft.com> wrote:
> > Dear all,
> >
> > I was trying to delete a user of a SQL database. But it cannot be deleted
> > and the error message is: "Error 15008: User 'username' does not exist in the
> > current database."
> > The database had some problem yesterday and I restored the backup
> > successfully. But the database user login cannot be deleted. How to solve
> > this problem?
> > Thanks in advance.
> >
> > Ivan
>|||Open Query Analyzer, and with the correct database context, what happens
when you say:
EXEC sp_helpusers
EXEC sp_helpuser 'username'
EXEC sp_helplogins
EXEC sp_helplogin 'username'
?
On 3/3/05 11:59 PM, in article
C33B5F1E-928D-4AF8-A673-996F097B9B6A@.microsoft.com, "Ivan"
<Ivan@.discussions.microsoft.com> wrote:
> Hi,
> It is a "Database User"
> Ivan
> "Aaron [SQL Server MVP]" wrote:
>> There is a big difference between a user and a login. Is this a user or a
>> login?
>>
>> On 3/3/05 11:43 PM, in article
>> 80C03227-032B-45D7-B821-6EFE7D89A54B@.microsoft.com, "Ivan"
>> <Ivan@.discussions.microsoft.com> wrote:
>> Dear all,
>> I was trying to delete a user of a SQL database. But it cannot be deleted
>> and the error message is: "Error 15008: User 'username' does not exist in
>> the
>> current database."
>> The database had some problem yesterday and I restored the backup
>> successfully. But the database user login cannot be deleted. How to solve
>> this problem?
>> Thanks in advance.
>> Ivan
>>|||Hi,
I tried to stop the SQL server service and started it again, then I could
delete it.
Thanks a lot and your help is greatly appreciated!
Ivan
"Aaron [SQL Server MVP]" wrote:
> Open Query Analyzer, and with the correct database context, what happens
> when you say:
> EXEC sp_helpusers
> EXEC sp_helpuser 'username'
> EXEC sp_helplogins
> EXEC sp_helplogin 'username'
> ?
>
> On 3/3/05 11:59 PM, in article
> C33B5F1E-928D-4AF8-A673-996F097B9B6A@.microsoft.com, "Ivan"
> <Ivan@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > It is a "Database User"
> >
> > Ivan
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >> There is a big difference between a user and a login. Is this a user or a
> >> login?
> >>
> >>
> >> On 3/3/05 11:43 PM, in article
> >> 80C03227-032B-45D7-B821-6EFE7D89A54B@.microsoft.com, "Ivan"
> >> <Ivan@.discussions.microsoft.com> wrote:
> >>
> >> Dear all,
> >>
> >> I was trying to delete a user of a SQL database. But it cannot be deleted
> >> and the error message is: "Error 15008: User 'username' does not exist in
> >> the
> >> current database."
> >> The database had some problem yesterday and I restored the backup
> >> successfully. But the database user login cannot be deleted. How to solve
> >> this problem?
> >> Thanks in advance.
> >>
> >> Ivan
> >>
> >>
>

Cannot delete SQL database user

Dear all,
I was trying to delete a user of a SQL database. But it cannot be deleted
and the error message is: "Error 15008: User 'username' does not exist in the
current database."
The database had some problem yesterday and I restored the backup
successfully. But the database user login cannot be deleted. How to solve
this problem?
Thanks in advance.
Ivan
There is a big difference between a user and a login. Is this a user or a
login?
On 3/3/05 11:43 PM, in article
80C03227-032B-45D7-B821-6EFE7D89A54B@.microsoft.com, "Ivan"
<Ivan@.discussions.microsoft.com> wrote:

> Dear all,
> I was trying to delete a user of a SQL database. But it cannot be deleted
> and the error message is: "Error 15008: User 'username' does not exist in the
> current database."
> The database had some problem yesterday and I restored the backup
> successfully. But the database user login cannot be deleted. How to solve
> this problem?
> Thanks in advance.
> Ivan
|||Hi,
It is a "Database User"
Ivan
"Aaron [SQL Server MVP]" wrote:

> There is a big difference between a user and a login. Is this a user or a
> login?
>
> On 3/3/05 11:43 PM, in article
> 80C03227-032B-45D7-B821-6EFE7D89A54B@.microsoft.com, "Ivan"
> <Ivan@.discussions.microsoft.com> wrote:
>
>
|||Open Query Analyzer, and with the correct database context, what happens
when you say:
EXEC sp_helpusers
EXEC sp_helpuser 'username'
EXEC sp_helplogins
EXEC sp_helplogin 'username'
?
On 3/3/05 11:59 PM, in article
C33B5F1E-928D-4AF8-A673-996F097B9B6A@.microsoft.com, "Ivan"
<Ivan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> Hi,
> It is a "Database User"
> Ivan
> "Aaron [SQL Server MVP]" wrote:
|||Hi,
I tried to stop the SQL server service and started it again, then I could
delete it.
Thanks a lot and your help is greatly appreciated!
Ivan
"Aaron [SQL Server MVP]" wrote:

> Open Query Analyzer, and with the correct database context, what happens
> when you say:
> EXEC sp_helpusers
> EXEC sp_helpuser 'username'
> EXEC sp_helplogins
> EXEC sp_helplogin 'username'
> ?
>
> On 3/3/05 11:59 PM, in article
> C33B5F1E-928D-4AF8-A673-996F097B9B6A@.microsoft.com, "Ivan"
> <Ivan@.discussions.microsoft.com> wrote:
>
>