Assign role of db_denydatawriter to user dbo

When I try to do this I receive the following error message:

Add member failed for DatabaseRole 'db_denydatawriter'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot use the reserved user or role name 'dbo'. (Microsoft SQL Server, Error: 15405)

I have a database that needs to remain 'frozen'. I want to maintain ownership of the database, but prevent myself (as dbo) from accidentally modifying the data. Is there another way to accomplish this?

EDIT: I've decided to open a new question to address the lines below:
The data in the database needs to remain frozen. However, the structure might change.

The database is actually several databases. They are essentially snapshots of the production database taken semi-annually. Users are then able to view historical data by switching the back-end database from the front-end GUI. However, sometimes new fields get added to the tables in the current database. This can cause problems if the front-end expects these fields to be present. Our current solution is to add the fields on the fly (users have db_datareader, db_denydatawriter, and db_ddladmin roles). What this means is that the database cannot be made read-only because that would prevent changes to the table structure.

-------------Problems Reply------------

set the db to read only:


Category:sql server Views:3 Time:2010-02-05
Tags: sql server dbo

Related post

  • Assigning Roles to Users 2009-02-22

    I'm having trouble figuring out how to assign roles to users. I've got the log in mechanism working, but need to figure out how to use roles defined to give users certain access. I have a database with these tables: Tables ------ UserTbl RolesTbl Use

  • ASP.NET MVC Assign Roles when using custom authentication 2011-05-19

    I am using MongoDB to store users and passwords (instead of sql). I have my own mechanism for assigning roles to users. I would like to decorate my controller actions with roles, as follows: [Authorize(Roles = "Administrators")] My login code looks l

  • Assigning roles programmatically in grails using acegi 2010-02-16

    could someone provide me with a snippet that assigns a given existing acegi role (Authority) to a Person in Grails ? Thanks. --------------Solutions------------- How's this to add a role to a user: def role = Role.findByAuthority("ROLE_USER") def use

  • How to assign role with PHP in Drupal? 2010-08-20

    I need to assign a kind of "premium status" to members that purchase an amount equal or greater than 100 € via PHP. Conditional Actions are already set up (user = anonymous/authenticated AND total amount = equal/greater than 100 AND user =! premium)

  • How to assign role to a new user in bootstrap with grails spring security? 2011-07-04

    In my grails app i am using spring security plugin..I have registration form and registered users will be stored in db table. in bootstrap i have created new role as, def PhysicianRole = new Role(authority: 'ROLE_PHYSICIAN').save(flush: true) Then i

  • ASP.NET Assign Role by CheckBox 2012-01-26

    I am using VS2005 C#. I have a list of users and 2 checkboxes. The checkboxes each indicate Administrator and User. When a user is selected, the respective checkboxes will be checked according to the roles assigned to the user. Now I am trying to add

  • Tomcat - Assign roles based on client ip address in my custom realm 2012-02-28

    I am trying to create a custom realm based on certificate authentication. Basically I want to allow clients to connect if they present a trusted certificate. This is no problem and there are plenty of examples on how to do this. However, I need to as

  • Welcome file chosen based on assigned roles (Java web app)? 2009-08-03

    I have a Java web application running on JBoss using JAAS for authentication. I would like to dynamically select the page a user logging in is shown based upon their roles as I have disjoint sets of users that shouldn't have access to the same pages.

  • Store/assign roles of authenticated users 2009-11-30

    I am upgrading a site to use MVC and I am looking for the best way to set up Authentication. At this point, I have the log-in working off of Active Directory: validating a username and password, and then setting the Auth cookie. How do I store the us

  • Assign role or grant permissions for WP user then he collects some points 2011-07-17

    Lets begin from the start: I want to make a website where users could generate content. It will be based on Wordpress and am using TDO Mini Forms to let users submit content. I have also added CubePoints plug-in to grant points for my users. I want u

  • assigning roles to a user/Session from mysql 2011-11-09

    What I have is a two tables. One table with users and another table with user roles. I know how to get the data out of the mysql DB, but how do I add all of those roles to my user? I would normally thinking Session.Add but the problem is i have anywh

  • What is the best practice to assign roles in a many-to-many relationship in ruby? 2012-01-16

    I am trying to build a soccer game to experiment with ruby. The associations are below: Match.rb class Match < ActiveRecord::Base belongs_to :home_user, :class_name => 'User', :foreign_key => 'home_user_id' belongs_to :away_user, :class_name

  • Drupal 7: assigning custom created user role to newly created user AS DEFAULT 2011-07-19

    I need newly created users to be assigned to a particular role as default. as default, a new user automatically assigned as "authenticated user" I also need as "content admin (custom created role)" any idea how to do that? appreciate helps!! Why I ne

  • Assign Dynamic Roles to Controller in MVC 2014-12-23

    I using MVC5,EF6,Identity 2.0 Query: Authorize user with dynamic Role Name which will be added by Admin from back End. The (Authorize [Roles="A,B"] means Either A or B but can i do like A and B? [Please suggest if you have an better idea] My Work: Pl

  • How to create schema that have an access as that of dbo and can be accessed by sa user 2010-06-08

    I am new to schema, roles and user management part in sql server. Till now I used to work with simple dbo schema but now after reading few articles I am intrested in creating schema for managing my tables in a folder fashion. At present, I want to cr

  • Roles for "Hhtpcontext.user" in Formauthentication 2009-07-19

    In global.asax, where should i write code to assign role in httpcontext.user? What i have read in site is that it should be part of "Application_onPostauthentication" event of global.asax but i am not getting that if i write there then it is

  • How do I manually set a user's role in ASP.NET MVC? 2009-08-19

    This project I'm working on requires me to keep a local db of admin users and use an external db for regular users. Anyone who passes authentication in the admin db should be assigned the 'admin' role, and anyone authenticated through the other db wi

  • Is it possible to use AzMan for role based authorization on objects which are created at runtime? 2009-08-24

    Is it possible to use AzMan for role based authorization on objects which are created at runtime? If yes how can this be done? For Example: If an object of class "CustomAlert" is created at runtime, I am trying to see if I can have different rules fo

  • ASP.NET - Membership and roles 2009-08-27

    How to get user roles from Membership provider? Suppose I have a username and his/her password and now I need to retrieve his/her assigned Roles. How can I do that? --------------Solutions------------- You want to call the Roles.GetRolesForUser metho

Copyright (C), All Rights Reserved.

processed in 0.133 (s). 11 q(s)