SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Deny access at database level		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Posting Yak Master

245 Posts
Posted - 07/19/2012 :  06:00:50  Show Profile  Reply with Quote
Hi all

I 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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 07/19/2012 :  06:45:35  Show Profile  Reply with Quote
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.
Go to Top of Page

rmg1
Posting Yak Master

245 Posts
Posted - 07/19/2012 :  08:50:03  Show Profile  Reply with Quote
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?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 07/19/2012 :  09:01:22  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Posting Yak Master

245 Posts
Posted - 07/19/2012 :  09:56:48  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 07/19/2012 :  10:41:56  Show Profile  Reply with Quote
You are very welcome.)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000