Solved

SQL 2008 - Permissions problem

Posted on 2013-01-19
6
204 Views
Last Modified: 2013-01-20
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
0
Comment
Question by:JElster
6 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 38797168
In the properties of test on the left are the other tabs that let you assign permissions to all databases or just tspme
0
 
LVL 1

Author Comment

by:JElster
ID: 38797179
I created test under  SECURITY / USERS

On the tab is GENERAL, SECURABLES, and EXTENDED PROP
0
 
LVL 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 100 total points
ID: 38797212
I don't have it in front of me, one has sysadmin. If you check that it will have SA like permissions
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:JElster
ID: 38797226
doesn't have that
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 400 total points
ID: 38797300
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
 
LVL 35

Expert Comment

by:David Todd
ID: 38797703
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now