May 21, 2008 at 3:36 pm
USE [apppub]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [BranchDB]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [epg]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [epgcache]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [epgpub]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [EventLog]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [LiveBackend]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [ppvpub]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [tmspub]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
--
USE [ServiceGroupDB]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [SGepg]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [tservercontroller]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
--
USE [ActivityLog]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
USE [ClientTraceLog]
GO
CREATE USER @account FOR LOGIN [@VHO_Name\SQLRO]
GO
EXEC sp_addrolemember N'db_datareader', N'@VHO_Name\SQLRO'
GO
May 21, 2008 at 4:20 pm
Try this. Hope it works.
Create PROCEDURE dbo.s_test
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @sdbname SYSNAME
DECLARE @account SYSNAME
DECLARE @VHOName SYSNAME
DECLARE @sSQL VARCHAR(150)
DECLARE @sSQL1 VARCHAR(150)
DECLARE @iRowCount INT
DECLARE @t_TableNames_Temp TABLE
(table_name SYSNAME)
Set @account='Domainewuser'--Specify account here
Set @VHOName='Domainewuser'--Specify user account here
INSERT @t_TableNames_Temp
SELECT name
FROM master..SYSdatabases where name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @iRowCount = COUNT(*) FROM @t_TableNames_Temp
WHILE @iRowCount > 0
BEGIN
SELECT @sdbname = table_name from @t_TableNames_Temp
SELECT @sSQL = 'use '+@sdbname+char(13)+'CREATE USER ['+@Account+
'] FOR LOGIN ' +'['+@VHOName+']'
SELECT @sSQL1 = 'use '+@sdbname+char(13)+'EXEC sp_addrolemember N''db_datareader'', N'''+@VHOName+''''
EXEC (@sSQL)
EXEC (@sSQL1)
DELETE FROM @t_TableNames_Temp WHERE @sdbname = table_name
SELECT @iRowCount = @iRowCount - 1
END
--RETURN 0
SET NOCOUNT OFF
GO
Manu Jaidka
May 22, 2008 at 6:09 am
Search this site for information on the undocumented system procedure:
sp_MSforeachdb
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply