The Database Principal Owns A Schema In The Database, And Cannot Be Dropped.

Feb 15, 2006

Trying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?

The below is the TSQL that generates the my issue.



Use [master]
go
create database [test]
go

USE [test]
GO

CREATE ROLE [ReadOnlyRole]
GO

USE [test]
GO

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole]
GO

drop role [ReadOnlyRole]
go

View 12 Replies


ADVERTISEMENT

SQL Server 2005 Express: The Database Principal Owns A Schema In The Database, And Can Not Be Dropped.

Jan 11, 2006

I recently added a new user to my database.  Now I want to delete that user, but I keep getting the error above.  What do I need to do to delete my recently added user?

View 4 Replies View Related

Trying To Drop A Login That Owns A Database

May 1, 2008

I am trying to drop a login but the system is telling me.
quote:Msg 15174, Level 16, State 1, Line 3
Login 'Mark' owns one or more database(s). Change the owner of the database(s) before dropping the login.

Question:
But how do i check to find out which objects or tables that this login is associated with.

Dallr

View 6 Replies View Related

GRANT .... AS &&<database Principal&&>

Apr 19, 2008




I am trying to grant various users permissions and am using the following sql command


GRANT Delete ON Resident TO hector AS barbara


barbara is a member of the fixed server role sysadmin and has Delete permission on the Resident table.

I log in to the server as barbara and try and execute this command and get:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'Resident', because it does not exist or you do not have permission.


If I just run

GRANT Delete ON Resident TO hector

then everything works fine but the grantor is then dbo. I want to be able to have it recorded that barbara was the one that actually gave hector permissino to delete on the Resident table. Is there a way to do this?

View 2 Replies View Related

In What Situations Principal Database Might Become Unavailable ?

Dec 13, 2006

Is it correct to say

in any case exept when data or log file not available pricipal db will be availabe

and manual failover could be done only from from principal?











View 1 Replies View Related

Database User Cannot Be Dropped

Mar 2, 2005

Hi..I just restored a databae from backup. After restoring, as usual, from the Enterprises manager, I want to delete the database user and readd again from the DB login name. But when I tried to delete the DB user, it prompts me the following.

"the selected user cannot be dropped because the user owns objects"

why? I never encountered this b4 ? I restored DB again but problem still the same. Any ideas?

View 2 Replies View Related

Recovering Dropped SQL Database

Sep 21, 2006

I am in a big trouble ,

accidently i have issued a DROP DATABASE XXX command in the sql client, thinking its a local server....... The whole 4 months of database database is now dropped.





Please help me , how can i recover the database ....



thanks in advance

View 1 Replies View Related

The Database Principal Is Set As The Execution Context Of One Or More Procedures ...

Aug 14, 2007

I have database in the database there are a few users that no one is used. When I try to drop thpse users I got next error message:
"The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped." (Msg 15136)

Indeed, I think that those users have execute rights on store procedures.
How do I find for wich procedures or other database objects those users have grants?
How do I delete them from database (and maybe from logins of the server)?
How can I see what grants a user has?
How can I see what grants does STP has?

View 10 Replies View Related

Code To Programmatically Failover A Principal Database.

Mar 12, 2008



Hi Guys,
I was wondering if there is a system procedure / T-SQL code to programmatically Failover a Database from Principal to mirror mode?

also,
We noticed that at times few of all the databases that were kept on 'Principal' mode get randomly 'Failover'. Does anyone have any idea?

If so, pls respond.

Thank you.


View 9 Replies View Related

Database Connection Timeout / Dropped

Jan 25, 2013

My sql server is setup to use windows authentication. From time to time one of the applications on that server would have issues connecting to that server. I changed it to use sql authentication and all of a sudden all the problems went away.

View 8 Replies View Related

The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.

Sep 27, 2007

Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'.  However, the
current database schema is not compatible with this version.  You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!

View 4 Replies View Related

Royal F-up, Dropped User Default Database

Jan 27, 2004

I am by no means a db administrator, so I apologize for my ignorance. However I do know that I screwed up and am unable to find the solution...

The small company I work for has a development SQL Server where we do testing and development for web based applications. While using SQL Server Enteprise Manager I accidentally dropped the user's default default database.

