We help IT Professionals succeed at work.

GRANT SQL User Database Read Only

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!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
Something like this should work:
EXEC sp_addrolemember 'db_datareader', 'yourusername'

Author

Commented:
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.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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'
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.