Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2012-07-19 : 06:00:50
|
| Hi allI need to disable access to a list of users temporarily to a database.I've been going through the GUI to do it and it's not working as I expected.I've denied connect and execute rights and my test user can still do everything as normal.There are several databases in the list and I don't want to disable the overall login just the one to this database.Anyone any pointers? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-19 : 06:45:35
|
| What were the steps you did using the GUI?Right-click on the login name unser Security -> Logins at the Server level (not at the database level) in Object Explorer in SSMS. In the Login Properties window that comes up, click User Mapping tab, uncheck the Map checkbox for this database and click OK. |
 |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-07-19 : 08:50:03
|
| I've just tried removing the tick against the relevant database under user-mapping and my test-user still has access.Does this require a reboot of either the SQL server or the user's machine? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-19 : 09:01:22
|
It should not require a reboot. Is the user a member of sysadmin role? You can find out using:SELECT IS_SRVROLEMEMBER('sysadmin', 'yourtestuser');Other roles are listed here. You might want to see what roles your test user has: http://msdn.microsoft.com/en-us/library/ms176015.aspx |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-07-19 : 09:56:48
|
| My test user was a sysadmin.Once I removed him (temporarily) from that role, I could deny him read and write access to the relevant table and it seems to work.Thanks for all the help. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-19 : 10:41:56
|
| You are very welcome.) |
|
|
|
|
|
|
Deny access at database level