SQL Server Performance

Script to automatically add login and permissions to new databases

Discussion in 'SQL Server 2008 General DBA Questions' started by darkangelBDF, Oct 13, 2009.

  1. darkangelBDF Member

    Hi there all. I have a question again, this time regarding creating a script(s) that could be used throughout all servers.
    We want a script that will add a certain AD group as a database login.
    We then want to add this group to all database on the local server.
    We also then want this user to be added to the db_backupoperator database role.
    What my manager explains to me is that this will then by default add it to the model database which means that it will then be added to each subsequent database that is created.
    Just how would I go about creating such a script?
  2. melvinlusk Member

    Try this. You'll have to run it against each server.USE [master]
    GOCREATE LOGIN [YOURDOMAINADGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GODECLARE
    @pstrSQL as varchar(200)DECLARE
    @pstrDBName as varchar(50)create
    table #tmpDatabaseList(
    fstrDBName varchar(50))insert
    into #tmpDatabaseListselect
    [name] from sys.databaseswhile
    exists (select 1 from #tmpDatabaseList)BEGIN
    set
    @pstrDBName = (select top 1 fstrDBName from #tmpDatabaseList)set
    @pstrSQL = (select 'use ' + @pstrDBName + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'CREATE CREATE USER [YOURDOMAINADGroup] FOR LOGIN [YOURDOMAINADGroup] ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'EXEC sp_addrolemember N''db_backupoperator'', ''YOURDOMAINADGroup''' + CHAR(13) + CHAR(10) + 'GO')exec
    (@pstrSQL)delete
    from #tmpDatabaseList where fstrDBName = @pstrDBNameEND
    drop table #tmpDatabaseList
  3. darkangelBDF Member

    Thank you kindly! I'm definitely going to give this a try.

Share This Page