May 5, 2012 at 8:37 pm
Hi Friends,
One of my database configured on mirroring, and i want to restrict users on it. Because to put database on single user mode is not possible if the database is in mirroring state. So can anyone suggest me how i can restrict users to access database. Users having dbowner previllages.
Thanks in advance.
________________________________________
M.I.
[font="Times New Roman"]
May 5, 2012 at 10:15 pm
MSQLDBA (5/5/2012)
Hi Friends,So can anyone suggest me how i can restrict users to access database.
Mirrored database as such is not accessible.
M&M
May 6, 2012 at 4:42 am
mohammed moinudheen (5/5/2012)
MSQLDBA (5/5/2012)
Hi Friends,So can anyone suggest me how i can restrict users to access database.
Mirrored database as such is not accessible.
🙂 I know bro. I m talking about principal database.
________________________________________
M.I.
[font="Times New Roman"]
May 7, 2012 at 2:05 am
MSQLDBA (5/5/2012)
Hi Friends,One of my database configured on mirroring, and i want to restrict users on it. Because to put database on single user mode is not possible if the database is in mirroring state. So can anyone suggest me how i can restrict users to access database. Users having dbowner previllages.
Thanks in advance.
Revoke the permissions of Logins of the targeted user.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 7, 2012 at 2:55 pm
Do they need access to other databases or resources on the instance? If the answer is no just disable their logins.
If the answer is yes then it gets a bit tricky because you cannot disable a Database User. You can drop the Database User and then re-create it later with the same permissions and Role memberships. If there are a lot of object-level permissions this can be a pain. If they only belong to db_owner then it is not too bad.
Another option I have used when the user has a lot of object-level permissions is to create a new dummy login that is disabled, then re-map the Database User I want to disable to the dummy-disabled login. Then when I want to re-enable the database user I re-map it to the original login and drop the dummy login. This preserves the permissions of the Database User, but in effect prevents anyone from using it via a login. Note: the Database User can still be used in EXECUTE AS scenarios even with a disabled login.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply