Domain group accounts

I have a problem with database access that I would like to sort out.
1. I gave a Windows 2003 server with SQL 2000 (SP3)
2. I decided to create different Windows Groups and add Windows accounts to
them to access different databases
3. This is where it starts giving me problem
4. I noticed that user belonging to one group (ie. database1 access) also
had access to database2
4. To test this I created a group called Test and addedd this to
Security/logins in Enterprise Manager and assigned this group access to
database1
5. I placed my own account into this group and that gave me access to all
other databases as well, even though the group Test is only set to
database1.
What is problem here'
OweTry running xp_logininfo to report the permission path(s) for the account.
For example
EXEC master..xp_logininfo 'MyDomain\Test', 'all'
For a Windows authenticated user to gain access to a database, one of the
following must be true:
- the account was granted database access
- the account is member of a Windows group than was granted database access
- the account is the database owner
- the account is a member of a sysadmin fixed server role
- the guest account is enabled in the database
Hope this helps.
Dan Guzman
SQL Server MVP
"Owe Armandt" <owe.armandt@.visma.se> wrote in message
news:ugQDkVnyFHA.2696@.TK2MSFTNGP10.phx.gbl...
>I have a problem with database access that I would like to sort out.
> 1. I gave a Windows 2003 server with SQL 2000 (SP3)
> 2. I decided to create different Windows Groups and add Windows accounts
> to them to access different databases
> 3. This is where it starts giving me problem
> 4. I noticed that user belonging to one group (ie. database1 access) also
> had access to database2
> 4. To test this I created a group called Test and addedd this to
> Security/logins in Enterprise Manager and assigned this group access to
> database1
> 5. I placed my own account into this group and that gave me access to all
> other databases as well, even though the group Test is only set to
> database1.
> What is problem here'
>
> Owe
>|||I helped a bit, I will test further tomorrow.
I found out that I (my windows account) happend to be owner of the database.
I have now changed the owner to 'sa' and then I get access only if the group
I belong to is set to have DB access.
One thing bothers me though, my college do not belong to any group that has
access to any database and still he could access the database that I was the
owner of.
I don't think he is part of admin ro anything, we try to kep our avccounts
clear in order to be as alike the users account as possible.
This is what I will try to check out tomorrow - I will be back tomorrow with
some info
Owe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> skrev i meddelandet
news:OzfN9jnyFHA.464@.TK2MSFTNGP15.phx.gbl...
> Try running xp_logininfo to report the permission path(s) for the account.
> For example
> EXEC master..xp_logininfo 'MyDomain\Test', 'all'
> For a Windows authenticated user to gain access to a database, one of the
> following must be true:
> - the account was granted database access
> - the account is member of a Windows group than was granted database
> access
> - the account is the database owner
> - the account is a member of a sysadmin fixed server role
> - the guest account is enabled in the database
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Owe Armandt" <owe.armandt@.visma.se> wrote in message
> news:ugQDkVnyFHA.2696@.TK2MSFTNGP10.phx.gbl...
>