How to enrypt password in a table?

Hi,
I use SQL 2K and have a table "tblPassword" which stores all the username
and passwords for front end login validation. I want to encrypt these table
so only "sa" can read. How can I accomplish this ?
Thanks
kaiHi
Not in SQL Server 2000. Get an application to do it and look at it though
the application.
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/
"kai" <kailiang@.earthlink.net> wrote in message
news:J_PHe.1993$ns.1635@.newsread1.news.atl.earthlink.net...
> Hi,
> I use SQL 2K and have a table "tblPassword" which stores all the username
> and passwords for front end login validation. I want to encrypt these
> table so only "sa" can read. How can I accomplish this ?
> Thanks
> kai
>|||Hi,
Have a look into the below URL.
http://www.activecrypt.com/help/xpc...tep_by_step.htm
Thanks
Hari
SQL Server MVP
"kai" <kailiang@.earthlink.net> wrote in message
news:J_PHe.1993$ns.1635@.newsread1.news.atl.earthlink.net...
> Hi,
> I use SQL 2K and have a table "tblPassword" which stores all the username
> and passwords for front end login validation. I want to encrypt these
> table so only "sa" can read. How can I accomplish this ?
> Thanks
> kai
>|||Hi, Mike
Thanks.
Kai
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OMAl6q5lFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Hi
> Not in SQL Server 2000. Get an application to do it and look at it though
> the application.
> 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/
> "kai" <kailiang@.earthlink.net> wrote in message
> news:J_PHe.1993$ns.1635@.newsread1.news.atl.earthlink.net...
>|||Hari,
Thanks for the link. Where I can find
master..xp_sha1
Thanks
Kai
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u74DSv5lFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Have a look into the below URL.
> http://www.activecrypt.com/help/xpc...tep_by_step.htm
> Thanks
> Hari
> SQL Server MVP
> "kai" <kailiang@.earthlink.net> wrote in message
> news:J_PHe.1993$ns.1635@.newsread1.news.atl.earthlink.net...
>

how to encrypt the password field in SQL table

Hi,

I have a login table with username and password as attributes. I need to encrypt the password using stored procedure and then save it in the database. And also while retrieving the password, decrypt using the same stored procedure and get the original text.

I dont know how to do it in SQL server 2000.

Please help me on this. Its urgent.

Thanks and Regards

Unfortunately, this is not an easy task with SQL 2000.

You will have an easier time if you use the VS.NET's encryption library to encrypt the password, and store the encrypted (hashed) value.

Then retrieve the encrypted value, use the encryption library at the application level to encrypt and match to the stored value.

Otherwise, you will be passing the password as clear text across the 'wires' -which isn't very secure.

|||

ok thanks for the suggestion.

I need to confirm whether the same can be done in SQL server 2005.

If can how to do it?

thanks and regards

|||

SQL Server 2005 has rich encryption capabilities.

However, there are two issues you need to consider about encryption.

Data at rest Data in Transit|||If you cannot rely on the client functionality for en/decrypting the information you will have to buy a third party product for SQL Server 2000 which is in common a extended procedures being able to use cryptographic libraries.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to eliminate login screen when i try to access report via URL

I am trying to access a report via url

http://69.23.3.112/reportserver?/rptProject/rptStatus&eStatus=All&eUser=2

it always asks for username and password.

All my users login to my project which is asp.net 1.1(vs2003) based project, now from inside the project, if they try to access any report from (which is on framework 2.0), they have to go through a autentication screen which is related to sql server reporting services.

can you please help, how to override this login screen.

Thank you very much for the information.

If It is for login to the datasource then you ned to pass datasource credentials. (reportviewer.setdatasourcecredentials())And if it ask you to login to the report server than you need to set the report server to allow access of the report to your website.

and if you want you can set report server credentials.. and give that credentials access to the reports using report manager.

|||

Hi, Thanks.

I gave permissions to report manager folder for aspnet and also iusr_machine name both.

and also checked anonymous login for reports virtual directory under intepub.

still i get the login screen if somone trying to access reports via reporting services reports folder.

i am calling the reports via url from vs 2003 , and my reports are on vs 2005.

And microsoft did'nt release no report viewer with framework 1.1., that is causing the problem.

please help guys.,

Thank you all.

|||

I can give you one option.

You can crete one project with a page (may be 1 default.aspx) containing report viewer and if you want parameter promp controls of your own.

And redirect your users from original website to new website when ever they select menuitem( or whatever you have used) for report.

Now for login screen.

I need to know where it pop us..

when anyone trying to go to report folder or when any one trying to run report.

If it is asking when anyone trying to go to report folder.. I thing you need to pass login for the user in URL( Not sure how)

If it is asking when they run report just do one thing. go to each report.. than properties than datasource and save credentials for datasource.

How to eliminate login screen when i try to access report via URL

I am trying to access a report via url

http://69.23.3.112/reportserver?/rptProject/rptStatus&eStatus=All&eUser=2

it always asks for username and password.

