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