Re: SQL Server 2005 Express Remote connection

Hi Uri

More information.

I have just deleted the login on SQL server for my user (Fred)

I have also deleted the login for Fred from the list of users for my
database.

So Fred no longer exists as a login or as a user in the SQL Server setup.

I have then gone to my workstation and logged in quite happily to my remote
SQL Server on the server using the Windows Authentication for Fred.

I am getting more lost in all this by the hour!!

Surely once I have deleted Fred as a login he should not be able to login to
the database?

Roy


--
Roy Thompson
Independent Developer
Specialist in Sage Accounting solutions (UK)


"Uri Dimant" wrote:

Roy
1. By impersonate you mean log in to SQL server as Login1? Yes?
Yes
2. How do I revoke "View Any Database" from Public? I cannot see any login
called Public.
Public is a role (db,server).

REVOKE VIEW ANY DATABASE to PUBLIC
3) Click connect and then database engine in object explorer and specify
Login and password


You create a login and create a user (within a db) to be mapped to the
login and then grant access to the database , so you say the users do not
see it?
Does the log have GRANT VIEW ANY DATABASE perm?





"Roy Thompson" <rothco@xxxxxxx> wrote in message
news:3C35B3F3-CB5B-4658-97C3-8DEBA2277F0C@xxxxxxxxxxxxxxxx
Hi Uri

Many thanks for that. It was so helpful.

I am afraid though it raises a number of questions which as a newbie I do
not understand:

1. By impersonate you mean log in to SQL server as Login1? Yes?

2. How do I revoke "View Any Database" from Public? I cannot see any login
called Public.

3. How do I register the sever as Login1?



Here is what I want to achieve:

I have created a database logged in to SQL server as administrator on the
main server.

I now want other users to be able to work in that database from their own
PCs

I have set up a new user in the main database area and set their user
mappings to the database.

The problem is that when I login to SQL server from the user's PC I cannot
see the database.

Clearly I am missing something here?

Thanks again for your time

Roy

--
Roy Thompson
Independent Developer
Specialist in Sage Accounting solutions (UK)


"Uri Dimant" wrote:

Roy
It is because you are not member of sysadmin server group and/or those
databases are owned by someone else
However, this works perfectly if the user is the owner of the database.
Look
that …

• Create a new SQL login "login1"
• Create a user named “login1” in master database
• Grant CREATE DATABASE to login1
• While impersonating login1, create a database called “dbteste”
• Revoke CREATE DATABASE permission from login1
• Revoke VIEW ANY DATABASE permission from PUBLIC
• Register this server as login1
• From the “login1” session, expand database tree. Now, you should see
master, tempdb, dbteste
• Grant VIEW ANY DATABASE to PUBLIC
• From the “login1” session, you should see all the databases







"Roy Thompson" <rothco@xxxxxxx> wrote in message
news:C6F70C40-8BC0-41EE-8171-152702CB8C9B@xxxxxxxxxxxxxxxx
Thanks for that Daniel. But the problem is not in connecting remotely.
I
can
obviously connect remotely as I can see the server from Mgmt studio on
the
client.

The problem is how I have got the database configured on the server. I
cannot see the database from the client - just the server and the
system
databases.


--
Roy Thompson
Independent Developer
Specialist in Sage Accounting solutions (UK)


"Daniel Imesch" wrote:

Roy,

http://support.microsoft.com/?scid=kb;en-us;914277&x=9&y=11 could help
you

Daniel


"Roy Thompson" <rothco@xxxxxxx> a ?crit dans le message de
news:2E71D1C3-3728-4045-8C5B-33912D657642@xxxxxxxxxxxxxxxx
Hi

I am a relative newbie to SQL Server

I have set up a SQL Server 2005 Express database on my server and am
trying
to connect to it from a workstation. I did all the stuff about
Surface
configuration, etc. I was still having some problems connecting
through
my
program so I downloaded a copy of SQL Express Management Studio to
my
client.
Now I can connect to the remote database fine. So the problem is not
my
client seeing the server. Mgmt Studio on the client allows me to
logon
using
windows authentication. The trouble is that whilst I can connect
using
my
Windows name I cannot see the database that I want to get into from
the
client. I can see and explore all the system databases but not the
one
I
created on the server!

On the server I have setup the user name and set the user mapping to
point
to my database but when running from Mgmt Studio on the client I do
not
see
the database.

What have I done wrong?

Thanks
--
Roy Thompson
Independent Developer
Specialist in Sage Accounting solutions (UK)







.

Relevant Pages