All my users login to my project which is asp.net 1.1(vs2003) based project, now from inside the project, if they try to access any report from (which is on framework 2.0), they have to go through a autentication screen which is related to sql server reporting services.

can you please help, how to override this login screen.

Thank you very much for the information.

If It is for login to the datasource then you ned to pass datasource credentials. (reportviewer.setdatasourcecredentials())And if it ask you to login to the report server than you need to set the report server to allow access of the report to your website.

and if you want you can set report server credentials.. and give that credentials access to the reports using report manager.

|||

Hi, Thanks.

I gave permissions to report manager folder for aspnet and also iusr_machine name both.

and also checked anonymous login for reports virtual directory under intepub.

still i get the login screen if somone trying to access reports via reporting services reports folder.

i am calling the reports via url from vs 2003 , and my reports are on vs 2005.

And microsoft did'nt release no report viewer with framework 1.1., that is causing the problem.

please help guys.,

Thank you all.

|||

I can give you one option.

You can crete one project with a page (may be 1 default.aspx) containing report viewer and if you want parameter promp controls of your own.

And redirect your users from original website to new website when ever they select menuitem( or whatever you have used) for report.

Now for login screen.

I need to know where it pop us..

when anyone trying to go to report folder or when any one trying to run report.

If it is asking when anyone trying to go to report folder.. I thing you need to pass login for the user in URL( Not sure how)

If it is asking when they run report just do one thing. go to each report.. than properties than datasource and save credentials for datasource.

sql

How to drop login

Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
Martin
Behind the scenes, EM is dropping the user from the databases to which (s)he
had been granted access, and then it drops the login. To script it, you
would have to loop through all DB's for that user and do the drops, followed
by sp_droplogin.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
Martin
|||Hi
You can run SQL Server Profiler to see what is going on behind the scenes
when you delete the LOGIN via EM
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to drop the login, the login should be revoked the DB access before issue
> the sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>
|||oh, no shortcut...bad to know the fact... :p
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:um7DtsTyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Behind the scenes, EM is dropping the user from the databases to which
> (s)he
> had been granted access, and then it drops the login. To script it, you
> would have to loop through all DB's for that user and do the drops,
> followed
> by sp_droplogin.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to
> drop the login, the login should be revoked the DB access before issue the
> sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>
|||woo, this absolutely new to me.....sounds great...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ufvFgxTyHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi
> You can run SQL Server Profiler to see what is going on behind the scenes
> when you delete the LOGIN via EM
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
>

How to drop login

Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
MartinBehind the scenes, EM is dropping the user from the databases to which (s)he
had been granted access, and then it drops the login. To script it, you
would have to loop through all DB's for that user and do the drops, followed
by sp_droplogin.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
Martin|||Hi
You can run SQL Server Profiler to see what is going on behind the scenes
when you delete the LOGIN via EM
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to drop the login, the login should be revoked the DB access before issue
> the sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||oh, no shortcut...bad to know the fact... :p
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:um7DtsTyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Behind the scenes, EM is dropping the user from the databases to which
> (s)he
> had been granted access, and then it drops the login. To script it, you
> would have to loop through all DB's for that user and do the drops,
> followed
> by sp_droplogin.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to
> drop the login, the login should be revoked the DB access before issue the
> sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||woo, this absolutely new to me.....sounds great...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ufvFgxTyHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi
> You can run SQL Server Profiler to see what is going on behind the scenes
> when you delete the LOGIN via EM
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
>

How to drop login

Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
MartinBehind the scenes, EM is dropping the user from the databases to which (s)he
had been granted access, and then it drops the login. To script it, you
would have to loop through all DB's for that user and do the drops, followed
by sp_droplogin.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
Martin|||Hi
You can run SQL Server Profiler to see what is going on behind the scenes
when you delete the LOGIN via EM
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to drop the login, the login should be revoked the DB access before issue
> the sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||oh, no shortcut...bad to know the fact... :p
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:um7DtsTyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Behind the scenes, EM is dropping the user from the databases to which
> (s)he
> had been granted access, and then it drops the login. To script it, you
> would have to loop through all DB's for that user and do the drops,
> followed
> by sp_droplogin.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to
> drop the login, the login should be revoked the DB access before issue the
> sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||woo, this absolutely new to me.....sounds great...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ufvFgxTyHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi
> You can run SQL Server Profiler to see what is going on behind the scenes
> when you delete the LOGIN via EM
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> when i execute the sp, EXEC master..sp_droplogin 'Martin'
>> it returns error:
>> Login 'Martin' is aliased or mapped to a user in one or more database(s).
>> Drop the user or alias before dropping the login.
>> I know that the user 'Martin' has been granted to different DB. If I have
>> to drop the login, the login should be revoked the DB access before issue
>> the sp_droplogin.
>> So, is there a command than can drop the login directly, i mean no matter
>> there are any DB associated with the login?
>> Just similar to the Enterprise manager, under Security -> Logins ->
>> highlight the login and right click "delete"
>> It will remove all associated database with the login and then drop the
>> login.
>> Thanks in advance!
>> Martin
>>
>

