Saturday, November 17, 2012

How to create user defined Server Role to manage permissions easily in SQL Server 2012

Until SQL 2008 R2, you don't have option to create user defined server role. In SQL Server 2012, you can create a server role with permissions. It would help easily to manage the login with limited permissions. 

For example, in your company, there are  two teams to manage the front end and back end. Back end team should able to create obejcts and manage database. Front end team should have only access to read and write the records and not allow to create/alter sql objects. To achieve this, you can do either by create login with permissions or by means of server role. It would be easy for you to manage the login with server role with permission set.

Follow the below step on how to create a server role, permissions and logins.

Step1: Create a Server Role.

Step 2: Assigning permissions to server role.

Step 3: After server role created.

Step 4: Create Login with assigning newly created user defined server role.

No comments: