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 create multiple triggers

PLS HELP !!!!
I cannot create multiple triggers (AFTER) of command type INSERT. When
I create a new one the previous one gets deleted.
Prodcut: SQL Server Enterprise Edition.
Product Version:8.00.194 (TRM).
The funny thing is that this happens on clients production environment
while I am able to create multiple triggers on my development
environment. The properties for this sql servers is as follows
Product: SQL Server Enterprise Edition.
Production Version: 8.00.760 (SP3).
Is this because of different product version or am i missing something?Hi
Tell you client to apply at least SQL Server 20000 SP3a. They are totally
unpatched and susceptible to Slammer.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<agandhi@.usc.edu> wrote in message
news:1121983311.188033.51980@.g47g2000cwa.googlegroups.com...
> PLS HELP !!!!
> I cannot create multiple triggers (AFTER) of command type INSERT. When
> I create a new one the previous one gets deleted.
> Prodcut: SQL Server Enterprise Edition.
> Product Version:8.00.194 (TRM).
> The funny thing is that this happens on clients production environment
> while I am able to create multiple triggers on my development
> environment. The properties for this sql servers is as follows
> Product: SQL Server Enterprise Edition.
> Production Version: 8.00.760 (SP3).
> Is this because of different product version or am i missing something?
>|||Thanks for the recommendation and I will do that immediately but any
idea about not being able to create multiple triggers.
Regards,|||Why SP3a and not SP4? SQL Server 2000 is installed on Windows 2000
Machine.|||The service pack may fix the problem you're having with creating multiple
triggers. If it works on the development server and not on the production
server, then it is probably the service pack. Unless the triggers are
instead of triggers, you can have more than one trigger of the same kind on
a table provided the name of the trigger is different for each one.
<agandhi@.usc.edu> wrote in message
news:1121985554.564111.259640@.g47g2000cwa.googlegroups.com...
> Thanks for the recommendation and I will do that immediately but any
> idea about not being able to create multiple triggers.
> Regards,
>|||agandhi@.usc.edu wrote:
> Why SP3a and not SP4? SQL Server 2000 is installed on Windows 2000
> Machine.
He said SP3a as a *minimum*. Whether they're ready to move up to SP4 or
not is more of a business decision, and less of a "we have a massive
security hole here" kind of thing.
Damien

cannot connect to SQL database through IIS

Hello,

I'm new...

I've installed Web Matrix, and MDSE. I've been successful in installing the Time Tracker Starter kit, I've connected to multiple databases (SQL and Access) within Web Matrix and I'm now trying to connect to my own SQL database...with limited success.

Scenario:
I've created a SQL database using MS Access (*.adp) access project for SQL. I've created a new ASPX with Web Matrix and placed a database table into my new ASPX by draggin-n-droppin the table from the DATA connections in Web Matrix. Everything goes smoothly...

