Domain group accounts

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Owe Armandt - 06 Oct 2005 13:58 GMT

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

Reply to this Message

Dan Guzman - 06 Oct 2005 14:22 GMT

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

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

>I have a problem with database access that I would like to sort out.
>
[quoted text clipped - 14 lines]
>
> Owe

Reply to this Message

Owe Armandt - 06 Oct 2005 20:42 GMT

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

> Try running xp_logininfo to report the permission path(s) for the account.
> For example
[quoted text clipped - 33 lines]
>>
>> Owe

Reply to this Message