how to drop erroneous user from db in 2005

Hi All
i have a row in sysusers table with name 'analyst'. The same login exist in
master..syslogins table but sid's are different. It likely happened when
somebody loaded db but I really don't know.
In 2000 I would remove this row from sysusers table. In 2005 I can't do
that. Which essentially leaves me with option not to use 'analyst' login at
all becase it works in some db's and not others.
What should I do in this situation? How can I remove this erroneous row
which does not have matching sid in master..syslogins table?
Hi
Have you checked that the user is not orphaned with EXEC
sp_change_users_login 'report' ?
In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
John
John
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
> Hi All
> i have a row in sysusers table with name 'analyst'. The same login exist
> in
> master..syslogins table but sid's are different. It likely happened when
> somebody loaded db but I really don't know.
> In 2000 I would remove this row from sysusers table. In 2005 I can't do
> that. Which essentially leaves me with option not to use 'analyst' login
> at
> all becase it works in some db's and not others.
> What should I do in this situation? How can I remove this erroneous row
> which does not have matching sid in master..syslogins table?
|||Hi John
It is orphaned user. I just did not know how to handle it other than remove
it in '2000' fashion. I will try your way tomorrow.
Thank you, Gene.
"John Bell" wrote:

> Hi
> Have you checked that the user is not orphaned with EXEC
> sp_change_users_login 'report' ?
> In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
> use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
> John
> John
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
>
>
|||Hi Gene
You should be able use the same methods in 2005 as you used in SQL 2000 see
http://support.microsoft.com/kb/274188/
John
"Gene." wrote:
[vbcol=seagreen]
> Hi John
> It is orphaned user. I just did not know how to handle it other than remove
> it in '2000' fashion. I will try your way tomorrow.
> Thank you, Gene.
> "John Bell" wrote:

how to drop erroneous user from db in 2005

Hi All
i have a row in sysusers table with name 'analyst'. The same login exist in
master..syslogins table but sid's are different. It likely happened when
somebody loaded db but I really don't know.
In 2000 I would remove this row from sysusers table. In 2005 I can't do
that. Which essentially leaves me with option not to use 'analyst' login at
all becase it works in some db's and not others.
What should I do in this situation? How can I remove this erroneous row
which does not have matching sid in master..syslogins table?Hi
Have you checked that the user is not orphaned with EXEC
sp_change_users_login 'report' ?
In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
John
John
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
> Hi All
> i have a row in sysusers table with name 'analyst'. The same login exist
> in
> master..syslogins table but sid's are different. It likely happened when
> somebody loaded db but I really don't know.
> In 2000 I would remove this row from sysusers table. In 2005 I can't do
> that. Which essentially leaves me with option not to use 'analyst' login
> at
> all becase it works in some db's and not others.
> What should I do in this situation? How can I remove this erroneous row
> which does not have matching sid in master..syslogins table?|||Hi John
It is orphaned user. I just did not know how to handle it other than remove
it in '2000' fashion. I will try your way tomorrow.
Thank you, Gene.
"John Bell" wrote:
> Hi
> Have you checked that the user is not orphaned with EXEC
> sp_change_users_login 'report' ?
> In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
> use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
> John
> John
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
> > Hi All
> > i have a row in sysusers table with name 'analyst'. The same login exist
> > in
> > master..syslogins table but sid's are different. It likely happened when
> > somebody loaded db but I really don't know.
> > In 2000 I would remove this row from sysusers table. In 2005 I can't do
> > that. Which essentially leaves me with option not to use 'analyst' login
> > at
> > all becase it works in some db's and not others.
> > What should I do in this situation? How can I remove this erroneous row
> > which does not have matching sid in master..syslogins table?
>
>|||Hi Gene
You should be able use the same methods in 2005 as you used in SQL 2000 see
http://support.microsoft.com/kb/274188/
John
"Gene." wrote:
> Hi John
> It is orphaned user. I just did not know how to handle it other than remove
> it in '2000' fashion. I will try your way tomorrow.
> Thank you, Gene.
> "John Bell" wrote:
> > Hi
> >
> > Have you checked that the user is not orphaned with EXEC
> > sp_change_users_login 'report' ?
> >
> > In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
> > use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
> >
> > John
> >
> > John
> >
> >
> > "Gene." <Gene@.discussions.microsoft.com> wrote in message
> > news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
> > > Hi All
> > > i have a row in sysusers table with name 'analyst'. The same login exist
> > > in
> > > master..syslogins table but sid's are different. It likely happened when
> > > somebody loaded db but I really don't know.
> > > In 2000 I would remove this row from sysusers table. In 2005 I can't do
> > > that. Which essentially leaves me with option not to use 'analyst' login
> > > at
> > > all becase it works in some db's and not others.
> > > What should I do in this situation? How can I remove this erroneous row
> > > which does not have matching sid in master..syslogins table?
> >
> >
> >