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?

Solution 1

Use sp_addrolemember.

EXECUTE sp_addrolemember db_datareader, 'UserName'

Solution 2

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.

Solution 3

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.