Avatar of lbjamison
lbjamison
Flag for United States of America asked on

Giving a Programmer full access to a specific database (and no access to others) including object explorer

I'm trying to set up a user who can have pretty much full access to a specific database.  He could view all objects and have read-write-delete access.  Most of what you would have as sa.  The login is for development purposes.

I created  a server user with the default database assigned.

I create the same user for the database and added him to the dbo_owner role and he can access the database fine in a query window.

The user cannot see the database or any other using Object Explorer in SSMS.  I need for him to be able to use object explorer.

SQL Server 2008.
Microsoft SQL Server

Avatar of undefined
Last Comment
lbjamison

8/22/2022 - Mon
Andrei Fomitchev

Although the terms login and user are often used interchangeably, they are very different.

    A login is used for user authentication
    A database user account is used for database access and permissions validation.

It looks like you create User but not Login.

You should (with SSMS) as DBA:
1. Connect to your MS SQL instance.
2. Security / Logins / Right Click / New... /
3. On the page "General": Login name: <name>  / Choose authentication and set password if needed / Default Database: <database>
4. On the page "User Mapping":  Choose DB / Select role: db_owner

If you used
Databases / <your DB> / Security / Users / New User ...
You still need DB Login.

When you create Login (stept 1-4) above the User will be created automatically and usually there are no issues with access.
lbjamison

ASKER
Yes, that's what I was doing.  Sorry about the improper naming.  Just to be sure I created another login exactly as you mention here.

I'm still getting the same error when I try to open ssms as this user.  See attached screen shots. Error when attempt to expand databases in Object Explorer Screenshot that shows the database assigned to the user and the one that's causing problems.
ASKER CERTIFIED SOLUTION
Andrei Fomitchev

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
lbjamison

ASKER
There are no references in DemoGolf_842 to Inge_839 or any other databases.   I tried granting access for the login to Inge_839 and then got the same error with a different database.  (it seems like ssms is confused and thinks that the login wants to access other databases.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Racim BOUDJAKDJI

change the default database login to master.
lbjamison

ASKER
That didn't handle.  But I searched for the SQL Server error and found this:

This suggestion solved my problem:

Please try using the following workaround and let us know if that helps:
1) Bring Object Explorer Details window by selecting View --> Object Explorer Details in menu (or hitting F7)
2) In Object Explorer window click at Databases folder
3) In Object Explorer Details window right-click at the column header and deselect Collation
4) Refresh Databases folder.

Doing this worked.  According to the thread, SP1 handles this as well.  Here's a link to the thread.

http://connect.microsoft.com/SQLServer/feedback/details/354291/the-server-principal-is-not-able-to-access-the-database-under-the-current-security-context-microsoft-sql-server-error-916#details

Thanks for your help.