SQL Script To Add A SQL Server Login That Can Access All Databases

Dear all,

I wrote the below script to add a SQL server login account that is the db_datareader, db_datawriter, and granted permission on all stored procs, functions, and views on all databases within a server.



Code Snippet
USE master
GO
SET NOCOUNT ON
DECLARE @database_name sysname
DECLARE @object_name sysname
DECLARE @object_type char(2)
CREATE TABLE #databases (DATABASE_NAME sysname, DATABASE_SIZE int, REMARKS varchar(254))
INSERT #databases EXEC sp_databases
-- ++++++++++++++++++ add SQL Server Login ++++++++++++++++++
IF EXISTS (
SELECT 1 FROM master.dbo.syslogins
WHERE [name] = 'WEB_USER2'
) BEGIN
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT DATABASE_NAME FROM #databases
OPEN db_cur
WHILE 1 = 1
BEGIN
FETCH db_cur INTO @database_name
IF (@@FETCH_STATUS <> 0) BREAK
EXEC ('USE ' + @database_name +';
IF EXISTS (
SELECT 1 FROM sysusers
WHERE [name] = ''WEB_USER2''
) BEGIN
EXEC sp_revokedbaccess ''WEB_USER2''
END
')
END
CLOSE db_cur
DEALLOCATE db_cur
EXEC sp_droplogin 'WEB_USER2'
END
EXEC sp_addlogin
@loginame = 'WEB_USER2',
@passwd = 'password'
-- ++++++++++++++++++ loop thro' all User-Databases ++++++++++++++++++
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT DATABASE_NAME FROM #databases
WHERE DATABASE_NAME NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution', 'ASPState')
OPEN db_cur
WHILE 1 = 1
BEGIN
FETCH db_cur INTO @database_name
IF (@@FETCH_STATUS <> 0) BREAK
PRINT ''
PRINT 'Current database=' + @database_name
-- add user to databases
EXEC ('USE ' + @database_name +';
IF EXISTS (
SELECT 1 FROM sysusers
WHERE [name] = ''WEB_USER2''
) BEGIN
EXEC sp_revokedbaccess ''WEB_USER2''
END
')
EXEC ('USE ' + @database_name +'; EXEC sp_grantdbaccess ''WEB_USER2''; ')
-- add user to db_datareader
EXEC ('USE ' + @database_name +'; EXEC sp_addrolemember ''db_datareader'', ''WEB_USER2''; ')
-- add user to db_datawriter
EXEC ('USE ' + @database_name +'; EXEC sp_addrolemember ''db_datawriter'', ''WEB_USER2''; ')
-- grant permission on Stored proc, Scalar function, Inlined table-function, Table function, View
-- !! coz EXEC is a self-contained batch, so must use GLOBAL
EXEC('USE ' + @database_name +';
DECLARE obj_cur CURSOR GLOBAL FAST_FORWARD FOR
SELECT [name], [type] FROM sysobjects
WHERE [type] IN (''P'', ''FN'', ''IF'', ''TF'', ''V'')
')
OPEN obj_cur
WHILE 1 = 1
BEGIN
FETCH obj_cur INTO @object_name, @object_type
IF (@@FETCH_STATUS <> 0) BREAK
-- PRINT 'object=' + @object_name + '; type=' + @object_type
IF LTRIM(RTRIM(@object_type)) = 'P' OR @object_type = 'FN'
BEGIN
-- EXEC on Stored proc, Scalar function
EXEC('USE ' + @database_name +'; GRANT EXEC ON dbo.' + @object_name + ' TO WEB_USER2 ')
END
ELSE
BEGIN
-- SELECT ON table function, View
EXEC('USE ' + @database_name +'; GRANT SELECT ON dbo.' + @object_name + ' TO WEB_USER2 ')
END
END
CLOSE obj_cur
DEALLOCATE obj_cur
END
CLOSE db_cur
DEALLOCATE db_cur

DROP TABLE #databases





plz revise it if you have better idea! Thx!