How to trace sp_addlogin

Dear all,
I have been trying to use SQL Profiler to trace login
creation. Can someone tell me how do I get the login names
that were created? Trace did not display the detail info
of sp_addlogin.
Thanks,
Joe
You can't get it from Profiler. If it sees password in the text it won't
display it.
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
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:c61d01c47a41$709fcbc0$a501280a@.phx.gbl...
> Dear all,
> I have been trying to use SQL Profiler to trace login
> creation. Can someone tell me how do I get the login names
> that were created? Trace did not display the detail info
> of sp_addlogin.
> Thanks,
> Joe
|||Other than SQL profiler, is there any work around? I only
want to prove the new login creation.
Thanks in advance
>--Original Message--
>You can't get it from Profiler. If it sees password in
the text it won't
>display it.
>--
>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
>"Joe" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:c61d01c47a41$709fcbc0$a501280a@.phx.gbl...
names
>
>.
>
|||Execute sp_helplogins or query the master..syslogins table.
-Sue
On Thu, 5 Aug 2004 06:24:29 -0700, "Joe"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Other than SQL profiler, is there any work around? I only
>want to prove the new login creation.
>Thanks in advance
>the text it won't
>message
>names
|||Not poossible with the current set of tools.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:c61d01c47a41$709fcbc0$a501280a@.phx.gbl...
> Dear all,
> I have been trying to use SQL Profiler to trace login
> creation. Can someone tell me how do I get the login names
> that were created? Trace did not display the detail info
> of sp_addlogin.
> Thanks,
> Joe
|||Try using sp_trace_setevent and use event number 104 Audit Add/Drop Login
"Occurs when a SQL Server Login is added or removed; for sp_addlogin and
sp_droplogin. Also check out Microsoft article "SQL Server 2000 Auditing".
It details how to use Profiler to audit SQL Server.
Thanks,
Val
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:c61d01c47a41$709fcbc0$a501280a@.phx.gbl...
> Dear all,
> I have been trying to use SQL Profiler to trace login
> creation. Can someone tell me how do I get the login names
> that were created? Trace did not display the detail info
> of sp_addlogin.
> Thanks,
> Joe

how to tell if a login has already been created

I want to create a script that will run and create a predetermined set of logins AND dbrolemembership but only if they do not presently exist in the database. I have played around with sp_addlogin and sp_addrolemember. I then checked the sysusers and syslogins table to determine the existence. I cannot get a row to insert into sysusers. Can someone elighten me on where the login & rolemembership is stored. I also spent some time looking for a proc that might give me what i need.

any direction or comments are appreciated.

MikeRole membership is stored in sysusers table of each database (except for tempdb) and login info in master.dbo.syslogins. You right on track based on sp's and tables you mentioned. All you need to do is script those sp's (sp_addlogin and sp_addrolemember) in your QA and you'll have all the answers yourself.|||Thanks for the response.

I have written the scritps to create the users and add them to the correct roles. I would like to test to see if the user and /or role(s) are already existing when I run the script.

PRINT '===> Add logins'
EXEC sp_addlogin 'pubuser','pubuser'
EXEC sp_addlogin 'opsuser','opsuser'

PRINT '===> Add user, attach login and assign permissions for users'
USE Main
EXEC sp_grantdbaccess 'pubuser'
EXEC sp_addrolemember 'db_datareader','pubuser'
EXEC sp_addrolemember 'db_datawriter','pubuser'

EXEC sp_grantdbaccess 'opsuser'
EXEC sp_addrolemember 'db_datareader','opsuser'
EXEC sp_addrolemember 'db_datawriter','opsuser'

The issue / question is when I select * from syslogins I can see the pubuser and opsuser. However, I cannot see a cooresponding entry on sysusers. Still missing a piece somewhere?

mike|||I just ran your script and replaces Main with my database, and when I do a SELECT from sysusers of that database I see both users added. Did I miss anything?

And of course, you'd do something like "if not exist (select1 from master.dbo.syslogins where name = <new_login>) exec sp_addlogin <...>" to validate the presence or abscence of a login, same with users, etc.|||I was using the master sysusers table.

Thanks

How to Synchronize SQL Server Login SIDs

Hi everyone...

