logins errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

logins errors

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-08-18 : 09:46:56

Hi there we have
Domain\Users logins
and
SQL Logins

In security logins i have windows login
domain\JACK

and sql logins
JACK

If i select domain\JACK and select databases and tick one of them it comes back as
already exits in the current database

If i go to JACK
It says not found in the users collection.

What the best way for me to grant access to the Domain\Jack
Should i leave all databases unchecked and then on JACK just select these.

We use sql logins for one of our applications as it does not work with Windows NT Domain logins.

Thanks

dewacorp.alliances

452 Posts

Posted - 2006-08-18 : 15:34:43

Hi

Check in your database user instead. See if there is domain\JACK in that list. You may need to delete from that or changing the SID instead.

Hope this help.

quote:
Originally posted by TRACEYSQL

Hi there we have
Domain\Users logins
and
SQL Logins

In security logins i have windows login
domain\JACK

and sql logins
JACK

If i select domain\JACK and select databases and tick one of them it comes back as
already exits in the current database

If i go to JACK
It says not found in the users collection.

What the best way for me to grant access to the Domain\Jack
Should i leave all databases unchecked and then on JACK just select these.

We use sql logins for one of our applications as it does not work with Windows NT Domain logins.

Thanks

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-08-19 : 08:23:47

Thanks for replying...
So on the DOMAIN\JACK if databases selected remove these...and
then on sql login JACK select databases.

Then When developer JACK uses DOMAIN\JACK he be able to see and do what he likes with the databases ...

It that correct.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-08-19 : 11:50:55

first verify if domain/jack is aliased as jack in the database
if yes, you may only want to limit the alias/name jack to the sql login jack

when you grantdbaccess to domain/jack, specify as domain/jack also to avoid the confusion, by default when you use enterprise manager to grant database access to a domain user, it strips off the domain name

HTH

--------------------
keeping it simple...

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-08-19 : 12:02:25

Ok i have
DOMAIN\JACK no databases selected.
SQL LOGIN JACK DATABASEA selected.
I login into sql query and i cannot access DATABASEA

I then select SQL LOGIN and select DATABASEA and still cannot access as im in as DOMAIN\JACK.

How do i do the aliais bit.
first verify if domain/jack is aliased as jack in the database
if yes, you may only want to limit the alias/name jack to the sql login jack

when you grantdbaccess to domain/jack, specify as domain/jack also to avoid the confusion, by default when you use enterprise manager to grant database access to a domain user, it strips off the domain name

Thanks..

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-08-19 : 12:05:26

go to query analyzer

use databasename
go

exec sp_grantdbaccess 'domain/jack','domain/jack'
exec sp_grantdbaccess 'jack','jack'

go

--------------------
keeping it simple...

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-08-19 : 12:22:59

i keep seeing this
http://www.data-based-systems.com/Articles/Microsoft%20Security.htm
where is the SQL Security Manager..? Im in EM

So i ran that in query....i ran the above...sp_grant command

Now in the database users i get
Name DOMAIN\JACK Login DOMAIN\JACK
and JACK JACK

In Security Logins both the DOMAIN\JACK is selected with database and the SQL Login selected

So it looks like in EM you cannot do it but running the command you can.

So you can have both...

So that command...is like me going to EM to the database and doing add user .....(i have not got into the quick short cuts in
query yet)....

So to deny them i just run ...which command. sp_denyaccess ..

Is that what the alias means...?

i login as domain\JACK in explorer and can see DATABASE

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-08-19 : 12:25:20

So in EM it strips off the DOMAIN when trying to add the DOMAIN\JACK ...so thats why you get the
User already exists if try to do it in EM..

But doing it at the database user ...it works.........

Thats great.....I did not know that...

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-08-19 : 12:49:47

If you get a chance ....if you can explain the line
first verify if domain/jack is aliased as jack in the database
For me that would be appreciated.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-08-19 : 13:38:36

you can also do the grantdbaccess in EM, but don't do it directly on the security>logins, goto the db>users and add the account there, you will have a textbox for including the domain name

for alias...
1. using em, goto databasename>users
run this in QA

use databasename
go
select user_name() as 'db user name',system_user as 'login'

-- the db user name represents the alias, loginame is the actual account name
you can use a different name for your user inside the database to define the login, or you can create a role for it

I find it quite useful especially in identifying what the login's 'business' in the database (if existing in other databases as well)

--------------------
keeping it simple...

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-08-19 : 18:21:26

Thanks i appreciate your help today

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources