Incorrect user login information showing in Enterprise Manager

When I check properties for database user x the login name says domain1\x .
If I delete that login from the server then look at the user x's properties
again it still says domain1\x in the login name!
How can this be fixed?Eric,
Since you say Enterprise Manager, I assume that you are using SQL Server
2000.
If I delete a login in SQL Server 2000 EM, it goes through and deletes the
users.
If I "sp_revokelogin 'domain1\x'" it still leaves the 'x' user behind, and I
can still see 'domain1\x' in EM if I was looking at it earlier. But, once I
refresh the EM user view I still see user 'x' but with a blank login.
EM does have some latency in refreshing (refresh a couple of times may be
necessary). Could that be your problem?
If not, have you done anything out of the ordinary, such as restoring a
database from another server, or even another domain?
RLF
"EricW" <ewientzek@.hotmail.com> wrote in message
news:OA8zHbd1HHA.4672@.TK2MSFTNGP05.phx.gbl...
> When I check properties for database user x the login name says domain1\x
> . If I delete that login from the server then look at the user x's
> properties again it still says domain1\x in the login name!
> How can this be fixed?
>
>|||I'm speaking about Managemenst Studio in SQL 2005.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eQrTdlg1HHA.4500@.TK2MSFTNGP02.phx.gbl...
> Eric,
> Since you say Enterprise Manager, I assume that you are using SQL Server
> 2000.
> If I delete a login in SQL Server 2000 EM, it goes through and deletes the
> users.
> If I "sp_revokelogin 'domain1\x'" it still leaves the 'x' user behind, and
> I can still see 'domain1\x' in EM if I was looking at it earlier. But,
> once I refresh the EM user view I still see user 'x' but with a blank
> login.
> EM does have some latency in refreshing (refresh a couple of times may be
> necessary). Could that be your problem?
> If not, have you done anything out of the ordinary, such as restoring a
> database from another server, or even another domain?
> RLF
> "EricW" <ewientzek@.hotmail.com> wrote in message
> news:OA8zHbd1HHA.4672@.TK2MSFTNGP05.phx.gbl...
>|||Eric W,
OK, you are using SQL 2005.
When using SSMS you delete a login, you will get this message: "Deleting
server logins does not delete the database users associated with the logins.
To complete the process, delete the users in each database. It may be
necessary to first transfer the ownership of schemas to new users."
But your question is" "Why does the user entry still know the login name?"
The answer is that it records the SID in the user. If you:
select * from sys.database_principals
you will see the SIDs of the logins used to create the users. In fact, if
you copy the SID for a deleted Windows login and paste it into:
SELECT SUSER_SNAME(0x0...9)
it will still return the name of the Windows Login. (In SQL Server 2000,
sysusers maintained the login's SID, but since the rows were usually deleted
automatically, you never saw this behavior manifested.)
To get rid of this, you must also drop the user yourself. Which may mean
that you must first drop that user's schema.
RLF
"EricW" <ewientzek@.hotmail.com> wrote in message
news:OaZe5gP2HHA.4476@.TK2MSFTNGP06.phx.gbl...
> I'm speaking about Managemenst Studio in SQL 2005.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eQrTdlg1HHA.4500@.TK2MSFTNGP02.phx.gbl...
>

incorrect syntax near ;

Hi,

I am trying to test a login form and I get this error message and can't find out why. Istarted out with the Login control, but since I have to try it on the ISP's server, I can't use the SQL Server Managment Studio's integrated authentication. So, I converted the login control to a template and assigned a handler for the login button:

protectedvoid LoginButton_Click(object sender,EventArgs e)

{

String usrname = lpLogin.UserName.ToString(); //lpLogin is the <ASP:Login ...>

String conString ="Data Source=mylocalserver\\SQLEXPRESS;Initial Catalog=LPRU;Integrated Security=True";

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"';";

SqlConnection con =newSqlConnection(conString);

SqlCommand cmd =newSqlCommand(selQuery, con);

con.Open();

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // <--it says "syntax error near ';' " on this line, I tried it without CommandBehavior

while (rdr.Read())

{

Label1.Text= rdr.GetString(0) + rdr.GetString(1); // for testing purposes, trying to print out first name and last name

}

rdr.Close();

con.Close();

}

Is there a way of using SQLServer 2000, used by my ISP, and take advantage of .net 2.0's login control, roles, membership, ...? By just using a connection string?

I think you have an unwanted ";" in the line code

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"';";

You should change it to this,maybe it can work well.

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname;

wish this help you

|||

In the immortal words of Homer Simpson, "DOH!".. (I know your working in C#, but that doesn't mean a semi-colon is good for everything)

look at your line:

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"';";

And then look at this line:

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"')";

Don't you just hate it when that happens... For the record, the queryis executed on the line where you get the exception rather than where you make the assignment.

|||

Jason,

You got rid of the offending semicolon, but you still have to close the single quote and close parenthesis around 'usrname'

|||

NoBullMan:

String selQuery ="SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname +"';";

You missed a ')' at the end of the query string, which you can easily check in Query Analyzer (or any where you can parse T-SQL statement) BTW, if there is a single quote in the usrname, the query string will be broken, unless you replace every single quote in the usrname with 2 single quotes; and such concatenated queries may lead to SQL Injection, so always useParameterized Queries.

|||

Thank you guys. I am from php/MySQL background and the ';' at the end of the query doesn't cause problems in MySQL. I appreciate your help.

|||T-SQL in SQL Server also accepts ';'sql

incorrect password

I create a SQL login on my MS-SQL 2k Personal Edition and set the password
and confirm the password.
If I immediately go back into the login and do changes in Database Access, I
get prompted to confirm the password and when I enter the password again, it
tells me that the confirmation password is incorrect.
What is wrong?
ThanksThis can occur after appying MS03-031: Cumulative Security Patch for SQL
Server
FIX: You are prompted for password confirmation after you change a standard
SQL Server login
http://support.microsoft.com/?kbid=826161
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Esmar Joensen" <EsmarJoensen@.discussions.microsoft.com> wrote in message
news:6265A40C-427B-40EA-8E94-9EC145541A0C@.microsoft.com...
>I create a SQL login on my MS-SQL 2k Personal Edition and set the password
> and confirm the password.
> If I immediately go back into the login and do changes in Database Access,
> I
> get prompted to confirm the password and when I enter the password again,
> it
> tells me that the confirmation password is incorrect.
> What is wrong?
> Thanks
>
>

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean
I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>