September 1, 2010 at 1:18 pm
I want to lock a database and restrict access to all the users. Is there any sql query so that any user can run it and the database opens only for that particular user?
Thanks,
Sandeep.
September 1, 2010 at 1:49 pm
ssandeepv (9/1/2010)
I want to lock a database and restrict access to all the users. Is there any sql query so that any user can run it and the database opens only for that particular user?
This is a very unusual business rule.
Having said that I would say nothing prevents you to write a script that on-the-fly disables all accounts but the one that is supposed to be active, then enables all accounts back. I would suggest not to even attempt to disable "sa" account.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 1, 2010 at 2:55 pm
Are you talking about single-user access?
September 1, 2010 at 5:38 pm
What I would suggest is that you setup a role with access, then have a script that removes access for everyone else and adds the user to this new role.
Have another script that reverses things.
September 2, 2010 at 2:21 pm
single user as in: as per the request.
I want to give access to the users whoever needs it, not like a group of people.
I need a query or a sproc which the user can execute & then get access exclusively to that user.
Thanks,
Sandeep.
September 2, 2010 at 2:30 pm
Why not save the trouble of trying to make a script work properly by using ALTER DATABASE SET SINGLE_USER statement?
This will allow only one user at a time to access the database. That way you can set permissions for a group of users, but only one will be able to access the database at at time.
Be sure and take the following advice into account:
SINGLE_USER
Specifies that only one user at a time can access the database. If SINGLE_USER is specified and there are other users connected to the database the ALTER DATABASE statement will be blocked until all users disconnect from the specified database. To override this behavior, see the WITH <termination> clause.
The database remains in SINGLE_USER mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.
Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. If the option is set to ON, perform the following tasks:
Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.
Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.
If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.
More information can be found at ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/f76fbd84-df59-4404-806b-8ecb4497c9cc.htm
September 2, 2010 at 11:54 pm
But if the database is set to be in single user mode then only sysadmin role will be able to login in the database.
Better to script out access to single user then script out the operation to revoke access from all users.
RUn the revoke script first and then run the script to give access to single user.
( Note : Have a reverse script for revoke operation )
September 3, 2010 at 12:00 am
But if the database is set to be in single user mode then only sysadmin role will be able to login in the database.
Better to script out access to single user then script out the operation to revoke access from all users.
RUn the revoke script first and then run the script to give access to single user.
( Note : Have a reverse script for revoke operation )
September 3, 2010 at 12:25 am
Here is a new way to think about it.
I am not sure it is going to work for you the way I used it, and I had it for a very specific case.
I created a user that had the permissions that were considered acceptable.
If the user needed access you could execute a job that would send them the password
I then create a job that would reset that password for that account each night,
This case that we did was done so developers could get short term access to the server without us having to set the accounts up each time. We would also trace the accounts so that we knew what they had done during that time.
Fell free to ask me if you want more detail.
September 3, 2010 at 4:19 pm
SINGLE_USER allows any single user with permission to access the database.
RESTRICTED_USER is the one that limits access to sysadmin, dbowner, or dbcreator.
September 3, 2010 at 4:34 pm
The issue with Single_user is that the first user gets in. Might not be that person.
I'd still set permissions in two groups. Revoke access for one group, add for the second (1 person). then reverse when they're done. Without permissions to objects, people can't do anything, even if they access the database.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply