Post reply

Restrict access to instance

  • March 14, 2011 at 6:17 am

    #147602

    I know you can put a database in either single or restricted mode, but is there a configuration anywhere for the instance? Basically we are refreshing our DEV environment with our production backups over the weekend, but people are leaving their connections up and when i try to kill them, they come right back. I have bee successful with disabling their accounts and then resetting the connections by putting the DB in single user mode and then back to multiuser mode.

    But, this instance has A LOT of users and i don't want to have to disable every single user account, but it's starting to look like i that's my only choice.

    Thoughts?

  • March 14, 2011 at 6:28 am

    #1298279

    Edit: reread your request: realized you wanted an entire instance, and not a single database.

    my advice then is a cursor which runs the alter database command for each database.

    EXEC sp_msforeachdb 'ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

    --old, not completely relevant post still below:

    the easiest way is with the alter database command:

    this command will force-ably disconnect everyone so you can restore or do whatever you need to in a database. note it would roll back any pending transactions from those users who were still connected; take that into consideration.

    ALTER DATABASE YourDataBase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    after you are done, if you did not RESTORE the database(the restore would put the db in the status it was backed up at...multi_user most likely, you might need to allow regular users again:

    ALTER DATABASE YourDataBase SET MULTI_USER

    I've also disconnected people with a cursor full of KILL commands to drop their connections; that is another option, but the ALTER DATABASE command is the best solution:

    [

    --usage: exec sp_kill DataBaseName

    --enhanced 02/04/2005 to also list hostname

    CREATE PROCEDURE sp_Kill

    @DBNAME VARCHAR(30)

    --Stored procedure to Delete SQL Process

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @SPID INT

    DECLARE @STR NVARCHAR(50)

    DECLARE @HOSTNAME NVARCHAR(50)

    DECLARE @DBID INT

    CREATE TABLE #TMPLOG (

    SPID INT,

    ECID INT,

    STATUS VARCHAR(50),

    LOGINAME VARCHAR(255),

    HOSTNAME VARCHAR(50),

    BLK INT,

    DBNAME VARCHAR(30),

    CMD VARCHAR(100) ,

    RID INT,

    )

    select @DBID=db_id(@DBNAME)

    IF @DBID IS NULL

    PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'

    INSERT INTO #TMPLOG EXEC SP_WHO

    --do not even try to kill spids that are SQL server itself.

    DELETE FROM #TMPLOG WHERE SPID < 50

    IF @@ERROR <> 0 GOTO Error_Handle

    DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG

    WHERE DBNAME LIKE @DBNAME

    OPEN CURPROCESSID

    FETCH NEXT FROM CURPROCESSID INTO @SPID

    SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID

    IF @SPID IS NOT NULL

    PRINT 'Spid Process Kill List For database: ' + @dbName

    ELSE

    PRINT 'NO Processes Exist to be killed on database ' + @dbName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @SPID = @@SPID

    BEGIN

    PRINT 'Cannot kill your own SPID, skipping ' + convert(varchar,@spid) + ' - ' + @HOSTNAME

    END

    ELSE

    BEGIN

    SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)

    EXEC SP_EXECUTESQL @STR

    PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME

    END

    IF @@ERROR <> 0 GOTO ERROR_HANDLE

    FETCH NEXT FROM CURPROCESSID INTO @SPID

    END

    Error_Handle:

    IF @@ERROR <> 0 PRINT 'Error killing process - ' + convert(varchar,@spid) + ' - ' + @HOSTNAME

    drop table #tmpLog

    SET NOCOUNT OFF

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 14, 2011 at 7:54 am

    #1298325

    thanks for the quick reply. That is what i am currently doing but would rather set something at an instance level instead of a DB level. The problem we are having is some connections come right back and take the single connection up and the SQL job account cannot connect to the DB.

    Any more thoughts? 🙂

  • March 14, 2011 at 8:01 am

    #1298332

    You could try disabling remote connections first.

  • March 14, 2011 at 8:03 am

    #1298335

    The restores are scheduled to use a SSIS package on a remote server so that will disconnect the job.

  • March 14, 2011 at 8:06 am

    #1298337

    damn i knew this and didn't remember it; I'll blame it on caffiene deficiency.

    you'll want to stop and start the instance in single user mode

    sqlservr.exe -m -s SQLEXPRESS

    http://msdn.microsoft.com/en-us/library/ms180965.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 14, 2011 at 8:10 am

    #1298338

    mflora (3/14/2011)

    The problem we are having is some connections come right back and take the single connection up and the SQL job account cannot connect to the DB.

    Any more thoughts? 🙂

    Are those accounts dbo/sysadmin? If not, then alter database set RESTRICTED_USER. That will keep everyone who's not dbo, sysadmin or db_creator out of the database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 14, 2011 at 8:25 am

    #1298349

    Thanks Gail, this is the option we are currently coding to use this weekend. It was the by DB option to use if the instance configuration wasn't possible. Hopefully this will keep the programmers out. 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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