On two instances of SQL Server 2005 I am using the same SQL login names, but the SID is different for each one. I would like to synchronize these IDs so that when I restore the production database to the developement database I don't have to remove and re-add the logins.

Is there a quick and easy way to do this?

Thanks,

Forch

You can create a login with the same SID on the second server using the SDI = sid option on CREATE LOGIN DDL (http://msdn2.microsoft.com/en-us/library/ms189751.aspx).

-Raul Garcia

SDE/T

SQL Server Engine

|||While it will take a lot of manual effort, it will do the trick. Thanks!

How to Synchronize SQL Server Login SIDs

Hi everyone...

On two instances of SQL Server 2005 I am using the same SQL login names, but the SID is different for each one. I would like to synchronize these IDs so that when I restore the production database to the developement database I don't have to remove and re-add the logins.

Is there a quick and easy way to do this?

Thanks,

Forch

You can create a login with the same SID on the second server using the SDI = sid option on CREATE LOGIN DDL (http://msdn2.microsoft.com/en-us/library/ms189751.aspx).

-Raul Garcia

SDE/T

SQL Server Engine

|||While it will take a lot of manual effort, it will do the trick. Thanks!

how to surpress a server error msg?

background sql2k on nt5.
when using stored procedure sp_helprotect against a login. I've got the
following msg in query analyzer messages tab.
Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.
is it possible to surpress that msg and just display the Grid tab?
thank you.
steveThat would be a client issue, but I assume that you are using QA and it does
n't have any awareness of any
particular stored procedures; it just displays whatever error comes back. On
e option can be to copy the source
code for the proc and modify it so it doesn't return that error message (I a
ssume that there's a RAISERROR
with that error somewhere in the source code).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message news:%23xdrpj0PEHA.640@.TK2MSFTN
GP12.phx.gbl...
> background sql2k on nt5.
> when using stored procedure sp_helprotect against a login. I've got the
> following msg in query analyzer messages tab.
> Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
> There are no matching rows on which to report.
>
> is it possible to surpress that msg and just display the Grid tab?
> thank you.
>
> steve
>|||well, i'd rather not to change the system stored procedure since that would
mean i have to change that particular sp on all servers!
i'd prefer a solution to control the display.
i was calling the sp thru SQL pass thru in Access and return the query
result to display in an Access report. (the query set, not the error msg)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OExOyt0PEHA.2236@.TK2MSFTNGP10.phx.gbl...
> That would be a client issue, but I assume that you are using QA and it
doesn't have any awareness of any
> particular stored procedures; it just displays whatever error comes back.
One option can be to copy the source
> code for the proc and modify it so it doesn't return that error message (I
assume that there's a RAISERROR
> with that error somewhere in the source code).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:%23xdrpj0PEHA.640@.TK2MSFTNGP12.phx.gbl...
>|||As this is a client issue, I suggest you post the question to an Access grou
p. there might be some option in
Access to suppress error messages...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message news:eiigD40PEHA.3944@.tk2msftng
p13.phx.gbl...
> well, i'd rather not to change the system stored procedure since that woul
d
> mean i have to change that particular sp on all servers!
> i'd prefer a solution to control the display.
> i was calling the sp thru SQL pass thru in Access and return the query
> result to display in an Access report. (the query set, not the error msg)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OExOyt0PEHA.2236@.TK2MSFTNGP10.phx.gbl...
> doesn't have any awareness of any
> One option can be to copy the source
> assume that there's a RAISERROR
> news:%23xdrpj0PEHA.640@.TK2MSFTNGP12.phx.gbl...
>|||> well, i'd rather not to change the system stored procedure since that
would
> mean i have to change that particular sp on all servers!
What if you found a more serious error in your stored procedure? You should
be setting up your servers so that deploying changes is not so convoluted
and difficult that you find yourself looking for more convenient
workarounds. IMHO.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

how to store encrypted data in sql database

hai everybody,

I have a login page in asp.net with the details being stored in the database. In the table at present i am storing the password in plain text field.

Is there any possible for me to store the data in any encrypted format or some format so that no one will be able to view the password.

And while returning back to the webpage i want them in the plain text . . . !

Is there any possibility for this

kindly help at the earliest

