SQL 2008 - Permissions problem

Hi.
I created a new Login called test.    But when I connect using Management Studio.
I can connect to myDB  database. But don't see the tables... I can see the system tables.

If I connect using sa - I can see ALL the objects.

What am I doing wrong.  How can see the tables and using the test account?
What settings where do I need to set?

thx
LVL 1
JElsterAsked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
If you right click on the login name and select properties you'll get a properties screen.

On the left, select Server Roles.

If you want to create a sysadmin login, you go there and check the box next to sysadmin.

The available server roles are defined here:

http://msdn.microsoft.com/en-us/library/ms188659%28v=sql.100%29.aspx

A login with this level of security should be a very rare thing, restricted to the DBA's for your server, perhaps even just a few senior ones depending on your setup.

You need to understand the difference between a login and a user.

A login will log you in to the instance, once there, your permissions are defined either by server level roles assigned to the login, or database by database as users are created in each database and assigned back to the login.

There's an article here that covers some of the concepts:
http://www.akadia.com/services/sqlsrv_logins_and_users.html

You should never assign a user login a powerful server level role.  (Well, never is a strong word, I'm sure there are some third party applications that "require" it for whatever reason.  Let's say "rarely" instead of never).

If you have three databases db1, db2 and db3, you could create a user test1 that logs into the instance.

Then you could create a user in db1 and db3 linked back in each case to the login test1.

When you login to the instance with test1, he'd be able to see all three databases in the list of DB's to the left, but would only have access to db1 and db3.

Here's some other documentation links from BOL:

Creating a login: http://msdn.microsoft.com/en-us/library/aa337562%28v=sql.100%29.aspx
Create a database user: http://msdn.microsoft.com/en-us/library/aa337545%28v=sql.100%29.aspx
A generic link page in BOL to Access Control entries: http://msdn.microsoft.com/en-us/library/bb510418%28v=sql.100%29.aspx

If you have any other questions, please ask.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
In the properties of test on the left are the other tabs that let you assign permissions to all databases or just tspme
0
 
JElsterAuthor Commented:
I created test under  SECURITY / USERS

On the tab is GENERAL, SECURABLES, and EXTENDED PROP
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
I don't have it in front of me, one has sysadmin. If you check that it will have SA like permissions
0
 
JElsterAuthor Commented:
doesn't have that
0
 
David ToddSenior DBACommented:
Hi,

As a note, when using the MS supplied scripts to script out logins between servers, it does NOT add in the assigned server roles.

HTH
  David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.