When you create a new login in SQL Server it selec

问题:

I am having some trouble creating a new SQL user in SQL Server 2008 R2. When I use SQL Server Management Studio it checks db_owner role membership by default. I just want to create a new sql user with read only access. Even with the following raw SQL it still creates the user with db_owner level permission.

<!-- language: lang-sql -->
CREATE LOGIN readonlyuser
WITH PASSWORD = '12345',CHECK_POLICY = OFF, DEFAULT_DATABASE=mydatabase
GO
USE mydatabase
GO
CREATE USER readonlyuser FOR LOGIN readonlyuser
GO
EXEC sp_addrolemember 'db_datareader', 'readonlyuser'

Now if I log on to SQL Server Management Studio with newly created user I can basically access any table and modify any data any way that I want. This is exactly what I not want to do. I want only to be read data and not to modify any data

Strange thing is if I look at the roles for database the readonlyuser is inside db_datareader and not in db_owner.

So why is SQL creating this user with db_owner level permissions, thus allowing the user to modify data?

Update 2013/08/07

This seems to be happening with just one specific database. I created brand new database and created bunch of tables and then ran the same script above and it is working perfectly fine. But if i try with the actual database where i need this change, it doesn't work like that. It created the user and gave way too much permission.

Is there anything that i can check on that database? Please note that specific database was not designed by me. It is from a 3rd party vendor. So not sure exactly what modifications they might have done.

Any help is greatly appreciated.

回答1:

I ran into the same issue.

The solution: sp_changedbowner. That fixed it. (Somehow the owner was corrupted)

回答2:

I don't see any problem in query. And it worked as expected when i tested it. Just for confirmation, verify if user is properly mapped to required database (mydatabase) and db_datareader is selected from user property window.

回答3:

I found that this happens to me when adding logins to databases that I restored to a new server. In the Files tab of the database's properties, the owner field is blank, even though it lists a user in the General tab. If I populate the owner field with a valid login, then the problem is resolved: new logins don't have "db_owner" checked by default.

回答4:

To expand on Greg's comment, this probably happens when the restored database was owned on the source server by a login that's not present on the current (the destination) server. The info on the General tab is the original owner (on the source server), which cannot be reflected on the Files tab, since that logion is missing on the current server. In a certain sense this is an "orphaned database" then.