CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
ahmd0
ahmd0

Reputation: 17303

How to grant database user read/write access roles the "new way"?

OK, so I have a database, a login and a database user assigned to a login. To grant that user db_datareader and db_datawriter access to the database I would do this:

USE [mydatabase];
EXEC sp_addrolemember db_datareader, [myuser];
EXEC sp_addrolemember db_datawriter, [myuser];

But if I'm reading the documentation correctly, the use of sp_addrolemember is discouraged:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead.

So how do you do this with ALTER ROLE? When I do this:

ALTER ROLE sys.database_role_members.db_datareader ADD MEMBER [myuser];

I get this error:

Incorrect syntax near ADD MEMBER

Upvotes: 1

Views: 11320

Answers (2)

Eric Isaacs
Eric Isaacs

Reputation: 1

I know this is a very old post, but it still comes up at the top of Google when querying this question.

In newer versions of SQL Server since 2012, ALTER ROLE is the way to do it...

    USE [DATBASENAME];
    ALTER ROLE [db_datareader] ADD MEMBER [username];
    ALTER ROLE [db_datawriter] ADD MEMBER [username];

Upvotes: -2

sahalMoidu
sahalMoidu

Reputation: 1152

If you are using sql server 2008 only sp_addrolemember will work.

The alter role syntax is only valid for the 2012 version. It is due to that you get an incorrect syntax error

Upvotes: 2

Related Questions