how to deny logins

Question

how to deny logins

RightOnTarget

Hall of Fame

Points: 3041
More actions
May 14, 2013 at 10:43 am

#275236

Hi all,
I've copied a database to a new server. How do I make database on original server inaccessible to users while I am checking that everything is OK with the copy?
And how to reverse it?
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

rightontarget (5/14/2013)
Hi all,
I've copied a database to a new server. How do I make database on original server inaccessible to users while I am checking that everything is OK with the copy?
And how to reverse it?
Thanks,

A couple different ways depending on what you need to do. If you want to examine stuff in the database you can set the database to single user. That way nobody else can login.

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

If you want nobody to be able to access it you could take it offline, of course any application trying to connect will fail because it will appear that the db doesn't exist.

http://blog.sqlauthority.com/2010/04/24/sql-server-t-sql-script-to-take-database-offline-take-database-online/[/url]

If you just want to make it readonly, well set it to readonly. http://blog.sqlauthority.com/2011/04/16/sql-server-making-database-to-read-only-changing-database-to-readwrite/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

A couple of other ideas ,depending on who it is you want to keep out of the database restore the database with a name noone would connect to.

and another way is to create a role which deny the users access, and add everyone to the role; it only goes about halfway to what Sean posted, because they could connect to the database, but not select anything...but that connection could slow you down from restoring. it also might not work on someone who happens to be a sysadmin.

take a look at this thread with a very similar question:

http://www.sqlservercentral.com/Forums/Topic1451695-146-1.aspx#bm1451705

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!

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 3

Sorry for delay and thanks for your help.

I ended up setting it to read_only.