Tuesday, July 26, 2011

Sql server create user to all or group of database in server

declare @query nvarchar(max)

DECLARE

    DBNames CURSOR

FOR

    SELECT

        NAME

    FROM sysdatabases
 where name like 'parte ddo nome das BDs_pretendidas%' –-remover esta linha ser for para todas as BDs



OPEN DBNames



DECLARE @Name varchar(50)



FETCH NEXT FROM DBNames

INTO @Name



WHILE (@@FETCH_STATUS <> -1)

BEGIN



set @query='USE ['+ @Name+']'

--set @query=@query+' GO '

set @query=@query+' CREATE USER [dominio\user] FOR LOGIN [dominio\user] WITH DEFAULT_SCHEMA=[dominio\user]' -- pode mudar o default_schema ex: dbo



EXECUTE sp_executesql @query



  FETCH NEXT FROM DBNames

    INTO @Name



END



CLOSE DBNames

DEALLOCATE DBNames

No comments:

Post a Comment