Database Security Question

• Sarwan

  SSCommitted

  Points: 1999

  More actions

  October 24, 2008 at 1:53 pm

  #96567

  Hi All,

  we have a sql server instance which consists of 200 databases. A user called Jack has DB_reader permission on all the databases and i want to change jack's permission from DB_reader to DB_writer.

  How can i do this in a single shot using T-SQL/Mgmt Studio ?

• Jerry Hung

  SSChampion

  Points: 12968

  More actions

  October 24, 2008 at 1:58 pm

  #889460

  Look into sp_MSForEachDB (hidden MS procedure)

  sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') use ? EXEC sp_droprolemember N'db_datareader', N'Jack'

  sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') use ? EXEC sp_addrolemember N'db_datawriter', N'Jack'

  Otherwise you can always look through a cursor of all User Databases

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005