Post reply

My Script Dies :(

  • May 1, 2008 at 1:57 pm

    #71940

    Hey all. I have a question about my script below. It dies and ends if 1.) I do not have a login on that database or 2.), the database is in read only. When it hits of of those two things, it dies and will not continue onto the other databases.

    Would any please be able to assist?

    sp_grantlogin 'NAME'

    DECLARE @userName sysname,

    @dbName sysname

    DECLARE dbCursor CURSOR

    LOCAL FORWARD_ONLY STATIC READ_ONLY

    FOR

    SELECT name

    FROM master.dbo.sysdatabases

    OPEN dbCursor

    FETCH NEXT FROM dbCursor

    INTO @dbName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'The current database is [' + @dbName + ']:'

    EXECUTE ('exec ' + @dbName+ '..sp_grantdbaccess @loginame=''NAME'' ')

    FETCH NEXT FROM dbCursor

    INTO @dbName

    END

    CLOSE dbCursor

    DEALLOCATE dbCursor

    GO

  • May 1, 2008 at 2:27 pm

    #809813

    database read only -

    add an if statement in the cursor loop to only execute code if database not read only, i.e.

    if (SELECT DATABASEPROPERTY(@dbname, 'Isreadonly')) = 0

    begin

    grantaccess...........

    access to all databases -

    need to run this as a user with sysadmin rights or a pre-defined user with rights to grant logins and add users already in all databases, no way round that.

    you might want to omit system databases as well using dbid > 4 when building the cursor

    ---------------------------------------------------------------------

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply