Solved

SQL 2008 - Permissions problem

Posted on 2013-01-19
6
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

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 39

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help Required 3 117
MS SQL + Insert Into Table - If Doesnt Exist 9 58
Downgrading MS SQL 2008 R2 Enterprise to MS SQL 2005 Standard? 12 84
convert null in sql server 12 57
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

749 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