Creating a Read Only User - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating a Read Only User

Author

Topic

tony_waters
Starting Member

1 Post

Posted - 2005-10-10 : 21:17:00

I'm not in our IT section but am responsible for the running of a finance application on SQL 2000.

I want a Read only user set-up on the SQL DB so that we can use Crystal and Access to look at the tables.

IT have some concerns with this as they say it by passes all security.

Are they spinning me a line?? Surely read oonly is read only? Can a user be restricted to a specific database as there are a number running on the same server.

Many thanks for any advice comments.

Tony

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-10-10 : 21:49:09

Absolutely. SQL Server adds two database roles to make this easier for you: db_datareader, and db_denydatawriter. You would create a read-only login on your SQL Server, add a user to the database with that login, then add that user to those two roles:

EXEC sp_addlogin @loginame = 'readonly', @passwd = 'readonlypassword', @defdb='myDatabase'
USE myDatabase
GO
EXEC sp_adduser @loginame = 'readonly', @name_in_db = 'readonly'
EXEC sp_addrolemember @rolename = 'db_datareader', @membername='readonly'
EXEC sp_addrolemember @rolename = 'db_denydatawriter', @membername='readonly'

You can also individually DENY permissions for that user on various objects in the database:

DENY UPDATE, INSERT, DELETE on myTable TO readonly

DENY always overrules any permissions inherited by group/role membership. While this is redundant as long as the user is in the db_denydatawriter role, it's a nice backup in case someone could remove or change role membership for that user.

sangamonese
Starting Member

1 Post

Posted - 2005-10-19 : 17:45:45

Could this be a solution to my problem of not being able to do an "outside call" to my MS SQL db on a remote server?

I have a DOS program that connects to the db and makes calculations and then writes the results back to the db.

I had to switch servers this week and I can not get the DOS program to work now. I enter the command prompt and get as far as

Creating connected session object..

and then it stops.

I know there is something in the permission that needs to be changed, but I don't know what it is and the techies at the server are clueless.

TIA for any help,
Karen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources