Thursday, February 23, 2012

Granting Privileges to a role in sql server

To grant privileges to a role use the same syntax as granting privileges to a user except that in the place of user name specify the role name.Here i will shown a example to grant various permissions on the tables orders,sales and customers to the role myrole
Grant select,Insert,Update(name,job) on orders to myrole
Grant select,Insert,Update on sales to myrole
Grant select,Insert,Update on customers to myrole
Adding a member to the role:To add a member to the role use the procedure sp_addrolemember
sp_addrolemember 'rolename','username'
if you want to delete the role myrole then the below syntax will be used
sp_droprole 'myrole'

No comments: