[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL 2008 - Permissions problem

Posted on 2013-01-19
6
Medium Priority
?
242 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 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 300 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 1

Author Comment

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

Accepted Solution

by:
Steve Wales earned 1200 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

872 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