Create a SQL USER user

• Vee

  Hall of Fame

  Points: 3028

  More actions

  September 20, 2005 at 2:39 am

  #115166

  question :

  Create a SQL USER  user called [veeresh], whose default database is [nokia], His password should be [vee],He can read data from any tables from his default database, he can create table or any object  but he cannot modify the data and cannot drop objects . He can take backups .

  I am running Sql server 2000 Personal Edition..

  I tried these steps , but user can drop table. that should not happen .. help me

  ..

   sp_addlogin [veeresh]

   go

   sp_defaultdb [veeresh],[nokia]

   go

   sp_password null,[vee],[veeresh]

   go

   use [nokia]

   go

   sp_grantdbaccess [veeresh]

   go

   sp_addrolemember [db_datareader],[veeresh]

   go

   sp_addrolemember [db_denydatawriter],[veeresh]

   go

   sp_addrolemember [db_backupoperator],[veeresh]

   go

   sp_addrolemember [db_ddladmin],[veeresh]

   go

   

   go

  sp_helpuser [veeresh] 

  

  give me TSQL-Statements for the same....

   

   

• Adam Angelini

  SSCrazy Eights

  Points: 8548

  More actions

  September 20, 2005 at 9:17 am

  #591374

  sp_addrolemember [db_denydatawriter],[veeresh]

   

  This statement keeps him from modifying data - add him to the datawriter instead of denydatawriter role