Read Only User - One Database

How do I create a user, that has rights to only one database, and only
select rights to some views?
I created a login in Enterprise Manager; checked the database access only on
the database the user needs to see; and checked the db_datareader role.
However, logging in as this user I can see and select from other databases.
Under the database I gave the user rights to, I checked only select rights
on the views I want the user to select from. I can still select from
tables, etc.
Obviously, I'm missing something here...Which other databases? Most likely those are databases with
the guest account enabled. If a user doesn't have an account
to log into the database and the guest account is enabled,
the user has access through the guest account and whatever
rights are granted to public and this account. The guest
account cannot be deleted from master or tempdb. It can be
added, delete from other databases.
When you added the user to the db_datareader role, you gave
that user permissions to select from all user tables.
Permissions are cumulative so the user obtains all
permissions through the combination of their individual
account and any groups, roles that they are members of. When
you also gave the individual account select permission on
views, the user ended up with those permissions as well as
select on all user tables.
-Sue
On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:

>How do I create a user, that has rights to only one database, and only
>select rights to some views?
>I created a login in Enterprise Manager; checked the database access only o
n
>the database the user needs to see; and checked the db_datareader role.
>However, logging in as this user I can see and select from other databases.
>Under the database I gave the user rights to, I checked only select rights
>on the views I want the user to select from. I can still select from
>tables, etc.
>Obviously, I'm missing something here...
>|||sue i can't see guest account except master database.. you meant somebody
created it ?
"Sue Hoegemeier" wrote:

> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue
> On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
>|||No...nothing about someone creating the guest account but I
don't know what databases the user is accessing. Which other
databases? System databases or user databases? Is the user a
member of other roles or groups that would give them access
to the other databases?
-Sue
On Thu, 28 Oct 2004 19:53:01 -0700, "Dave"
<Dave@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>sue i can't see guest account except master database.. you meant somebody
>created it ?
>"Sue Hoegemeier" wrote:
>|||I've deleted the guest account from other user DBs and the user I can not
see/connect to those DBs - good.
I've unchecked the db_datareader role and only gave select permissions to 2
views in the DB - that seems to work OK as well.
However, the user can still select from system tables in the database they
are assigned to. Any way to prevent that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue|||The public role is able to select from some of the system tables. All
database user are members of public. That's why the user can select
from some of the system tables.
-Sue
On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:

>I've deleted the guest account from other user DBs and the user I can not
>see/connect to those DBs - good.
>I've unchecked the db_datareader role and only gave select permissions to 2
>views in the DB - that seems to work OK as well.
>However, the user can still select from system tables in the database they
>are assigned to. Any way to prevent that?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>|||Adrian
In additon to Sue's advice ,if you don't want the users be able to select
from system tables , you can hide them by editing SQL Server registration
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
> The public role is able to select from some of the system tables. All
> database user are members of public. That's why the user can select
> from some of the system tables.
> -Sue
> On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
2[vbcol=seagreen]
they[vbcol=seagreen]
>|||That doesn't prevent users from being able to select from
system tables though. All that will do is prevent displaying
them in Enterprise Manager.
-Sue
On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
<urid@.iscar.co.il> wrote:

>Adrian
>In additon to Sue's advice ,if you don't want the users be able to select
>from system tables , you can hide them by editing SQL Server registration
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
>2
>they
>|||Yep, I assumed that all manipulations are done by users in EM.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:qi5bo0lo2223mjjdqcfkdlhvafhlg596i0@.
4ax.com...
> That doesn't prevent users from being able to select from
> system tables though. All that will do is prevent displaying
> them in Enterprise Manager.
> -Sue
> On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
> <urid@.iscar.co.il> wrote:
>
not[vbcol=seagreen]
to[vbcol=seagreen]
>|||I am trying to do the same thing. I have a user that needs access to one of
the databases on the server. I can prevent him from modifying or even seeing
the tables on the other databases with permissions, but I want to be able to
hide the other databases from Enterprise Manager. I am using SQL logins. Can
I do this? It sounds like you were able to do this. I have deleted the guest
account from a number of databases and added his login to only two databases
but the login displays all of the databases, including those with only sa as
the login.
"Adrian Maull (MCP)" wrote:

> I've deleted the guest account from other user DBs and the user I can not
> see/connect to those DBs - good.
> I've unchecked the db_datareader role and only gave select permissions to
2
> views in the DB - that seems to work OK as well.
> However, the user can still select from system tables in the database they
> are assigned to. Any way to prevent that?
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>
>

Read Only ODBC to SQL SERVER 2005

I have a Login that I setup in SQL Server 2005. The only database roles given to the login are db_datareader and public. On SQL 2000 these permissions are sufficient to allow me to setup a read only ODBC connection using this login and creating an import table in Access. When using the login in SQL 2005 and creating an import table, I am able to edit the underlying data, something I am not able to do with a SQL 2000 ODBC connection. Is there something in SQL 2005 that I am unaware of that would allow this login to modify (update) the table?

Assuming you are using SQL Server Authentication, I'd just double check the roles that account is a member of, and what permissions those roles have on the table in question.

If you are using Windows Authentication, make sure your account is NOT a member of the administrators group on the server. If it is, it automatically becomes part of the Server Administrator's role and can basically do anything.

When I run into these things I often find it's something silly that I overlooked. Having another person look at things for you will often help catch the things you miss.

Good Luck

|||

We have 3 peoople looking at it, one of us has exprience going back to SQL 6.5, but no one can figure it out, lol. We are using SQL Auth and we even tried to deny update rights to the user on the table and take away all grant rights. As soon as we connect to a SQL 2005 table in Access using the ODBC, the user can edit whatever. It is strange because with the exact same permissions in 2000, the user is denied the access and it acts like we expect. In 2005, it is just a free for all. I know it is something silly and we are all going to slap our foreheads when we figure it out

|||

We had to give the user db_denydatawriter permissions. It is strange that we had to explicitly give these permissions when we didn't have to do it in SQL 2000, but it worked.

|||

Just a random thought: Does the schema assigned to the sql server login in question match the name of the login? What schema owns the table in question and what schema is the user in?

|||

dbo

|||

I think that may be your answer. I would try creating a seperate schema for the user in question and disassociate the dbo schema from the user.

RDA Pull Problem: Command=PULL Hr=80040E4D Login failed for user 'test'

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:

Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}

Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:

Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456

The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.

So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?

Please help.

Thank you.

Fipil.

Is the test account in the PAL? It may also need to be in the dbo_role in the distribution database.|||

I'm not using Replication, but RDA. So, there are no a publication created.

|||Moving to CE forum.|||

I solved the problem.

The problem was in login name, I replaced 'g' by 'q' in login name, so this was all the problem :-).

|||

Hi,Fipil

I met the same problem with u.

Glad that you have slove the problem :-)

But, I'm not clear what's your mean that "I replaced 'g' by 'q' in login name", Can you explain it detaily?

Thanks & Best Regards,

Hua wen gui

|||

Hi,

I made a mistake: I simple put the 'q' character instead of 'g' character to user name field, in my application's login form (on PDA). And, because I implemented remembering of username, the mistake was repeated. Little 'q' is similar to little 'g', so I passed over the mistake :-) and searched for another error...

|||

So, you did not connect the DB with Login "test" in your PDA program, right?

My problem is : I can connect the DB with the same connect string in VBA . But, I can not connect DB in PDA program().

meanwhile, I can access http://server/PDA/sscesa20.dll on PDA successfully.

It's a strange problem, any suggestion?

Thanks

|||

Yes, you are right. I changed original name of user by 'test' while writting post here.

Did you mean DB on server or db on PDA?

|||

The DB on server.

I'd like to pull data from DB Server to PDA with RDA.pull.

But, a error comes out with login error (I can connect the Server DB with the same connection string in VB)

I can not find out the problem.

I'm not sure If the problem on IIS or SQL Server?

Ps, my SQL Server is SQL Server 2005, I can view http://Server/PDA/sscesa20.dll correctly.

RDA Pull Problem: Command=PULL Hr=80040E4D Login failed for user 'test'

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:

Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}

Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:

Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456

The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.

So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?

Please help.

Thank you.

Fipil.

Is the test account in the PAL? It may also need to be in the dbo_role in the distribution database.|||

I'm not using Replication, but RDA. So, there are no a publication created.

|||Moving to CE forum.|||

I solved the problem.

The problem was in login name, I replaced 'g' by 'q' in login name, so this was all the problem :-).

|||

Hi,Fipil

I met the same problem with u.

Glad that you have slove the problem :-)

But, I'm not clear what's your mean that "I replaced 'g' by 'q' in login name", Can you explain it detaily?

Thanks & Best Regards,

Hua wen gui

|||

Hi,

I made a mistake: I simple put the 'q' character instead of 'g' character to user name field, in my application's login form (on PDA). And, because I implemented remembering of username, the mistake was repeated. Little 'q' is similar to little 'g', so I passed over the mistake :-) and searched for another error...

|||

So, you did not connect the DB with Login "test" in your PDA program, right?

My problem is : I can connect the DB with the same connect string in VBA . But, I can not connect DB in PDA program().

meanwhile, I can access http://server/PDA/sscesa20.dll on PDA successfully.

It's a strange problem, any suggestion?

Thanks

|||

Yes, you are right. I changed original name of user by 'test' while writting post here.

Did you mean DB on server or db on PDA?

|||

The DB on server.

I'd like to pull data from DB Server to PDA with RDA.pull.

But, a error comes out with login error (I can connect the Server DB with the same connection string in VB)

I can not find out the problem.

I'm not sure If the problem on IIS or SQL Server?

Ps, my SQL Server is SQL Server 2005, I can view http://Server/PDA/sscesa20.dll correctly.