can't drop user, owns objects

I migrated copies of a database to a test server, but I cannot log on. My
admin account needs to be dropped and readded but every time I run
sp_dropuser in query analyzer I get the error :
Msg 15183, level 16, state 1, ...
"the user owns objects in the database and cannot be dropped."
then a list of the objects (EG TMP_SYSA_1234) is displayed.
How can I delete these objects? I assume then I will be able to delete the
user and recreate him.
On 20 mar, 19:09, totoro <tot...@.discussions.microsoft.com> wrote:
> I migrated copies of a database to a test server, but I cannot log on. My
> admin account needs to be dropped and readded but every time I run
> sp_dropuser in query analyzer I get the error :
> Msg 15183, level 16, state 1, ...
> "the user owns objects in the database and cannot be dropped."
> then a list of the objects (EG TMP_SYSA_1234) is displayed.
> How can I delete these objects? I assume then I will be able to delete the
> user and recreate him.
Hi,
You're perfectly right... This error means that some objects are still
owned by this user.
In order to drop each object, you will first need to know what kind of
objects are owned by the user you would like to delete. Then, you'll
be able to run the correct drop commands in the query analyser...
As an example, here are some usual drop commands :
DROP TABLE [schema].[Table name]
DROP VIEW [schema].[View name]
DROP PROCEDURE [schema].[Procedure name]
...
Whenever all the objects owned will be dropped, you'll be able to use
the sp_dropuser procedure.
Hope this helps,
Cdric Del Nibbio
MCTS SQL Server 2005
MCSD .NET
MCT
http://cedric-delnibbio-sql.blogspot.com
|||thank you, Cedric
"Cédric Del Nibbio" wrote:

> On 20 mar, 19:09, totoro <tot...@.discussions.microsoft.com> wrote:
> Hi,
> You're perfectly right... This error means that some objects are still
> owned by this user.
> In order to drop each object, you will first need to know what kind of
> objects are owned by the user you would like to delete. Then, you'll
> be able to run the correct drop commands in the query analyser...
> As an example, here are some usual drop commands :
> DROP TABLE [schema].[Table name]
> DROP VIEW [schema].[View name]
> DROP PROCEDURE [schema].[Procedure name]
> ...
> Whenever all the objects owned will be dropped, you'll be able to use
> the sp_dropuser procedure.
> Hope this helps,
> Cédric Del Nibbio
> MCTS SQL Server 2005
> MCSD .NET
> MCT
> http://cedric-delnibbio-sql.blogspot.com
>

can't drop user, owns objects

I migrated copies of a database to a test server, but I cannot log on. My
admin account needs to be dropped and readded but every time I run
sp_dropuser in query analyzer I get the error :
Msg 15183, level 16, state 1, ...
"the user owns objects in the database and cannot be dropped."
then a list of the objects (EG TMP_SYSA_1234) is displayed.
How can I delete these objects? I assume then I will be able to delete the
user and recreate him.On 20 mar, 19:09, totoro <tot...@.discussions.microsoft.com> wrote:
> I migrated copies of a database to a test server, but I cannot log on. My
> admin account needs to be dropped and readded but every time I run
> sp_dropuser in query analyzer I get the error :
> Msg 15183, level 16, state 1, ...
> "the user owns objects in the database and cannot be dropped."
> then a list of the objects (EG TMP_SYSA_1234) is displayed.
> How can I delete these objects? I assume then I will be able to delete the
> user and recreate him.
Hi,
You're perfectly right... This error means that some objects are still
owned by this user.
In order to drop each object, you will first need to know what kind of
objects are owned by the user you would like to delete. Then, you'll
be able to run the correct drop commands in the query analyser...
As an example, here are some usual drop commands :
DROP TABLE [schema].[Table name]
DROP VIEW [schema].[View name]
DROP PROCEDURE [schema].[Procedure name]
.=2E.
Whenever all the objects owned will be dropped, you'll be able to use
the sp_dropuser procedure.
Hope this helps,
C=E9dric Del Nibbio
MCTS SQL Server 2005
MCSD .NET
MCT
http://cedric-delnibbio-sql.blogspot.com|||thank you, Cedric
"Cédric Del Nibbio" wrote:

> On 20 mar, 19:09, totoro <tot...@.discussions.microsoft.com> wrote:
> Hi,
> You're perfectly right... This error means that some objects are still
> owned by this user.
> In order to drop each object, you will first need to know what kind of
> objects are owned by the user you would like to delete. Then, you'll
> be able to run the correct drop commands in the query analyser...
> As an example, here are some usual drop commands :
> DROP TABLE [schema].[Table name]
> DROP VIEW [schema].[View name]
> DROP PROCEDURE [schema].[Procedure name]
> ...
> Whenever all the objects owned will be dropped, you'll be able to use
> the sp_dropuser procedure.
> Hope this helps,
> Cédric Del Nibbio
> MCTS SQL Server 2005
> MCSD .NET
> MCT
> http://cedric-delnibbio-sql.blogspot.com
>sql

can't drop login

can't drop login

icrosoft SQL Server, Error: 15141

The server principal owns an event notification and cannot be dropped.

notification services do not show anything, where else could it be ?

Did you opened up the Service Broker node in the database node ? There might be a Queue open for that principal that you want to delete.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Have you checked sys.server_event_notifications? Is there any notification there owned by the login?

Queues are owned by database principals, so this error is not caused by the presence of a queue.

Thanks
Laurentiu

|||

thanks a lot

sys.server_event_notifications showed a row related to user.

I droped notification and and was able to drop login

can't drop login

can't drop login

icrosoft SQL Server, Error: 15141

The server principal owns an event notification and cannot be dropped.

notification services do not show anything, where else could it be ?

Did you opened up the Service Broker node in the database node ? There might be a Queue open for that principal that you want to delete.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Have you checked sys.server_event_notifications? Is there any notification there owned by the login?

Queues are owned by database principals, so this error is not caused by the presence of a queue.

Thanks
Laurentiu

|||

thanks a lot

sys.server_event_notifications showed a row related to user.

I droped notification and and was able to drop login

Can't Deploy AdventureWorks Database

Hi,

We attempted to reload the Adventure Works DW in Analysis Services 2005 after a previous install. We first dropped and uninstalled the sample OLTP databases, then removed the previous install using the add/remove programs tool. We then installed the samples again using the AdventureWorks Data Warehouse Database and Analysis Services Project, attached the two databases to the database engine, and then attempted to deploy the Adventure Works. sln project using the Business Intelligence Development Studio. We encountered the following error when we processed the AdventureWorks DW Standard Edition database in Analysis Services:

Error 1 OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01; Shared Memory Provider: The system cannot open the file. ; 08S01; Login failed for user 'NT AUTHORITY\SYSTEM'.; 28000; Cannot open database requested in login 'AdventureWorksDW'. Login fails.; 42000. 0 0

Please note that we used the configuration manager to enable named pipes and allow remote connections. We also, renamed the target server (to the named server instance instead of the localhost), disabled any Windows and Norton firewalls, and changed the deployment server edition to the Evaluation version in the Adventure Works DW Property page.

Nothing seems to work. Any help would be greatly appreciated!

Thanks,

Brad

