6

I just created a new web application that accepts some form based input from the user and inserts it in into the database. To go along with this I created a new user in the database and initially assigned the user to two roles and schemas ...

db_datareader
db_datawriter

After thinking things over I realized the user did not need to be part of the db_datareader role because the user only inserted data into the database and never read any. So I went back and removed the role of db_datareader and noticed that the schema options were grayed out. I could not remove the user from schema ownership of db_datareader.

How does one go about removing a user from ownership of a specific schema? Should I have even assigned schema ownership in the first place?

I am logged in as administrator of the SQL Server and of the Windows 7 OS.

webworm
  • 10,587
  • 33
  • 120
  • 217

2 Answers2

13

I had same issue today and found a way to remove user as owner of schema. Open Schema node below Security in database and change the owner for the user that you mistakenly used with the default owner. For example if some oddball user name is listed as schema owner for db_datareader, change it to dbo (the default) and so on.

Doreen
  • 714
  • 2
  • 14
  • 36
  • 1
    The only thing I'd add is to refresh the database node in Sql Mgmnt. Studio (right click|refresh) after making Doreen's changes or the sql user mistakenly assigned to the schema will still display the schemas membership checked while sql mgmnt studio is still open. Not necessary but can avoid the incorrect perception the fix didn't work. – JimSTAT Jan 15 '14 at 02:46
6

No, you shouldn't have assigned ownership of the schema to the user. You should have just made the user a member of the schema. Revert ownership to dbo and check that the user account is not still a member of the schema.

HardCode
  • 6,497
  • 4
  • 31
  • 54
  • Thanks. I did what you suggested and the user is no longer owner of the schema. I find the who concept of schema ownership confusing. Any suggestions for resources or tutorials that cover this area? – webworm Mar 04 '11 at 20:40
  • 1
    Well, I attended a training class for SQL Server 2008. Before that, I was confused about what schemas are all about. What I really found out is that if you need granular permissions on objects (i.e. more than one user can access (or not access) objects in a schema), then the schemas really become nothing more than logical containers like namespaces - useful for only the developers. Otherwise, if an account can access all objects in a schema, adding more objects to the schema implicitly grants the schema member the configured access rights. – HardCode Mar 04 '11 at 21:30
  • Personally, for my internal corporate apps using Active Directory security, I find schemas only useful as namespaces for us devs. I create database (or application from now on) roles and assign the roles permissions to the objects. – HardCode Mar 04 '11 at 21:32