Friday 3 August 2012

How to grant read  permission to all the databases?

Case Scenario:

 can grant the read permision to all the databases at one go?

Resolution:
Step 1: Create the Login ID Script first.
Step 2: Change the User ID and Login ID in the following script and execute it which will grant read permission to all the databases.
          EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
          Begin
               Print ''?''
               Declare @cmd varchar(255)
               set @cmd = ''USE [?] CREATE USER [User ID] FOR LOGIN [Domain\Windows Login ID] ''
               set @cmd = @cmd + ''EXEC sp_addrolemember N''''db_datareader'''', N''''User ID'''' ''
               exec (@cmd)
               print @cmd
          End'
Note: The above script can be used to grant any sort of permission to all the databases.

No comments:

Post a Comment