• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

SQL 2008 - Permissions problem

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?

2 Solutions
Aaron TomoskyTechnology ConsultantCommented:
In the properties of test on the left are the other tabs that let you assign permissions to all databases or just tspme
JElsterAuthor Commented:
I created test under  SECURITY / USERS

Aaron TomoskyTechnology ConsultantCommented:
I don't have it in front of me, one has sysadmin. If you check that it will have SA like permissions
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

JElsterAuthor Commented:
doesn't have that
Steve WalesSenior 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:


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:

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.
David ToddSenior DBACommented:

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

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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now