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.

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.

ASKER CERTIFIED SOLUTION

Andrei Fomitchev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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.