Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||Hello Jen,
The person in question is probably already in, but using a
role / group such as BUILTIN\ADMINISTRATORS.
Thats the reason why they can still do the delete, insert
and update stuff as well as the select.
Look under the security, logons and see which types are
Windows Groups, then have a chat to your Server Bods to
see if the user is already included in the group.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Hi,
>I am currently having problems assigning read only
privileges to a database
>for a specific user.
>Basically, I have an application which uses Windows
Authentication to access
>a database on SQL server 2000. I only want this user to
have read only access
>to this database. I have added the user into the Logins
in SQL server (i.e.
>domain\username) and granted them the db_datareader role
to that database. I
>was assuming that this would only allow them to have read
access to the
>database, but this is not the case as they can add,
modify and delete records.
>Any help/advice would be appreciated as this is driving
me mad.
>Thanks,
>Jen
>.
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
--
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>