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
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?
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.
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?
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?
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 ....
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?
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.
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!
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.
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.
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?
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!?!?)
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
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" ?
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.
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?
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?
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...
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???
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)?
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?
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.
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?
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.
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?
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....