HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.newusers » SSMSE connecting remotely permissions question
Topic: SSMSE connecting remotely permissions question
Hi All
I am connecting remotely using SSMSE to a SSE on a W2003 server box using
WINDOWS authentication (NOT SQL Authentication)
This all works fine except I would like to know the "best" WINDOWS user
group for this user to be a member of ....
There is a local username on the server and notebook that match
If the server user is a member of the Users group then I can connect to the
server and see a list of databases. If I then double click on one of these I
cannot get any further ie get at the tables.
If the server user is a member of the Administartors group then I can gain
access to the tables
Is there a better "lower" group than Administrators that will give me access
to view and edit the tables ?
Thanks in advance for your help
Andrew
Replies below ↓
Replies
Re: SSMSE connecting remotely permissions question
Hello Andrew,
Is this a domain environment? Are the SQL Server server and your users
members of this domain?
It seems your Users group has Connect permission (at the server level) which
makes them able to connect to your SQL Server instance; however for database
access, you need to give them connect, read and write permissions on those
databases. If you want your Users group to be able to connect to your Test
database, you can use the following:
If you want them to be able to see the rows in tables, then:
use [test_db]
GO
GRANT SELECT TO [test_user]
Note:
Before using the above commands, you should ensure this Users group Login
has added users list of the mentioned database. If you want to perform this
action using T-SQL, then:
USE [test_db]
GO
CREATE USER [test_group] FOR LOGIN [EKREM-PC\test_group]
GO
You can perform this action using the interface (SSMSE) as well. For this
purpose, open SSMS and connect to your SQL Server instance as a System
Administrator.
Go to your database and add this Users group to the Users of this database
(from the Security node under your database)
Right click on the database that you want your Users group to be able to
perform some actions like SELECT, INSERT etc. and go to Properties of this
database. Go to Permissions at the left list and add your user to the list
at the right upper side of the window and from the lower side window assign
that group necessary permissions.
--
Ekrem Önsoy
"Andrew Kennard" <b@a.com> wrote in message
news:uPUjMoNwIHA.1768@TK2MSFTNGP03.phx.gbl...
> Hi All
>
> I am connecting remotely using SSMSE to a SSE on a W2003 server box using
> WINDOWS authentication (NOT SQL Authentication)
>
> This all works fine except I would like to know the "best" WINDOWS user
> group for this user to be a member of ....
>
> There is a local username on the server and notebook that match
>
> If the server user is a member of the Users group then I can connect to
> the server and see a list of databases. If I then double click on one of
> these I cannot get any further ie get at the tables.
>
> If the server user is a member of the Administartors group then I can gain
> access to the tables
>
> Is there a better "lower" group than Administrators that will give me
> access to view and edit the tables ?
>
> Thanks in advance for your help
>
> Andrew
>
Re: SSMSE connecting remotely permissions question
Thanks for the prompt resposnses.
The server and terminals are in a small workgroup and i'm using windows
authentication to access the SSE on the server
I've not tried the other windows usergroups yet eg power users but if I
can't get the combination I want I'll look into setting the SQL permissions
as you suggest below
Thanks again
Andrew
"Ekrem Önsoy" <ekrem@compecta.com> wrote in message
news:63CD32CE-3759-44D7-AFEB-C3184B0FBC79@microsoft.com...
> Hello Andrew,
>
> Is this a domain environment? Are the SQL Server server and your users
> members of this domain?
>
> It seems your Users group has Connect permission (at the server level)
> which makes them able to connect to your SQL Server instance; however for
> database access, you need to give them connect, read and write permissions
> on those databases. If you want your Users group to be able to connect to
> your Test database, you can use the following:
>
> If you want them to be able to see the rows in tables, then:
> use [test_db]
> GO
> GRANT SELECT TO [test_user]
>
> Note:
> Before using the above commands, you should ensure this Users group Login
> has added users list of the mentioned database. If you want to perform
> this action using T-SQL, then:
> USE [test_db]
> GO
> CREATE USER [test_group] FOR LOGIN [EKREM-PC\test_group]
> GO
>
> You can perform this action using the interface (SSMSE) as well. For this
> purpose, open SSMS and connect to your SQL Server instance as a System
> Administrator.
>
> Go to your database and add this Users group to the Users of this database
> (from the Security node under your database)
>
> Right click on the database that you want your Users group to be able to
> perform some actions like SELECT, INSERT etc. and go to Properties of this
> database. Go to Permissions at the left list and add your user to the list
> at the right upper side of the window and from the lower side window
> assign that group necessary permissions.
>
> --
> Ekrem Önsoy
>
>
>
> "Andrew Kennard" <b@a.com> wrote in message
> news:uPUjMoNwIHA.1768@TK2MSFTNGP03.phx.gbl...
>> Hi All
>>
>> I am connecting remotely using SSMSE to a SSE on a W2003 server box using
>> WINDOWS authentication (NOT SQL Authentication)
>>
>> This all works fine except I would like to know the "best" WINDOWS user
>> group for this user to be a member of ....
>>
>> There is a local username on the server and notebook that match
>>
>> If the server user is a member of the Users group then I can connect to
>> the server and see a list of databases. If I then double click on one of
>> these I cannot get any further ie get at the tables.
>>
>> If the server user is a member of the Administartors group then I can
>> gain access to the tables
>>
>> Is there a better "lower" group than Administrators that will give me
>> access to view and edit the tables ?
>>
>> Thanks in advance for your help
>>
>> Andrew
>>
>
Re: SSMSE connecting remotely permissions question
On May 28, 11:46=A0am, "Andrew Kennard" <b...@a.com> wrote:
> Hi All
>
> I am connecting remotely using SSMSE to a SSE on a W2003 server box using
> WINDOWS authentication (NOT SQL Authentication)
>
> This all works fine except I would like to know the "best" WINDOWS user
> group for this user to be a member of ....
>
> There is a local username on the server and notebook that match
>
> If the server user is a member of the Users group then I can connect to th=
e
> server and see a list of databases. If I then double click on one of these=
I
> cannot get any further ie get at the tables.
>
> If the server user is a member of the Administartors group then I can gain=
> access to the tables
>
> Is there a better "lower" group than Administrators that will give me acce=
ss
> to view and edit the tables ?
>
> Thanks in advance for your help
>
> Andrew
The default is the built in local administrators group.
However this can be changed in the Enterprise Manager under Security
and "Logins". You can add a new user or group and it will let you
select them from the local server or from any domain that it can
connect to.