Re: How to give domain users SQL login permissions when SQL is on anot

Hi Scott,

You correctly determined my problem. I notice there is a Default Schema and
a Database Role Membership, would I need to set either/both? I have noticed
that certain configs are only available for Domain Users and not Domain User
Groups.

Creating a login for a user and giving the permissions as you suggested does
work, thank you.

Regards

"Scott Schwarze" <ScottSchwarze@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C6A7F00E-76B1-42BB-913C-48F6DA491A89@xxxxxxxxxxxxxxxx
I believe I understand the problem. Windows or SQL users in this case does
not matter. The point is you are accessing data from two DB thur a view.
The
users must have at least read permission on both DB's to see data from the
view.

The lowest security is grant them select permission on all the tables that
are ref. in the view. But if your new to SQL Server security, start by
giving
an user db_datareader in the main security tab under your SQL Server.
Right
click on a user/test person under security --> Logins --> someuser -->
User
Mapping --> DB1 and DB2 make them a db_datareader.
--
Scott Schwarze
Microsoft Certified Trainer
MCIPT: Database Administrator
MCTS: SQL Server 2005


"Terry" wrote:

The Problem:

I have an intranet with a Windows Server 2003se PDC, another Windows
Server
2003se running SQL2005se and 10 XPpro clients. The SQL Server has 2
databases, DB1 and DB2. DB2 I use for developing views of the data in DB1
along with a local table or two.

On my development machine (XPpro) I can connect to any database, but then
my
login has a lot of permissions and belongs to the administrators group in
the domain. The other users can connect to DB1 because the permissions
were
setup by an application that is run.

I have created an Excel spread*** that connects to a View in DB2 and
displays the result from that View. The View draws data from tables in
DB1.

I needed to allow the other network users to view the data in the Excel
spread*** so moved it onto a network share along with the Excel .odc
connection file.

I created a login on SQL Server for DB2 for a domain group
(DOMNAME\SQLUSERS) and then created a SQL Datasource on a client machine
(a
successful connection could be established).

When the Excel spread*** is opened on that client and the connection is
refreshed I get an error 4060 and an indication that the default database
(DB2) could not be connected. There is also an indication that DB1 may
also
not be available.

I'm guessing that I need to get some login permissions correct in SQL.

To cap: Both databases are in the same SQL Server. The View is in one
database and gets data from tables in the other. The users are domain
users
and the SQL server runs on another server which is not a domain
controller.

How do I get this setup please?

Regards








.