SQL Server Performance

Add users in SQL 2000 and 2005 proc

Discussion in 'Contribute Your SQL Server Scripts' started by MichaelB, Apr 18, 2007.

  1. MichaelB Member

    This will add users programtically to all dbs. Change as you see fit. I added the role so that dbs can add them if they want them.

    alter Procedure AddSQLUser
    (@UserName varchar(200),@Pass varchar(200),@DefaultDB varchar(200),@AuthType varchar(20),
    @RoleName varchar(50) = 'processadmin')

    as


    declare @databasename as varchar(200)

    declare Curse cursor local fast_forward
    for

    select distinct
    name
    from
    master.dbo.sysdatabases
    where
    name not in ('master', 'msdb', 'model', 'tempdb')


    --check the type of authentication - and the database version -
    --if we use 2000 or 2005. This will need to be updated for any new
    --db version loaded.
    if @authtype = 'SQL' and left (cast((SERVERPROPERTY('productversion')) as varchar(25)),1) ='8'
    begin

    exec ('use master')
    EXEC master..sp_addlogin @userName ,@pass ,@DefaultDB
    EXEC sp_grantdbaccess @UserName, @Username
    EXEC sp_addsrvrolemember @UserName,@rolename

    open Curse


    fetch next from Curse into @databasename

    while @@fetch_status = 0
    begin
    exec ('use ' + @databasename + '


    EXEC sp_grantdbaccess ' + @UserName + ',' + @Username + '

    EXEC sp_addrolemember N''db_datareader'',' + @Username + '
    EXEC sp_addrolemember N''db_datawriter'',' + @Username + '
    EXEC sp_addrolemember N''db_ddladmin '',' + @Username + '

    ')

    fetch next from Curse into @databasename
    end
    close Curse
    deallocate Curse

    end


    if @authtype = 'SQL' and left (cast((SERVERPROPERTY('productversion')) as varchar(25)),1) ='9'
    Begin
    exec ('use master')

    exec ('CREATE LOGIN ' + @username + ' WITH PASSWORD='''+@pass + ''', DEFAULT_DATABASE=' + @defaultDB + ', CHECK_EXPIRATION=OFF, CHECK_POLICY=ON')
    EXEC master..sp_addsrvrolemember @username, @rolename

    open Curse


    fetch next from Curse into @databasename

    while @@fetch_status = 0
    begin
    exec ('
    use ' + @databasename + '
    CREATE USER ' + @username + ' FOR LOGIN '+ @username + '
    EXEC sp_addrolemember N''db_datareader'',''' + @Username + '''
    EXEC sp_addrolemember N''db_datawriter'',''' + @Username + '''
    EXEC sp_addrolemember N''db_ddladmin'',''' + @Username + '''
    ')
    fetch next from Curse into @databasename
    end
    close Curse
    deallocate Curse

    end


    if @authtype = 'WINDOWS' and left (cast((SERVERPROPERTY('productversion')) as varchar(25)),1) ='8'
    begin

    exec ('use master')
    EXEC master..sp_grantlogin @userName ,@pass ,@DefaultDB
    EXEC sp_grantdbaccess @UserName, @Username
    EXEC sp_addsrvrolemember @UserName,@rolename

    open Curse

    fetch next from Curse into @databasename

    while @@fetch_status = 0
    begin
    exec ('use ' + @databasename + '


    EXEC sp_grantdbaccess ' + @UserName + ',' + @Username + '

    EXEC sp_addrolemember N''db_datareader'',' + @Username + '
    EXEC sp_addrolemember N''db_datawriter'',' + @Username + '
    EXEC sp_addrolemember N''db_ddladmin '',' + @Username + '

    ')

    fetch next from Curse into @databasename
    end
    close Curse
    deallocate Curse

    end


    if @authtype = 'WINDOWS' and left (cast((SERVERPROPERTY('productversion')) as varchar(25)),1) ='9'
    Begin
    exec ('use master')

    exec ('CREATE LOGIN [ebs' + @username + '] FROM WINDOWS WITH DEFAULT_DATABASE=' + @defaultDB + '' )
    exec ('exec master..sp_addsrvrolemember ''ebs'+ @username + ''','''+ @rolename + '''')

    open Curse

    fetch next from Curse into @databasename

    while @@fetch_status = 0
    begin
    exec ('
    use ' + @databasename + '
    CREATE USER [' + @username + '] FOR LOGIN [ebs'+ @username + ']
    EXEC sp_addrolemember N''db_datareader'',''' + @Username + '''
    EXEC sp_addrolemember N''db_datawriter'',''' + @Username + '''
    EXEC sp_addrolemember N''db_ddladmin'',''' + @Username + '''
    ')
    fetch next from Curse into @databasename
    end
    close Curse
    deallocate Curse

    end


    Michael
    MCDBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7

Share This Page