Char and Varchar: login

Showing posts with label login. Show all posts

Sunday, March 25, 2012

Check NT login existance

How do you check for the existance of an NT login (in the domain not on sql
server itself) in a script that uses sp_grantlogin?You can use SUSER_SID:
IF SUSER_SID('MyDomain\MyAccount') IS NULL
PRINT 'account does not exist'
ELSE
PRINT 'account exists'
Happy Holidays
Dan Guzman
SQL Server MVP
"Steve Morris" <SteveMorris@.discussions.microsoft.com> wrote in message
news:FB40D121-995B-42DE-B735-7597C4793404@.microsoft.com...
> How do you check for the existance of an NT login (in the domain not on
> sql
> server itself) in a script that uses sp_grantlogin?

Check NT login existance

How do you check for the existance of an NT login (in the domain not on sql
server itself) in a script that uses sp_grantlogin?
You can use SUSER_SID:
IF SUSER_SID('MyDomain\MyAccount') IS NULL
PRINT 'account does not exist'
ELSE
PRINT 'account exists'
Happy Holidays
Dan Guzman
SQL Server MVP
"Steve Morris" <SteveMorris@.discussions.microsoft.com> wrote in message
news:FB40D121-995B-42DE-B735-7597C4793404@.microsoft.com...
> How do you check for the existance of an NT login (in the domain not on
> sql
> server itself) in a script that uses sp_grantlogin?

Check NT login existance

Check login

Hello, everyone. I am trying to create a procedure that checks a user's login, in the database. The login is based on e-mail address and password.

How do I construct the stored procedure to check if the username and password are correct?

Thanks,

Antonio

You could either use a stored procedure, or a function.

Code Snippet

CREATE PROCEDURE dbo.VerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

(or)

CREATE FUNCTION dbo.fnVerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)
RETURNS varchar(10)
AS

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

|||

Just a couple of points to make about password storage in databases.

You should never store a password in a clear (unencrypted) form in a database. Preferably you should not even store it in a decryptable form, but rather a value managled by a one-way hash. SQL Server 2005 now has the HashBytes function which will perform a cryptographically valid hash operation.

It is also recommended that you not perform a straightforward hashing but rather salt the value before hashing with another value. This means adding another known piece of information to the value before hashing the combination. This has a 2-fold advantage:

It makes a brute force attack of the hash algorithm with all the possible values harder (basically impossible if the salt is different for each password). Be aware there are projects on the internet where people are building hash lists for all the characters strings up to a certain length composed of a standard set of characters (mainly alpha/digit - upper case only) for some standard hash algorithms. Good reason for using longer passwords (12+ characters) and at least one unusual character. If you use a different salt for each user (I normally use something based upon the primary ID in the User Table) then it prevents the insider attack of moving the password hash from a user whose password is known to another allowing access to the system as that user (restoring the old hash removes signs of the hack). If the salt is held as a field in the table they can move that too (though a unique constraint might make that more difficult).sql

Friday, February 10, 2012

Chaning login name

