SQL Server/T-SQL Tutorial/System Functions/sp addrolemember

Add role member

2>
3> IF EXISTS(SELECT * FROM sys.tables
4>     WHERE name = "TrackPersons" AND schema_id = 1)
5>     DROP TABLE dbo.TrackPersons
6> IF EXISTS(SELECT * FROM sys.tables
7>     WHERE name = "TrackPersons" AND schema_id = 5)
8>     DROP TABLE sqllogin3.TrackPersons
9> GO
1>
2> EXEC sp_addrolemember "db_ddladmin", "sqllogin4"
3> GO
1>
2> EXECUTE AS login = "sqllogin4"

A statement that assigns a user to a database role

EXEC sp_AddRoleMember db_owner, JohnDoe

A statement that assigns the new role to a fixed database role

EXEC sp_AddRoleMember db_datareader, BillingEntry

How to assign a user to a database role

The syntax for sp_AddRoleMember
sp_AddRoleMember [@rolename = ] "database_role_name",
                 [@membername = ] "security_account"

Statements that assign users to the new role

EXEC sp_AddRoleMember BillingEntry, JohnDoe
EXEC sp_AddRoleMember BillingEntry, [Accounting\JaneSmith]