Can't add user to db_datareader via script

Question

I have the following script:

ALTER ROLE [db_datareader] ADD MEMBER [DomainGroup123]

when I run this against SQL Server 2008 R2 I get this error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ADD'.

I have looked online, and found examples that use this exact statement (but with a different user.)

I have double checked that the login exists and is a valid user on the database I am using. Also I have SA permissions on the server.

What am I mssing?

Although you've already found a solution, please note that the documentation for ALTER ROLE shows that the syntax you tried is only valid in SQL Server 2012, not SQL Server 2008 R2. — Pondlife
– Pondlife, Commented Feb 26, 2013 at 13:19
@Pondlife is right. Note: You should use "ALTER ROLE" for any future development in SS2012 on up. Proof: Microsoft's "Important" Note here on sp_addrolemember explicitly says to use "ALTER ROLE" instead. — MikeTeeVee
– MikeTeeVee, Commented Dec 8, 2014 at 19:36

supergrady · Accepted Answer · 2013-02-25 23:41:14Z

24

Use sp_addrolemember.

EXECUTE sp_addrolemember db_datareader, 'UserName'

answered Feb 25, 2013 at 23:41

supergrady

1,32210 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:10:24Z

9

Found this answer: https://stackoverflow.com/a/456365/16241

That showed me that I can run it like this:

exec sp_addrolemember db_datareader, [DomainGroup123]

I did that and it worked.

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Feb 25, 2013 at 23:40

Vaccano

83.2k163 gold badges510 silver badges929 bronze badges

Comments

Konrad Viltersten · Accepted Answer · 2015-01-22 08:42:41Z

4

I've noticed that depending on the version of the server, I need to go one of the following.

alter role RoleName add member UserName

or

execute sp_addrolemember RoleName, UserName

I'm thinking of changing my approach from trial-and-error to some kind of conditional but (a) this operation is performed quite seldom in my case and (b) I'm a bit lazy nowadays.

answered Jan 22, 2015 at 8:42

Konrad Viltersten

40.2k90 gold badges297 silver badges525 bronze badges

Collectives™ on Stack Overflow

Can't add user to db_datareader via script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related