There have been some major changes here at work, and in order to keep up we
need to change some of the Group names in Active directory. Will the
changes that are made in Active Directory cascade down into SQL Server?
What is the protocol for this type of change?
Thanks,
DrewNo. On 2005, you can rename a login (to reflect the name change) using ALTER LOGIN.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Drew" <drew.laing@.swvtc.dmhmrsas.virginia.gov> wrote in message
news:OQx$0ftqIHA.5096@.TK2MSFTNGP02.phx.gbl...
> There have been some major changes here at work, and in order to keep up we need to change some of
> the Group names in Active directory. Will the changes that are made in Active Directory cascade
> down into SQL Server? What is the protocol for this type of change?
> Thanks,
> Drew
>|||What is the protocol for SQL Server 2000?
Thanks,
Drew
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C42BDB9F-51A2-424C-8C25-58B905FB94E4@.microsoft.com...
> No. On 2005, you can rename a login (to reflect the name change) using
> ALTER LOGIN.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Drew" <drew.laing@.swvtc.dmhmrsas.virginia.gov> wrote in message
> news:OQx$0ftqIHA.5096@.TK2MSFTNGP02.phx.gbl...
>> There have been some major changes here at work, and in order to keep up
>> we need to change some of the Group names in Active directory. Will the
>> changes that are made in Active Directory cascade down into SQL Server?
>> What is the protocol for this type of change?
>> Thanks,
>> Drew
>|||You can't change the name for a login in 2000. You'd have to delete and re-create the login...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Drew" <drew.laing@.swvtc.dmhmrsas.virginia.gov> wrote in message
news:%234%233Q7tqIHA.2292@.TK2MSFTNGP03.phx.gbl...
> What is the protocol for SQL Server 2000?
> Thanks,
> Drew
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:C42BDB9F-51A2-424C-8C25-58B905FB94E4@.microsoft.com...
>> No. On 2005, you can rename a login (to reflect the name change) using ALTER LOGIN.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Drew" <drew.laing@.swvtc.dmhmrsas.virginia.gov> wrote in message
>> news:OQx$0ftqIHA.5096@.TK2MSFTNGP02.phx.gbl...
>> There have been some major changes here at work, and in order to keep up we need to change some
>> of the Group names in Active directory. Will the changes that are made in Active Directory
>> cascade down into SQL Server? What is the protocol for this type of change?
>> Thanks,
>> Drew
>>
>

Changing user in sql query analyzer

Hi,
In SQL Server 2000's SQL Query Analyzer, how do I change the login account?
I mean if I am using UsrA in the Analyzer, how do I switch to UserB by SQL
command?
Thanks for help.
JasonJason
I'm afraid you cannot in SQL Server 2000, unless open a new connection .
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23tox$%23FFHHA.3288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In SQL Server 2000's SQL Query Analyzer, how do I change the login
> account?
> I mean if I am using UsrA in the Analyzer, how do I switch to UserB by SQL
> command?
> Thanks for help.
>
> Jason
>|||> I mean if I am using UsrA in the Analyzer, how do I switch to UserB by SQL command?
In 2000, you only have the SETUSER command, but it changes the user context, not the login context.
In 2005, you have the EXECUTE AS command, and you decide whether to change user or login context.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23tox$%23FFHHA.3288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In SQL Server 2000's SQL Query Analyzer, how do I change the login account?
> I mean if I am using UsrA in the Analyzer, how do I switch to UserB by SQL command?
> Thanks for help.
>
> Jason
>

Changing user in sql query analyzer

Hi,
In SQL Server 2000's SQL Query Analyzer, how do I change the login account?
I mean if I am using UsrA in the Analyzer, how do I switch to UserB by SQL
command?
Thanks for help.
JasonJason
I'm afraid you cannot in SQL Server 2000, unless open a new connection .
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23tox$%23FFHHA.3288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In SQL Server 2000's SQL Query Analyzer, how do I change the login
> account?
> I mean if I am using UsrA in the Analyzer, how do I switch to UserB by SQL
> command?
> Thanks for help.
>
> Jason
>|||> I mean if I am using UsrA in the Analyzer, how do I switch to UserB by SQL
command?
In 2000, you only have the SETUSER command, but it changes the user context,
not the login context.
In 2005, you have the EXECUTE AS command, and you decide whether to change u
ser or login context.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23tox$%23FFHHA.3288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In SQL Server 2000's SQL Query Analyzer, how do I change the login account
?
> I mean if I am using UsrA in the Analyzer, how do I switch to UserB by SQL
command?
> Thanks for help.
>
> Jason
>

Sunday, March 25, 2012

Check NT login existance

Check NT login existance

Check NT login existance

Check login

Friday, February 10, 2012

Chaning login name

Changing user in sql query analyzer

Changing user in sql query analyzer

Changing user in sql query analyzer

Char and Varchar

Blog Archive

About Me