Solved

GRANT SQL User Database Read Only

Posted on 2012-03-13
4
524 Views
Last Modified: 2012-03-14
Hello,

I recently created a new SQL user on a SQL 2000 and SQL 2008 default instances on separate servers. Now I need to grant read only access to selected databases for that user. I'm having a little trouble putting togehter the SQL syntax for each version.

Can someone help me out with this?

Thank you in advance!
0
Comment
Question by:e_rock
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37718040
Something like this should work:
EXEC sp_addrolemember 'db_datareader', 'yourusername'
0
 

Author Comment

by:e_rock
ID: 37719670
acperkins - I tried that and this is what i'm getting. Steps are as follows:

1. I created the user by using the statement and pointing it to the database:
     CREATE LOGIN username WITH Password = 'password'        

2. I then ran the statement you provided pointing to the same database:
      EXEC sp_addrolemember 'db_datareader', 'username'

and i get the following message:
       
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'test12' does not exist in this database.

I have verified that the user i created exist under Security.

Thanks.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 37721045
EXEC master.dbo.sp_addlogin 'loginname', 'password', 'default_db' --for a SQL login
--EXEC sp_grantlogin 'login' --for a Windows login


EXEC db_name.dbo.sp_adduser 'loginname', 'loginname'
EXEX db_name.dbo.sp_addrolemember 'db_datareader', 'loginname'

OR of course:


USE db_name

EXEC dbo.sp_adduser 'loginname', 'loginname'
EXEX dbo.sp_addrolemember 'db_datareader', 'loginname'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37721531
I tried that and this is what i'm getting.
I am sorry I thought you just wanted to a User to a database role, I did not realize you had not even created the Login, let alone the User.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now