I have always been able to access the enteprise manager via windows authentication, but now I am unable to. Is there any way to set the default database back to 'master'? Currently I cannot connect with SQL Query Analyzer for the same reasons, and do not have any other access except via windows authentication. Is there any hope? Any help is much appreciated.

View 14 Replies View Related

Setup And Upgrade :: Both Database Went To Suspended Mode In Principal And Mirror

Aug 10, 2015

I have checked my Production databases, both are went to Disconnected state, both are unable to connect to the application, i checked all the services and its working fine. and restarted the end points on Principal and Mirror, still issue not solved, after restart the witness server End point both the databases are working fine.

View 3 Replies View Related

SSDT Database Users Being Dropped If Not In Source With DropObjectsNotInSource

Sep 18, 2012

URL>..n how to deploy (AKA publish) without dropping the users with the DropObjectsNotInSource enabled.  With the SSDT September 2012 release I have been testing I seem to be able to ignore permissions and role memberships but not users.In my situation and that of several other folks I have talked to it is a problem trying to manage this.  I am thinking of scenarios where there could be (1..n) databases needing the current build (where n is big and not a static number) and need to be updated but the database Users are different for several reasons.

don't have to manually use schema compare to identify objects to drop when we deploy and not drop database users at the target?  Because of the dynamic nature of these databases (i.e. 1-n might be on a different release than the current build) we would like to manage security at the source and not the project or post deployment script level.From the MSFT folks is there any hope that we will get a parameter to ignore users when DropObjects otInSource=true in an soon to be released version of SSDT?

View 14 Replies View Related

SELECT Permission Denied On Object 'TableID', Database 'Database', Schema 'dbo'

Mar 21, 2007


The error message:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'TestID'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

The log file reads:

---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.

***Background***

General Users got an error message when trying to access any reports we have created.
All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors


I'll add this from the report logs...

w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed.
Login failed for user €˜DOMAINUsername€™.

The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.


I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)

View 3 Replies View Related

The Server Principal XYuser Is Not Able To Access The Database Ydb Under The Current Security Context

May 23, 2007

SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:



The server principal "XYuser" is not able to access the database "Ydb" under the current security context



This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john

View 17 Replies View Related

Windows Server Principal Denied Access To Database Under Current Context

May 1, 2007

Hi guys. I created a database "MyDatabase" using an AD Account "user01" which is a member of a domain group "GroupA". I have created a login in my SQL Server 2005 for this AD group. I have also added the said login as a user of my database. I created other AD users and put them in the same group. When I try to access the database using the other users, I get this message

The server principal "MyDomainuser02" is not able to access the database "MyDatabase" under the current security context.

This KB tells me that my situation is triggered by the first case in which the owner of the database is windows authenticated. I have already set the database to be TRUSTWORTHY. I haven't tried CERTIFICATES though. I believe I haven't done anything complex to warrant this solution.

I'm really lost with the myriads of principals and securables that SQL2005 has. All I want is just to execute a certain set of stored procedures. I can't do this because I can't even execute "USE MyDatabase". What are the permissions I need to give to "GroupA" ?

View 5 Replies View Related

SQL Server Admin 2014 :: Schema Change History - Identify Who Dropped A Table?

Jun 30, 2015

How can I easily identify who dropped a table?

View 8 Replies View Related

How To Export Whole Database Schema To Another Database But Not Include The Data?

Sep 21, 2007


I need to export a database, x, of a server, X, to another database, y, of a server, Y and I need export the database schema only, not include the data.
Does anyone know how to do that?
Many thanks for replying.

View 7 Replies View Related

Database Schema Compatible With Schema Version '1'

Apr 12, 2008

Hello everybody!I'm using ASP.NET  3.5,  MSSQL 2005I  bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?

View 2 Replies View Related

Mirroring :: Principal Database Get Role Back After Being Back On Line

May 14, 2015

New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.

When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?

View 2 Replies View Related

Database Schema

Apr 28, 2005

What would be the best way to get the Schema from a complete database.

Thanks.

View 1 Replies View Related

How To Get All Database Schema?

Sep 9, 2005

hello,

