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
>
|||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
>