Creation of User-defined Roles

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 175
There are built-in roles in the database, such as db_owner, db_datawriter, db_datareader and so on...


In the same way, SQL Server aloows us to create user-defines roles for restricting user access on the database/any object.

This helps DBAs to have proper and relevant access privileges to specific database user....

-- First create a role
/* CREATE ROLE RoleName*/
CREATE ROLE FullPermissionsRole;



--Secondly, grant only the required privileges to that role
--GRANT read/write/EXECUTE permissions on all database objects
/*GRANT <permissions whihch you want> ON DATABASE::DatabaseName TO RoleName*/
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE PROCEDURE, CREATE FUNCTION, EXECUTE ON DATABASE::DatabaseName TO FullPermissionsRole;
GO

Finally, we can give a database user to have access to the above ROLE FullPermissionsRole....

Comments or Responses

Login to post response