Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft - Microsoft SQL Server - SQL - Users - Add Users to Databases ======

<code sql>
CREATE Procedure Add_User_to_ADM_Databases(
    @User VARCHAR(64)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DatabaseName NVARCHAR(100)   
DECLARE @SQL NVARCHAR(max)
PRINT 'The following user has been selected to have read-only access on all user databases except system databases and log shipped databases:  ' +@user
DECLARE Grant_Permission CURSOR LOCAL FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution','DataConstructionServer_Backup','dswMatchReview_Backup','dswTempDB')  
and [state_desc]='ONLINE' and  [is_read_only] <> 1 order by name
OPEN Grant_Permission  
FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  
SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'CREATE USER ' + @User + 
    ' FOR LOGIN ' + @User + '; EXEC sp_addrolemember N''db_datareader'', 
    ' + @User + '';
PRINT @SQL
EXEC sp_executesql @SQL
Print ''-- This is to give a line space between two databases execute prints.
FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
END  
CLOSE Grant_Permission  
DEALLOCATE Grant_Permission
END
</code>