I also ran into this problem (can't deploy lesson4 Analysis Services Tutorial) but I was able to solve it by opening the project in Developement Studio and right click on "Adventure Works DW.ds" under "Data Sources" . Then click "View Designer" and fix the connection string to use my named instance and Windows Authentication.|||

To assist with troubleshooting, could you please try editing the data source in the project, and in the dialog:

- go to "Impersonation Information" tab

- choose to use "Specific User Name & Password"

- enter an NT user name and password that you know has read rights to the DB

Then try to redeploy and process.

Thanks

Paul Sanders (MSFT)

|||

Great...!

Thanks...! It worked for me too.

|||Thanks everyone above. I wasted some 4 crucial hours for figuring this out. Thanks. It worked for me too.|||

To: Chu Xu

"and right click on "Adventure Works DW.ds""

Actually a left click. At least in my version of VS. Took me so much time to figure out something so obvious.

Regards

Can't Deploy AdventureWorks Database

Hi,

We attempted to reload the Adventure Works DW in Analysis Services 2005 after a previous install. We first dropped and uninstalled the sample OLTP databases, then removed the previous install using the add/remove programs tool. We then installed the samples again using the AdventureWorks Data Warehouse Database and Analysis Services Project, attached the two databases to the database engine, and then attempted to deploy the Adventure Works. sln project using the Business Intelligence Development Studio. We encountered the following error when we processed the AdventureWorks DW Standard Edition database in Analysis Services:

Error 1 OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01; Shared Memory Provider: The system cannot open the file. ; 08S01; Login failed for user 'NT AUTHORITY\SYSTEM'.; 28000; Cannot open database requested in login 'AdventureWorksDW'. Login fails.; 42000. 0 0

Please note that we used the configuration manager to enable named pipes and allow remote connections. We also, renamed the target server (to the named server instance instead of the localhost), disabled any Windows and Norton firewalls, and changed the deployment server edition to the Evaluation version in the Adventure Works DW Property page.

Nothing seems to work. Any help would be greatly appreciated!

Thanks,

Brad

I also ran into this problem (can't deploy lesson4 Analysis Services Tutorial) but I was able to solve it by opening the project in Developement Studio and right click on "Adventure Works DW.ds" under "Data Sources" . Then click "View Designer" and fix the connection string to use my named instance and Windows Authentication.|||

To assist with troubleshooting, could you please try editing the data source in the project, and in the dialog:

- go to "Impersonation Information" tab

- choose to use "Specific User Name & Password"

- enter an NT user name and password that you know has read rights to the DB

Then try to redeploy and process.

Thanks

Paul Sanders (MSFT)

|||

Great...!

Thanks...! It worked for me too.

|||Thanks everyone above. I wasted some 4 crucial hours for figuring this out. Thanks. It worked for me too.|||

To: Chu Xu

"and right click on "Adventure Works DW.ds""

Actually a left click. At least in my version of VS. Took me so much time to figure out something so obvious.

Regards

Can't Deploy AdventureWorks Database

Hi,

We attempted to reload the Adventure Works DW in Analysis Services 2005 after a previous install. We first dropped and uninstalled the sample OLTP databases, then removed the previous install using the add/remove programs tool. We then installed the samples again using the AdventureWorks Data Warehouse Database and Analysis Services Project, attached the two databases to the database engine, and then attempted to deploy the Adventure Works. sln project using the Business Intelligence Development Studio. We encountered the following error when we processed the AdventureWorks DW Standard Edition database in Analysis Services:

Error 1 OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01; Shared Memory Provider: The system cannot open the file. ; 08S01; Login failed for user 'NT AUTHORITY\SYSTEM'.; 28000; Cannot open database requested in login 'AdventureWorksDW'. Login fails.; 42000. 0 0

Please note that we used the configuration manager to enable named pipes and allow remote connections. We also, renamed the target server (to the named server instance instead of the localhost), disabled any Windows and Norton firewalls, and changed the deployment server edition to the Evaluation version in the Adventure Works DW Property page.

Nothing seems to work. Any help would be greatly appreciated!

Thanks,

Brad

I also ran into this problem (can't deploy lesson4 Analysis Services Tutorial) but I was able to solve it by opening the project in Developement Studio and right click on "Adventure Works DW.ds" under "Data Sources" . Then click "View Designer" and fix the connection string to use my named instance and Windows Authentication.|||

To assist with troubleshooting, could you please try editing the data source in the project, and in the dialog:

- go to "Impersonation Information" tab

- choose to use "Specific User Name & Password"

- enter an NT user name and password that you know has read rights to the DB

Then try to redeploy and process.

Thanks

Paul Sanders (MSFT)

|||

Great...!

Thanks...! It worked for me too.

|||Thanks everyone above. I wasted some 4 crucial hours for figuring this out. Thanks. It worked for me too.|||

To: Chu Xu

"and right click on "Adventure Works DW.ds""

Actually a left click. At least in my version of VS. Took me so much time to figure out something so obvious.

Regards

Can't Deploy AdventureWorks Database

Hi,

We attempted to reload the Adventure Works DW in Analysis Services 2005 after a previous install. We first dropped and uninstalled the sample OLTP databases, then removed the previous install using the add/remove programs tool. We then installed the samples again using the AdventureWorks Data Warehouse Database and Analysis Services Project, attached the two databases to the database engine, and then attempted to deploy the Adventure Works. sln project using the Business Intelligence Development Studio. We encountered the following error when we processed the AdventureWorks DW Standard Edition database in Analysis Services:

Error 1 OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01; Shared Memory Provider: The system cannot open the file. ; 08S01; Login failed for user 'NT AUTHORITY\SYSTEM'.; 28000; Cannot open database requested in login 'AdventureWorksDW'. Login fails.; 42000. 0 0

Please note that we used the configuration manager to enable named pipes and allow remote connections. We also, renamed the target server (to the named server instance instead of the localhost), disabled any Windows and Norton firewalls, and changed the deployment server edition to the Evaluation version in the Adventure Works DW Property page.

Nothing seems to work. Any help would be greatly appreciated!

Thanks,

Brad

I also ran into this problem (can't deploy lesson4 Analysis Services Tutorial) but I was able to solve it by opening the project in Developement Studio and right click on "Adventure Works DW.ds" under "Data Sources" . Then click "View Designer" and fix the connection string to use my named instance and Windows Authentication.|||

To assist with troubleshooting, could you please try editing the data source in the project, and in the dialog:

- go to "Impersonation Information" tab

- choose to use "Specific User Name & Password"

- enter an NT user name and password that you know has read rights to the DB

Then try to redeploy and process.

Thanks

Paul Sanders (MSFT)

|||

Great...!

Thanks...! It worked for me too.

|||Thanks everyone above. I wasted some 4 crucial hours for figuring this out. Thanks. It worked for me too.|||

To: Chu Xu

"and right click on "Adventure Works DW.ds""

Actually a left click. At least in my version of VS. Took me so much time to figure out something so obvious.

Regards

Can't Deploy AdventureWorks Database

Hi,

We attempted to reload the Adventure Works DW in Analysis Services 2005 after a previous install. We first dropped and uninstalled the sample OLTP databases, then removed the previous install using the add/remove programs tool. We then installed the samples again using the AdventureWorks Data Warehouse Database and Analysis Services Project, attached the two databases to the database engine, and then attempted to deploy the Adventure Works. sln project using the Business Intelligence Development Studio. We encountered the following error when we processed the AdventureWorks DW Standard Edition database in Analysis Services:

Error 1 OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01; Shared Memory Provider: The system cannot open the file. ; 08S01; Login failed for user 'NT AUTHORITY\SYSTEM'.; 28000; Cannot open database requested in login 'AdventureWorksDW'. Login fails.; 42000. 0 0

Please note that we used the configuration manager to enable named pipes and allow remote connections. We also, renamed the target server (to the named server instance instead of the localhost), disabled any Windows and Norton firewalls, and changed the deployment server edition to the Evaluation version in the Adventure Works DW Property page.

Nothing seems to work. Any help would be greatly appreciated!

Thanks,

Brad

I also ran into this problem (can't deploy lesson4 Analysis Services Tutorial) but I was able to solve it by opening the project in Developement Studio and right click on "Adventure Works DW.ds" under "Data Sources" . Then click "View Designer" and fix the connection string to use my named instance and Windows Authentication.|||

To assist with troubleshooting, could you please try editing the data source in the project, and in the dialog:

- go to "Impersonation Information" tab

- choose to use "Specific User Name & Password"

- enter an NT user name and password that you know has read rights to the DB

Then try to redeploy and process.

Thanks

Paul Sanders (MSFT)

|||

Great...!

Thanks...! It worked for me too.

|||Thanks everyone above. I wasted some 4 crucial hours for figuring this out. Thanks. It worked for me too.|||

To: Chu Xu

"and right click on "Adventure Works DW.ds""

Actually a left click. At least in my version of VS. Took me so much time to figure out something so obvious.

Regards