here is my problem.
I have to rebuild database after crash. there is no backup. So
I did a bcp to get data from. But I do not have the original database so my question is how to get the full schema off the data base, tables,colomns,stored procedures etc...

thanks lot for any help.

View 1 Replies View Related

Help For Database Schema

Mar 16, 2007

Hello All,

My project uses MS SQL server database and is not too big database (have aound 200 table).

Now I have to create Database schema for my database as my project needs to be integrated with some other product.

I don't know much about database schema and how to start with it.

Can someone please give me some inputs on following:

1) What exactly database schema should include?

2) How should I start creating the schema for my database?

3) Are there any tools for doing this?



Thanks in advance

View 12 Replies View Related

Sql Database Schema

Oct 24, 2005

Hi. I would like to retrieve the table names of a database, the column names nad its contraints of each table in a database.. How can this be achieved???

View 14 Replies View Related

Database Vs Schema

May 24, 2007

We currently have a product in which each client has their own Database. We adjust the connection when a user for that client logs into the system. This system has continued to grow and a good pace, but we have come to a point where failover is taking too long.



Refactoring the Database to handle multiple sites in a single database is not an option because of the time it would take to make the change. So, we are looking for another way in which this could be handle. One idea is to take multiple clients and place them in a single database using a schema to seperate them. (ex. Client A = server1.db1.schema1, Client B = server1.db1.schema2, etc).



Is there another option that would be better, or what kind of performance can we expect if we follow this path? Or, is there a way to decrease the failover time (it appears the problem is the startup of the database on the failover server)?



Thad

View 4 Replies View Related

RS Database Schema?

Oct 26, 2007



We have a RS 2000 server with a over a hundred reports, and about half as many weekly and monthly subscriptions.
In reply to my request to upgrade to RS2005, the boss asked me today for a compete list of the reports, who subscribes to them, and their delivery frequency.

He was not interested in paying for a VB or C# development effort with the SOAP API that it would require to obtain a simple list from a SQL server database, since he already has SQL programmers on staff.

So how can I get this list? Anyone know of any demo code out there?

Forgive my sarcasm and Thanks in advance.

Herb

View 4 Replies View Related

Changes To Database Schema

Oct 23, 2007



Does anyone know which database table/view one can query to get a list of all objects that have been changed by users accessing a database?

Is there any? or is there some other way of doing this?

View 1 Replies View Related

Exporting Database Schema

Sep 26, 2006

Is there a way for VS2005 to export the database schema of a .mdf into a .sql file? Preferably from a Web PRoject, not a Database Project, but right now I am anything but picky, so whatever works. Thanks. 

View 6 Replies View Related

SQL Database Schema Sync

Oct 15, 2007

SQL Database Schema Sync
We have development and production databases, when we change database schema in development database how can I synchronize it to production without creating data problems in production, is this possible in SQL2005? Or any external tool I can use?
 

View 1 Replies View Related

Replication Of The Schema Of A SQL Database

Nov 12, 2007

Hi all,
I am doing a replication of a SQL Server 2005 database from one server to another server, everything worked fine. Now, when I added some fields to this database, the replication stopped. can I do replication of a schema of a SQL database? how? Should the replication work even if I add some fields.

View 3 Replies View Related

Copy The Database Schema

Feb 4, 2002

Hi,

Can anyone help me to figure out how to generate the SQL Script that can produce the same database schema with the existing database?

I have tried the "Generate SQL Script" tool. However, it only creates the object such as tables, stored procedures, triggers and etc. I still have to create the database first in order to run that script. I have an existing database that has a 2.7 GB size (let's called this ABC Database). In a different server, I would like to create the same database with the ABC DB without having to restore the ABC DB. Bottom line, I would like to see the script creates the database along with all stored procedures, triggers and the size of the database will be 2.7 GB. Is it possible?


Thank you very much!
Jane

View 2 Replies View Related

Database Schema Exported......

Jul 10, 2002

MS SQL 6.5
NT

I have been asked to export the database schema (containing table names, field names, data types, field lengths, field defaults, nulls, etc) to an MS Excel spread sheet. "sp_help filename" provides some data but the format is difficult to manage.

All help with this matter would be greatly appreciated....


Thanks in advance.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved