How can I provide someone ,only read permission to all databases in my server at a time.

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

zygonr Old Hand Points: 329 More actions · Answer 1

You need to create a login, map the user to the login and then give datareader permissions to the users, basically select permission.

You can script this too!

dm_tapas Hall of Fame Points: 3515 More actions · Answer 2

server have 70 Databases. I want to give only read permission to all at a time not to one by one Database.

zygonr Old Hand Points: 329 More actions · Answer 3

For 70 databases at a time, not sure wait for the experts.

Marco V SSCrazy Points: 2939 More actions · Answer 4

SET NOCOUNT ON

-- Get the name of all databases

DECLARE AllDatabases CURSOR FOR

SELECT name FROM master..sysdatabases

where name not in ('master','tempdb')

-- Open Cursor

OPEN AllDatabases

-- Define variables needed

DECLARE @DB NVARCHAR(128)

DECLARE @COMMAND NVARCHAR(128)

-- Get First database

FETCH NEXT FROM AllDatabases INTO @DB

-- Process until no more databases

WHILE (@@FETCH_STATUS = 0)

BEGIN

-- Build command to put database into DDBO ONLY mode

set @command ='USE ' + @db +

' CREATE USER reader FOR LOGIN [reader]'

-- Print command to be processed

print @command

-- Process Command

exec (@command)

set @command = 'USE ' + @db +

' EXEC sp_addrolemember ''db_datareader'' ,''reader'''

-- Print command to be processed

print @command

-- Process command

exec (@command)

-- Get next database

FETCH NEXT FROM AllDatabases INTO @DB

END

-- Close and Deallocate Cursor

CLOSE AllDatabases

DEALLOCATE AllDatabases

used as login: reader, but change it to youre liking but make sure its available