Limit Access to DB's
-
I am upgrading from 2000 to 2005. I have one server with multiple databases. Two of my users previously were allowed to connect to a specific Database from Query Analyzer and run/edit stored procedures.
With 2005, query analyzer does not allow them to edit SP anymore. Can someone tell me the best way to allow these 2 users access to the one database they need and COMPLETELY limit any access (I dont even want them to know they exists) to all the other databases.
Are there any other security issues I would need to be concerned with that they might figure out?
I would prefer to not give them SQL Management studio but I dont know any way around that.
Any help woud be great....thanks in advance.
-
failrly straightforward requirement Bosco. they will not get any rights you don't give them. if they connect with SSMS, you cannot hide the existence of other databases, but they can't do anything to them even if they do know the database "PayrollAndCompanySecrets" exists or not, for example.
first, make sure their login does is not part of any role except Public:
SSMS Object Explorer>>Security Folder>>RightClick Properties of a specific login..The window that opens has a "Server Roles" selection with a window full of checkboxes.
Now, you will want to Add that login as a USER to that one specific database.
On a specific database, Expand the folders so you can see Security>>Users, which you will Right Click on and select New User. use the interface to select the login to match the user...it's good practice to name the user the same name as the login...so login 'bob' is the same as user 'bob' or the login 'domainname\Bosco' is the same as the user 'domainname\Bosco'
Next we create a ROLE, that has only the access you want. It looks like these guys had read /write as well as creating / editing procedures and tables. if they still get the same permissions, continue or remove what they do not need.
Similar to Users, we go to
Security>>Roles, Right click and select New Database Role.
i would create a role 'PowerUser' for example
and select the following three checkboxes:
ddl_admin --allows creating objects
db_datareader
db_datawriter
in the "owned schemas" section that opened.
on the bottom of that same screen is a button to Add users to the role...click it and find the user we had just added.
from there, the user will only have access to that one specific database, but he can do a lot to it...updating, deleting, creating and editing procedures....
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply