Post reply

Revoke Access

  • October 2, 2009 at 9:45 am

    #211617

    I am moving database from serverA to ServerB but during this process i dont want users to update any data on serverA, how do I revoke access to everyone in a single step so users can not update data but can read and i should be able to succesfully backup all databases.

    Also i dont want to take all db's to single user and backup because i think db's will be in in single user mode after restoring on destination server.

  • October 2, 2009 at 9:53 am

    #1061033

    Put a database in a ReadOnly mode.:-)

  • October 2, 2009 at 9:56 am

    #1061037

    ALTER DATABASE foo SET READ_ONLY

    GO

    Then, put a custom step in your maintenance plan so reindexing, etc doesn't fail (or exclude it from that part):

    ALTER DATABASE foo SET RESTRICTED_USER

    GO

    ALTER DATABASE foo SET READ_WRITE

    GO

    Then the step immediately after the maintenance:

    ALTER DATABASE foo SET READ_ONLY

    GO

    ALTER DATABASE foo SET MULTI_USER

    GO

    MJM

  • October 2, 2009 at 10:06 am

    #1061044

    But iam dealing with 150 databases from one server to another.

    If i put singloe user before backup and restore on another server , the destination server will resotre in single mode andi have to make multi user again there for 150 database which i move

  • October 2, 2009 at 10:44 am

    #1061064

    Tara-1044200 (10/2/2009)

    But iam dealing with 150 databases from one server to another.

    If i put singloe user before backup and restore on another server , the destination server will resotre in single mode andi have to make multi user again there for 150 database which i move

    Which is why I LOVE scripting so much (this assumes SQL90+) 😉

    USE [master]

    GO

    DECLARE

    @strSQL NVARCHAR(4000),

    @dbName VARCHAR(100)

    SET @strSQL = ''

    SET @dbName = ''

    DECLARE cDatabases CURSOR FAST_FORWARD

    FOR

    SELECT QUOTENAME(sdb.[name]) FROM sys.databases sdb WHERE sdb.user_access_desc = 'RESTRICTED_USER'

    OPEN cDatabases

    FETCH cDatabases INTO @dbName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @dbName = RTRIM(@dbName)

    SET @strSQL = 'ALTER DATABASE ' + @dbName + ' SET MULTI_USER;'

    PRINT @strSQL

    --EXEC sp_executesql @strSQL

    FETCH cDatabases INTO @dbName

    END

    CLOSE cDatabases

    DEALLOCATE cDatabases

    GO

  • October 2, 2009 at 11:13 am

    #1061082

    I dont think i cna bakup a database which is in single user mode. i get this error

    Msg 62309, Level 19, State 1, Line 0

    SQL Server has returned a failure message to LiteSpeed which has prevented the operation from succeeding.

    The following message is not a LiteSpeed message. Please refer to SQL Server books online or Microsoft technical support for a solution:

    BACKUP DATABASE is terminating abnormally.

    Database 'test' is already open and can only have one user at a time.

Viewing 6 posts - 1 through 5 (of 5 total)

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