Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 13 November 2013

How to assign Database Role/Permission on New Login Creation?

How to assign Database Role/Permission on New Login Creation?

While you create new login in SQL 2005 you should see the following roles available


So first step is right click under security tab on logins like see below


now u can give name and password like see below



and now under user mapping tab kindly assign as u require database and select schema like dbo and choose Important roles like below

db_datareader-------only select values of object like tables,views
db_datawriter--------only can update/modify on any objects  in SQL server      


After successfully created new login user can login now as see below



If user have db_datareader permission then cant able to modify records but can see all tables values

 like see below bu


So If u want user should modify values then u have to assign db_datawriter permission like see below


and now user can able to update/modify records see below


That is it.

Now if user want to Execute SPs he could get following error



to sort out this issue sysadmin role user like DBA shold give execute permission to that SP as see below

Right click the stored procedures tab under programmbility tab and choose properties like see below


and now add button to choose role and assign like see below