Add User to Role
-
Posted on July 25, 2009 by Derek Dieter
-
0
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'
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.
Post a comment
- Comments (RSS)
- Trackback
- Permalink
6 comments
Ibran Siagian
15 Apr 2012 at 8:47 pm
Thanks for the articles.
Creating Roles and assigning Permission in SQL Server and Oracle « Java Development
09 Oct 2011 at 12:15 am
[...] Source 2: SQL Server Planer [...]
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
