February 27, 2008 at 3:43 am
Hi,
I have a database which I want only a couple of people to be able to view and no one else.
I am new to SQL Server 2005 so would I need to set a login first and then somehow use/call these from within the database properties?
I'm not sure of the actual procedure to follow in order to set this up.
Thanks.
February 27, 2008 at 10:06 am
Yes. Create the logins then assign database access to the login, which will create a user in the database.
In SSMS, expand Security, right-click on Logins, and select New Login. Choose Windows authentication or SQL Server authentication, fill in the login name (and password if a SQL Server login), and set the default database. Select User Mapping and check the database you want the login to have access to. You can also assign membership in fixed database roles here. If you want to assign specific object permissions, you need to do that in the database itself.
Greg
February 28, 2008 at 1:50 am
Great I'll give that a try, thanks.
February 28, 2008 at 7:12 am
Hi Greg,
I have now managed to add logins for my database.
How do I now add these users to the database itself?
I have expanded the database and expanded 'security' and 'users'.
Do I need to add the user in this section aswell?
Thanks.
February 28, 2008 at 8:49 am
Yes, you can either right-click on Users in the database and select "New User..." or you can do it at the login level like I described in my previous post. The advantage of adding the user at the database level is you can assign a wider range of permissions there.
So, right-click on Users, select "New User...", select the Login, type in the user name, assign a default schema, and assign role membership. If you want to assign object permissions (select, execute, etc.), click on Securables and add them.
Greg
February 28, 2008 at 8:58 am
Or you can check the properties of the user and then add the database access to it.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 9:22 am
Have created the user but it seems like administrators are still able to access the database.
Users I currently have setup for my database as default are:
dbo, sys, INFORMATION_SCHEMA, guest and my own user account.
Could it be because of one of these that it is still allowing administrators access to the database?
February 28, 2008 at 4:39 pm
If by "administrators" you mean members of the sysadmin server role, you can't prevent them from accessing any database. They don't even have to be mapped to a user in a database to be able to access it. You'll have to remove logins from sysadmin if you don't want them to access user databases.
Greg
February 29, 2008 at 7:53 am
Ok yes I'll give it a try.
Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply