Add User to Role

To add a user to a role, you must use the stored procedure sp_addrolemember.

First you must select the database that contains the user and the role.

USE YourDatabaseHere
GO
EXEC sp_addrolemember 'db_datareader', 'JohnJacobs'
EXEC sp_addrolemember 'db_datawriter', 'JohnJacobs'
EXEC sp_addrolemember 'db_accessadmin', 'JohnJacobs'
EXEC sp_addrolemember 'db_backupoperator', 'JohnJacobs'
EXEC sp_addrolemember 'db_ddladmin', 'JohnJacobs'
EXEC sp_addrolemember 'db_denydatareader', 'JohnJacobs'
EXEC sp_addrolemember 'db_denydatawriter', 'JohnJacobs'
EXEC sp_addrolemember 'db_owner', 'JohnJacobs'
EXEC sp_addrolemember 'db_securityadmin', 'JohnJacobs'

The example above shows all predefined roles in a database.

6 comments
Ibran Siagian 15 Apr 2012 at 8:47 pm

Thanks for the articles.

anwar 02 Aug 2011 at 3:36 pm

very helpful. thank you.

James 01 Aug 2011 at 8:55 am

Just an editing point. You were going to cover adding a user with the interface.

Derek Dieter 02 Aug 2011 at 2:58 pm

Thank you James, I added it to my list of articles.

Jasbir 06 Dec 2010 at 2:20 pm

Thanks.
That was most helpful

Featured Articles

 Site Author