Aleks
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
Which steps do I need to take to fix this so that I can create the user?
error-read-only-.PNG
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you show how are you trying to grant the db_reader role?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Here's a simple overview of a MSSQL instance structure:
MSSQL instance
- Logins
- Databases
- Users
- Roles
- Schemas
- Tables
- Views
- Stored Procedures
- Functions
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ill try this now...
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.
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.
ASKER
Correct. Done. I added it but if I select the role of datareader I get that error.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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
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.
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.
ASKER
Here you go.
DBProperties.PNG
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 existsAnother 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
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
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),'')='')
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
second-error.PNG
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.
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.
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
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
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%'
Can you post a print screen of your logins?
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.
I'll confirm.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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