When I view the page using the Web Matrix 'built-in' web server (i.e. http://localhost:8080/default.aspx), everything displays just fine...it appears that the script is connecting properly to the database and executing the query without any problem -- I can view my data within the page. However, when I view this page using IIS (i.e. http://localhost/default.aspx), I get:
System.Data.SqlClient.SqlException: Cannot open database requested in login 'myDB'. Login fails. Login failed for user 'MYCOMPUTER\ASPNET' and then a stack dump.

Same code...different web servers...different results.

I've checked as many SQL permissions as I could think of, but my experience with SQL is limited. I'm using Microsoft's FREE SQL tools, but I'm pretty certain that there should be a simple way to ensure that my ASPNET user account has appropriate permissions to access my SQL database through IIS.

any suggestions?As you can see the sql error is occuring as the ASPNET account on your machine doesn't have sql priveleges - to solve this you could either add the apsnet account to sql logins and give it sufficient permissions or impersonate and identity which has sufficient sql priveleges.

Hope that helps
Kashif|||I have the same problem .
Would you please tell me how to add the apsnet account to sql logins .|||You can either run the following stored procedure to add the aspnet account

sp_grantlogin @.loginame

in your case @.login name would be "MachineName\ASPNET" or if you have SQL Enterprise Manager you can open the sql server you want to add the login to and then select security -> Logins -> NewLogin

Hope that helps
Kashif|||Thank you so much. However, I do have MSDE on my Windows XP machine.
and stil have the same problem.
your help will be appreciated.|||To installed MSDE on your system with SQL authentication support use this command:
Setup SAPWD=(Some password) SecurityMode=SQL

where setup is the executable of the setup of MSDE (download it over here with SP3 integrated:http://www.asp.net/msde/default.aspx?tabindex=0&tabid=1). Follow the instructions of the setup.

When you've done so, you can login to the MSDE database using this credentials:
uid=sa
pwd=(Some password)

Replace the (Some password) in the command line by the password you like.|||Thank you so much for your response however I already done that but it doesn't work:
This is my frist samle project for MSDE so would tell me the steps:
1. Create a project by MS Access and login='sa' password='passowrd'. that mean the SQL server is running and it's shown at my desck top.
2. write a small ample of aspx code by WebMatrix
3, it gives me the "Cannot open database requested in login 'adp1'. Login fails. Login failed for user 'sa'."

<%@. Page Language="C#" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.OleDb" %>
<%@. import Namespace="System.Data.SqlClient" %>
<script runat="server"
 void Page_Load(object sender, EventArgs e) {
 // TODO: Update the ConnectionString and CommandText values for your application
 string ConnectionString = "server=(local);database='adp1';uid='sa'; pwd='password'";
 string CommandText = "select * from news";
 SqlConnection myConnection = new SqlConnection(ConnectionString);
 SqlDataAdapter objCmd1 = new SqlDataAdapter( CommandText, myConnection);
 DataSet ds1 = new DataSet();
 objCmd1.Fill(ds1);
 //myDataList1.DataSource = ds1;
 //MyDataList1.DataBind();
 }
</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
 <h2>Simple Data Report
 </h2>
 <hr size="1" />
 <ASP:DataList id="MyDataList1" RepeatColumns="1" RepeatDirection="Horizontal" runat="server">
 <ItemTemplate>
 <table border="0" cellpadding="2" width="100%" height="68">
 <tr valign="TOP">
 <td width="20%" valign="top" rowspan="2" height="62">
 Hello
 </td>
 <td width="80%" valign="top" height="7">
 <font face="Verdana" size="2"> <%# DataBinder.Eval(Container.DataItem, "news_title") %> <font face="Verdana" size="1.5"><%# DataBinder.Eval(Container.DataItem, "news_date") %> </font> </font></td>
 </tr>
 <tr>
 <td width="70%" valign="top" height="49">
 <font face="Verdana" size="1.5"> <%# DataBinder.Eval(Container.DataItem, "news_description") %> </font>
 </td>
 </tr>
 </table>
 </ItemTemplate>
 </ASP:DataList>
</body>
</html>

|||From the error it seems the password you are using for sa is incorrect, can you try running some sql scripts from command line using login sa and password password and see if you are able to run those.

-Kashif|||thank you so muh, I am sorry but I been away for a short vacation.
This is my frist samle project for MSDE so would tell me the steps.
I do not have SQL Server..
Would you tell me how to check these command. ...

sat|||Open command prompt, switch to binn directory msde and run something like the following

osql -U sa id -P myPwd -S (.) -d Northwind -q "Select * from customers"

if this fails to run then we know that you are either not setting the sa password right or the MSSQL service is not running on your machine. Also if you have a named instance of MSDE installed then you would use "-S ServerName\InstanceName"

Hope that helps
Kashif|||Thank you.
I have created MS Acces project called adp1. wehn I open the project it asks me for user "sa" and password. I have database tables "news". however when I write the following:
>osql -U sa -P password -S (.) -D adp1 -Q "Select * from news"

Error " Data Source name not found and no default driver"

>osql -U sa -P password -S localhost\adp1 -D adp1 -Q "Select * from news"

Error " Data Source name not found and no default driver"|||actullay, after I run the previous command I got this error on my
http://localhost/

403 Forbidden - The server denies the specified Uniform Resource Locator (URL). Contact the server administrator. (12202)
Internet Security and Acceleration Server.

any explations please..!!! thank you|||The error you have specified above just implies that there is no page defined. Can you look up the Default wesite's home directory and see the "Default document" tab.

-Kashif|||Hello all,

I am writing this message simply because I started this thread and I wanted to remark that with ALL your help, and with many hours of studying SQL server permissions, osql commands and syntax, SQL server port configuration and firewalls, I have resolved the problems I was having when I started this thread.

Thanks to all who provided excellent advice and tolerated my previous ignorances.

Regards.