thanks in advance

sasidar::Is there any possibility for this

Sure. Just encrypt the data before writing it into the server, decrypt it after reading it.

note, though, that what you do here is NOT safe. Why do you ever want to decrypt the password? Use a hash and compare the hashed values.|||The best solution is to use a one way hash created in ASP.NET code, and then whenever you want to check the entered password you create a hashed copy of the password entered, and then compare it to the hash stored in the database.

private string CreatePasswordHash(string pwd,string salt)
{
string saltAndPwd=string.Concat(pwd,salt);
string hashedPwd=FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd,"SHA1");
return hashedPwd;
}

The salt string is just a string used to ensure that each password when encrypted will be unique (so if two people have a password of "password" the hashes will not be the same.

This way, really no one, not even you as the administrator of teh system, will be able to read the password (this is a good thing). You can of course create a method to reset the password.|||Steve Wyncoop of theSQL Server Worldwide User's Group is ga-ga overXP_CRYPT. I've not used the product, but it looks like it simplifies saving encrypted data to SQL Server.

Don|||Thanks for those who replied me,

But there is one more doubt for me. If i am using the hashing technique then we cannot reveal the password in the plain text format. Suppose if there is a situation where i require the password to known how to get back the plain text format. ?

Through my readings i came to conclusion that it cannot be done. Is it So ? In that case is there any other technique so that i can encrypt the password and then decrypt the password whenever required

Awaiting for the solutions :

Thanks in advance

Sasidar|||You should work out a way that you never need to get to the decrypted password. People often use the same password for lots of things. If a user uses their one password on your site, it is better that even you never be able to get to that password.

In systems I write, we have a system in place where a password can be reset, but we have no way to reverse the one way hash. This way, even if the database is compromised, user's passwords will not be exposed. Your users will appreciate it.|||Thanks for ur reply ,

but still i have a doubt , say suppose my client or user forget his pasword , then what should i do so that i can change the password . If i am going to change contecnts directly into the database then the plain text will be changed and i hope that will not be fine. Is there any solution for this

Awaiting for the solution

Thanks

Bye

Sasidar|||The way tou handle that is to create an administrative page that allows you to reset the password (you have a page that encrypts a default password EXACTLY the same way that your normal password hashing works).|||sorry mr.douglas

i am not able to understand

can u give me more details please

Thanking u

sasidar|||I really can't, without providing all the code to do it, and I do not have time for that.

All you need to do is do the exact same one-way hashing on a default password (for instance, "password") and save that as the users new password, and in some way notify the user of the new password.

This book:

http://www.microsoft.com/mspress/books/6501.asp

has lots of information on how to do this kind of thing.

How to stop Login attempts from cerain IP's

I have a sql box which is getting loads of login attempts from the sam ip
address. I cannot see where to block ip addresses, windows firewall does not
have usch an option - any body know how. or should i use some other host
based protection?You could configure SSL to sucure your communications.
NetHT19.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
NetHT19.asp
HTH. Ryan
"Martin" <Martin@.discussions.microsoft.com> wrote in message
news:3D2EDA46-051B-4715-8491-DBD46D69058B@.microsoft.com...
>I have a sql box which is getting loads of login attempts from the sam ip
> address. I cannot see where to block ip addresses, windows firewall does
> not
> have usch an option - any body know how. or should i use some other host
> based protection?|||While I haven't looked into myself at all (so I'm no expert), I would be
looking in the direction of IPSec
(http://www.microsoft.com/technet/it...ec/default.mspx).
There's much there about encryption, which is not what you're after
exactly, but wrapped up in IPSec is also the concept of allowing certain
traffic (protocols & ports) to pass, be secured (ie. encrypted) or
blocked. I'd be looking at setting up some kind of IPSec filter that
blocks the IP (or range of IPs) in question. There's a whole lot of
info on the web about IPSec; the technet link above is a good place to
start in relation to IPSec & Windows.
*mike hodgson*
http://sqlnerd.blogspot.com
Martin wrote:

>I have a sql box which is getting loads of login attempts from the sam ip
>address. I cannot see where to block ip addresses, windows firewall does no
t
>have usch an option - any body know how. or should i use some other host
>based protection?
>