Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

Error when adding read only user

I have a database MS SQL 2008. I added a user as 'owner' and had no problem doing so, but when I tried to add a new user as 'read only' I get the error below.
Which steps do I need to take to fix this so that I can create the user?
error-read-only-.PNG
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

I added a db_owner
Then I tried to add a DB reader but it sets it to be owner, it won't let me set it to reader, hence the error
Can you show how are you trying to grant the db_reader role?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

Using enterprise manager in security I add a user. Under user mapping I select the database then the role reader.
But then it defaults it as owner.
Seems like the role reader doesn't exist in the database.
Is this possible ?  If so how to add such role to the database ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

And the password ?
Password is for the Login and I'm assuming it already exists otherwise you'll need to create a login first.
Here's a simple overview of a MSSQL instance structure:
MSSQL instance
  - Logins
  - Databases
      - Users
      - Roles
      - Schemas
          - Tables
          - Views
          - Stored Procedures
          - Functions
Avatar of Aleks

ASKER

Ok. Then I guess I am using the wrong term. I am trying to create a login with read only permissions. But it won't let me. It only allows me to create login for the role owner and not reader.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

I know how to create an SQL login. What I don't know is why on some of my databases when I check the box for read only it sets it back to 'owner'
Give us a screen shot so we can see what youre looking at
Avatar of Aleks

ASKER

Here it is. On the databases where I have this issue when I go to the UserMapping page it already checks "db_owner" by default.
On the databases where I do not have the issue shows the checkboxes blank and I have no problem creating the user.

In this screenshot you when I opened the UserMapping db_owner was checked, I unchecked it and checked db_datareader. when I click OK I get the attached error. If I leave it as owner then no error is displayed.
Capture.PNG
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

Ill try this now...
Avatar of Aleks

ASKER

The above code does not set any password for the login. So I don't think that would work, nor does it set its default database, etc.
Avatar of Aleks

ASKER

Also, is it better to add the user at the database level or at the server level. Under each database there is a 'security' area, but there is also one at the server level right under "Databases". Seems I can add it in either place.
The user must be added to the sql server instance "server level" first before you can assign that user any rights on databases.
Avatar of Aleks

ASKER

Correct. Done. I added it but if I select the role of datareader I get that error.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

The database owner exists. I added it first. After I add it I can't add a database READER
Not db_owner but the owner of the database. Also db_owner should not need to be added its is permanent db role
Avatar of Aleks

ASKER

Can I check for this with the enterprise manager instead of running a query ?
Run the queries it's easier to post the results back to us.
Avatar of Aleks

ASKER

The first script returns NULL for ALL my databases. For both owner_name and owner_type.
That one ran on all my databases.

The second one I ran on the DB I am having the issue with (One of them). and got this.

dbo      1      dbo
guest      0      guest
INFORMATION_SCHEMA      0      
sys      0
The only way that the first query would be if none of the users that own the database exist.

On the second query this entry that I was looking for or not looking for
dbo      1      dbo

Can you right mouse click on the database and select properties and send the screen shot.
Avatar of Aleks

ASKER

Here you go.
DBProperties.PNG
The above code does not set any password for the login.
Why do you need to set the password? The login doesn't exist and has already a password?

Also, is it better to add the user at the database level or at the server level.
Ok, it's better you use the correct terminology or it will be hard for us to understand you.
A Login is set at server level.
An User is set at database level.
Usually an user need to have a login associated.
So, what you have exactly now? Just a login (no database user) or a login and an user?

The database owner exists
Another terminology that you need to use correctly. A database owner is different from a user that is member of db_owner role. You can only have one and just one database owner (user dbo) but many members of db_owner role.
So, the database really have a dbo user?
Chances are that this database was recently restored to this server and the original id from the backup is not present on this server and new owner defaulted to the user performing the restore.

Right click on the database
Select properties
Select page "Files"
The owner is probably blank
Put in DED2636\Administrator
Click OK

Try adding the user to the database
Avatar of Aleks

ASKER

I guess I need to add a login for a member that will be a database reader. I can do that in most of my databases but those that give me the error posted above.
Once you do the above re-run this query on the database and the dbo entry should now be gone.
SELECT u.name AS [Name],
CAST(CASE dp.state WHEN 'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess],
ISNULL(u.default_schema_name,'') AS [DefaultSchema]
FROM sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id and dp.type = 'CO'
WHERE (u.type in ('U', 'S', 'G', 'C', 'K'))and(ISNULL(suser_sname(u.sid),'')='')

Open in new window

Avatar of Aleks

ASKER

Yep. Jose .. we are on to something. All my other databases have:  DED2636\Administrator In that field. BUT when I try to add it to this particular database I get the error attached. (Using the exact same user as the other databases).
second-error.PNG
Avatar of Aleks

ASKER

I have a meeting. Will be back in about an hr ... but I wasn't able to set the owner. It was indeed blank.
This ID looks to be the servers local administrator group.
I am not sure what youre organizations policy is on database ownership is.
If you have mixed mode authentication change the owner to "sa" that should solve the issue unless your organization has a service account which should be used as the database owner.
Avatar of Aleks

ASKER

We need mixed authentication because a WebApplication connects to it and that is what the read only user is for.
You misunderstood.  If the server is configured with mixed mode authentication then you can change the database owner to "sa"
Unless youre organization has a different preference then use that but what ever ID you use it must be a user in the sql server instance
Avatar of Aleks

ASKER

I understand. Question is why all the other databases have that user as owner and they work just fine and this few won't accept that user as owner ?  they are all under the same server.
Just for my verification could you run the following query and post the results
select * from sys.server_principals where name like '%ded2636%' or name like '%administrator%'

Open in new window

Can you post a print screen of your logins?
Avatar of Aleks

ASKER

Nop. I can show a screen of this one login. I'll double check no other database uses the same username. It may be causing issues.
I'll confirm.
Avatar of Aleks

ASKER

I think the DB role is missing for some reason. I will check tonight but I think it that's the issue.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

Nop, nothing I was working on other things. Ill get back to this on Monday and see if I can provide with more information. So solution as of yet.