Menu
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.
696
Connect to a SQL Server instance. In Object Explorer, go to « Security » node then logins. Right-click on the SQL Server Login you want to drop then click on “Delete” SSMS will show following warning message.
To rename a schema, the user must be the owner of the schema. To change the owner of a schema, the user must be the current owner of the schema and a member of the new owner role. The new owner role must also have the CREATE privilege on the database to which the schema belongs.
To change the schema of a table by using SQL Server Management Studio, in Object Explorer, right-click on the table and then click Design. Press F4 to open the Properties window. In the Schema box, select a new schema. ALTER SCHEMA uses a schema level lock.
First, specify the name of the schema that you want to drop. If the schema contains any objects, the statement will fail. Therefore, you must delete all objects in the schema before removing the schema. Second, use the IF EXISTS option to conditionally remove the schema only if the schema exists.
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.
185
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.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
