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
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
The error message is saying that you're trying to drop a member from the db_owner role.
There's nothing about adding an user.
1
Jose TorresSenior SQL Server DBACommented:
When you added the first users as db_owner, did you grant that user db_owner or made that user the owner of the db_owner role
0
AleksAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you show how are you trying to grant the db_reader role?
0
Jose TorresSenior SQL Server DBACommented:
Also can you run the following query against the database you are working on to verify what the roles, role owner and members show
SELECT p1.name AS Role, p2.name RoleOwner, p3.name AS Member
FROM sys.database_role_members m
LEFT JOIN sys.database_principals p1 ON m.role_principal_id = p1.principal_id
LEFT JOIN sys.database_principals p2 ON p1.owning_principal_id = p2.principal_id
LEFT JOIN sys.database_principals p3 ON m.member_principal_id = p3.principal_id
ORDER BY 1,3

Open in new window

0
AleksAuthor Commented:
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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
To be honest I'm not used with creating users using the GUI.
I usually run this steps (open a new query window):
USE databaseNameHere
GO

 -- Only run the following line if the user is not created yet
CREATE USER [UserNameHere] FOR LOGIN [LoginNameHere]
GO

ALTER ROLE [db_datareader] ADD MEMBER [UserNameHere]
GO

Open in new window

0
AleksAuthor Commented:
And the password ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
0
AleksAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I am trying to create a login with read only permissions
I just made a test and ran perfectly:
USE [master]
GO
CREATE LOGIN [ABC] WITH PASSWORD=N'1', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [ABC] FOR LOGIN [ABC]
GO
USE [AdventureWorks]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ABC]
GO

Open in new window

NOTE: Replace AdventureWorks with your local database name and replace ABC with the login name you want to be create.
0
Jose TorresSenior SQL Server DBACommented:
0
AleksAuthor Commented:
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'
0
Jose TorresSenior SQL Server DBACommented:
Give us a screen shot so we can see what youre looking at
0
AleksAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Again, I made the test with T-SQL script and no error:
USE [AdventureWorks]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ABC]
GO
USE [AdventureWorks]
GO
ALTER ROLE [db_owner] DROP MEMBER [ABC]
GO

Open in new window

0
AleksAuthor Commented:
Ill try this now...
0
AleksAuthor Commented:
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.
0
AleksAuthor Commented:
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.
0
Jose TorresSenior SQL Server DBACommented:
The user must be added to the sql server instance "server level" first before you can assign that user any rights on databases.
0
AleksAuthor Commented:
Correct. Done. I added it but if I select the role of datareader I get that error.
0
Jose TorresSenior SQL Server DBACommented:
1. Can you verify that the database owner exists. Run the following query against master db.
select d.name as dbname, p.name onwer_name, p.type as owner_type
from sys.databases d
left join sys.server_principals p on d.owner_sid = p.sid

Open in new window

2. Can you run the following query and post the results against the db that is giving you the problem
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

0
AleksAuthor Commented:
The database owner exists. I added it first. After I add it I can't add a database READER
0
Jose TorresSenior SQL Server DBACommented:
Not db_owner but the owner of the database. Also db_owner should not need to be added its is permanent db role
0
AleksAuthor Commented:
Can I check for this with the enterprise manager instead of running a query ?
0
Jose TorresSenior SQL Server DBACommented:
Run the queries it's easier to post the results back to us.
0
AleksAuthor Commented:
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
0
Jose TorresSenior SQL Server DBACommented:
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.
0
AleksAuthor Commented:
Here you go.
DBProperties.PNG
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
Jose TorresSenior SQL Server DBACommented:
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
0
AleksAuthor Commented:
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.
0
Jose TorresSenior SQL Server DBACommented:
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

0
AleksAuthor Commented:
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
0
AleksAuthor Commented:
I have a meeting. Will be back in about an hr ... but I wasn't able to set the owner. It was indeed blank.
0
Jose TorresSenior SQL Server DBACommented:
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.
0
AleksAuthor Commented:
We need mixed authentication because a WebApplication connects to it and that is what the read only user is for.
0
Jose TorresSenior SQL Server DBACommented:
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
0
AleksAuthor Commented:
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.
0
Jose TorresSenior SQL Server DBACommented:
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

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post a print screen of your logins?
0
AleksAuthor Commented:
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.
0
AleksAuthor Commented:
I think the DB role is missing for some reason. I will check tonight but I think it that's the issue.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
amucinobluedot, any news about this issue?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.