March 22, 2011 at 8:29 am
I have it so they don't have rights to do anything, but I would like to prevent them from even using a database. I can't tweak the rights of public btw.
Also I can I prevent a user from even seeing all of the databases in SSIS?
Thanks
Henry
March 22, 2011 at 8:35 am
when you setup a user simply grant them access to only the databases they should be able to access. In the user properties page you only need look at the user mappings page. rather than selecting a database role selct the individual DB's that they should have access to. If you have a large group of people with similiar access you could also setup a new role that limits that access.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 22, 2011 at 8:46 am
Tried that, the problem is the public role has wide access and I can't take the specific user out of public and I can't modify the public rights.
March 24, 2011 at 1:23 pm
If the login is not linked to a user in the database then they will not be able to connect to that database.
To find the database user name for the login you want to block in a particular databases you can run this query after changing database_name and login_name to meet your case:
SELECT dp.*
FROM sys.server_principals sp
JOIN [database_name].sys.database_principals dp ON sp.sid = dp.sid
WHERE sp.name = 'login_name' ;
Then you can drop the user from the database:
USE [database_name]
GO
DROP USER [database_user_name]
GO
Note that all object-level database permissions for the user will be lost when they are dropped and if the user owns any schemas or objects you'll need to change the owner or drop the schemas and objects before you can drop the user.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 24, 2011 at 1:31 pm
Public is a database role, and as mentioned above, you can just outright remove said user from the database. However, this becomes a problem if you're doing NT group security like a lot of places.
You would have to do an explicit deny on the user themselves. Add the user as a server level login, then add them to the database. Go to the database itself in security, find your user, rt-click, and properties.
Go to securables, add..., Alll objects of the types... databases. You should have one entry, your database. Click the checkbox and hit OK.
In the list of explicit permissions below, find 'Connect'. click on Deny.
They should be locked out permanently from that database shell.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply