Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to give Log in permissions on more then 60 Database Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 6:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 41, Visits: 674
Hi Experts,

I have a more then 60 database in one of the server and want to give Single User and AD account "Read only" permission on all 60 database. I have added the User and AD account into SQL Security but don't want to click 60 times to give permission. Is there any script which I can use and it will apply permissions to all 60 database? or any other way which you Experts can tell me I will really appreciate your help.


Thanks
Post #1521719
Posted Tuesday, December 10, 2013 6:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:20 PM
Points: 237, Visits: 763

In SSMS set results to text and run (replace DOMAIN\login_name with appropriate values):
select 'USE ' + name + '
GO
CREATE USER [DOMAIN\login_name] FOR LOGIN [DOMAIN\login_name]
GO
EXEC sp_addrolemember N''db_datareader'', N''DOMAIN\login_name''
GO
'
from master.sys.databases
where database_id > 4

Post #1521720
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse