When trying to drop a user I get ' The database principle owns a schema in the database and cannot be dropped'
How Can I get around this? How can I tell which schema the user owns. It is not listed in the properties of the user.
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedHi: I try to drop a user with following error
drop user TestUser
Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.
Then I tried:
select * from information_schema.schemata
where schema_owner = 'TestUser'
CATALOG_NAME SCHEMA_NAME SCHEMA_OWNER
---------------- ------------- ------------
DB_Installation db_datareader TestUser
(1 row(s) affected)
Drop schema TestUser
Msg 15151, Level 16, State 1, Line 1
Cannot drop the schema 'TestUser', because it does not exist or you do not have permission.
any idea?
thanks
-D
I create a new account like--- kumar
now, i login to mssql by using above account...
now i login to mssql by using another console with administrator account....
Now in administrator account, i try to delete the user kumar....
then it shows the following error message...like...Could not
drop login 'kumar' as the user is currently logged in....
but i want to delete the user even if logged in....
please help me...
bye,
I restored a user database from another server and created a script to drop and recreate the users. This has worked for me in the past to synchronize logins/passwords. Recently I have been unable to drop two users and get the following error message:
Server: Msg 15183, Level 16, State 1, Procedure sp_MScheck_uid_owns_anything, Line 17
The user owns objects in the database and cannot be dropped.
I'm unable to find any objects owned by the user and have unsuccessfully been able to drop them. Has anyone else anything similar to this error?
Hello,
I am new to SQL 2005 and I am trying to drop a user from a database called prod. When I try to delete the user I get the following error message:
"The database principal has granted or denied permissions to objects in the database and cannot be dropped."
Any help would be greatly appreciated. Thank you.
I can not delete user from a database in sql2005 beta 3.
View 22 Replies View RelatedOkay I figured out how to determine if stored procs and funcs exist before dropping them.
How do I do the same for ROLE, LOGIN, USER?
I want get rid of annoying messages in my scripts when trying to drop something that doesn't exist.
Server 2005 and Server Express 2005
Thanks
I am really new to SSRS (This is my first report) and I am looking to create a drop down that will allow a user to select the value to appear in a report. Meaning that is my values are:
a
b
c
d
If the user types a B it would "jump to that letter in the selection.
We are unable to drop a database user with the following error "Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped."
On checking the database permissions we have the following permissions assigned to the user
classclass_descmajor_idminor_idgrantee_principal_idgrantor_principal_idtypepermission_namestatestate_desc
17SERVICE655360517SN SENDGGRANT
17SERVICE655370517SN SENDGGRANT
17SERVICE655380517SN SENDGGRANT
17SERVICE655390517SN SENDGGRANT
17SERVICE655400517SN SENDGGRANT
[Code] ....
How we can revoke the SEND permissions?
I would like to write code to delete and add a SQL Login to every User database on my development server.
Whenever I restore databases to dev using production backups the SQl Server logins are invalid and I need to delete them from the user database and add them again.
I've already hard-coded a sql server job with n steps... a step for each user database to drop and add this sql user to each database. This isn't optimal since I have to add or delete a step everytime a user database is added or deleted.
Does anyone know how to write a loop or cursor that does this dynamically?
I am doing something syntactically wrong related to the GO statement.
declare @db varchar(100)
declare @message varchar(3000)
DECLARE user_cursor CURSOR FOR
SELECT top 1 name
FROM master.sys.databases
where name not in
('master','tempdb','model','msdb')
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'use '+@db + '
GO'
+'DROP USER [SQLLogin.DataEntry]
CREATE USER [SQLLogin.DataEntry] FOR LOGIN [SQLLogin.DataEntry]
EXEC sp_addrolemember N''db_datareader'', N''SQLLogin.DataEntry''
GO
'
execute @message
print @message
FETCH NEXT FROM user_cursor INTO @db
END
CLOSE user_cursor
DEALLOCATE user_cursor
Using Studio, I created a user defined database role but I can not delete it because
"TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for DatabaseRole 'test1'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
I am quite annoyed because the "owned schema" is db_owner, which can not be unselected. Quite an innovation. How do I drop this relationship?
How do I allow a user (or group of users) permission to create/drop a table?
I have found the 'GRANT CREATE TABLE TO username' command, which will (I assume) allow a user to create a table, but how to I allow a user to 'DROP' the created table as well?
'GRANT DROP TABLE TO username' doesn't work?
and I want the users to be able to DROP/DELETE this table (temporary table created just for printing purposes) as well.
thanks
What is the impact on the users to drop an index on a table while in use? I will recreate the index afterwards. The table is used constantly by a three of processes/users at all times.
View 3 Replies View RelatedI have a temptable with a list of user IDs that I want to drop so I created a script to do a cursor and run through my drop functions. The drops work by themselves and the ver check works with them but when I wrap them in the cursor all i get is an output for each user in the results window in ssms. why it's not setting the variable and instead outputting to results?
DECLARE @ver nvarchar(128);
DECLARE @UserName nvarchar(50);
DECLARE @UserD nvarchar(80);
DECLARE @LoginD nvarchar(80);
-- Initialize the variable.
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
[code]...
Hi. Thru a sproc, I drop & re-create some temp tables.When I call that sproc from the client, though, I cannot drop thetables.I need to allow the user, say "Alex", to drop/create tables (actually,that would be DDL). Which role should "Alex" assume ? How do I do that?I run the following sproc named, say, "CREATE_TABLE" (SNIP):__________________________________________________ __________________Set @StrSQL = 'if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[' + @TableName + ']'') and OBJECTPROPERTY(id,N''IsUserTable'') = 1) drop table [dbo].[' + @TableName + ']'Exec (@StrSQL)Set @StrSQL = 'CREATE TABLE [dbo].[' + @TableName + '] ([GROUP] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,...........[Stuff] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]'Exec (@StrSQL)Set @StrSQL = 'GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['+ @TableName + '] TO [Alex]'Exec (@StrSQL)__________________________________________________ __________________As you can see, it is dynamic, because I need to repeat it for many@TableName values - that means, further more, that I will be executingthis in the context of the current user, Alex, and therefore I have togive Alex rights to both executing the sproc and to the tables referredto by the sproc, as specified by @TableName.I created a _TEST sproc which contains only the following:_______________________________________________CREATE PROCEDURE _TEST ASDROP TABLE [dbo].[SomeTable]RETURN_______________________________________________When I execute it from the client, thru ADODB, on user Alex, I get"User does not have permission to execute this operation on tableSomeTable"The table has been created thru "CREATE_TABLE", abovePlease help, I have to finish this tomorrow, and I'm under tons ofpressure.Thanks a lot,Alex.
View 1 Replies View RelatedHi,
I have been working around with SQL Server Express 2005 on VISTA. I created a small C++2008 application which interface with SSE 2005. When running my application, it automatically created a user instance which I would like to get rid of. I just forgot from start to set "User Instance = False" in my connectionstring. (oups...)
I'm aware, from readings I did, that a couple of tables as well as log files, etc. are created under directory "utilisateurs...AppDataLocalMicrosoftMicrosoft SQL Server DataSQLEXPRESS". SQLEXPRESS is the instance name chosen during install of SSE2005.
Also using following SQL Statement:
select owning_principal_name, instance_pipe_name, heart_beat
from sys.dm_os_child_instances
I got following result:
owning_principal_name: PC-DE-STEPHANEStéphane
instance_pipe_name: \.pipe6172F4E8-622E-4A sqlquery
heart_beat: dead (at this moment...)
==> How can I make a good cleanup of all this as well as get rid of the user instance itself?
Thanks in advance for any help.
Stéphane
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
View 4 Replies View Related
i'm using the Enterpirse library logger to write logs into a database.
When choosing connection string i choose the database i want in the "connection properties" dialog box and
push 'Test connection' button.
everything goes well.
then i open the SQL Server Management studio express and connect to the databse to check some things,
from that point on , when i push the 'Test Connection' button in the Enterprise library i get the error:
"cannot open user default database. Login failed. login failed for user My'server/MyuserName'"
even when i close the sql server manager , it is still stuck - the connection test doesn't work anymore....
it only work when i restart the computer.
why ?
Cannot open user default database. Login failed.Login failed for user
'NT AUTHORITYNETWORK SERVICE'. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Cannot open user default database. Login
failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
View 3 Replies View Related Hi,
I have several reports for users to view on our Intranet. After installation of SQL 2005 SP2 patch, I cannot delete user or user's authority from Report in Properties Tab. An error message was shown on the status bar. It indicated that JavaScript Error: 'Return' statement outside of function. Seems something wrong with the 'Delete' funciton in SQL 2005 after update. The other functions worked fine. Could you point me out how to fix it or need to install any updates / hotfix. Thanks a lot!
Regards,
Kenneth Lai
Programmer
Error Pic
Message Box
Hi, I'm new to SQL Express 2005.
I found information regarding : "Cannot open user default database. Login failed."on this forum but I think that in my case it's a bit different issue.
I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITYLOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....".
Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively.
My connection string is:
"Data Source=.SQLEXPRESS;AttachDbFilename="|DataDirectory|spider-lab.mdf";Integrated Security=True;User Instance=True"
I would be grateful if you can help me
Hi,
I have a copy of VS2005 and i have installed the Microsoft SQL Server Developer Edition
I am trying to do my first connection using ASP.net (yay for me...) and it has failed...with this error..Login failed for user 'testuser'. The user is not associated with a trusted SQL Server connection.
can someone please point out why? yes i know that the error seems pretty straight forward but how do i fix it? like how do i make the user a trusted server connection? where do i do it? and what are the steps? the user is an administrator and by rights should have all access to everything....
connection string
SqlConnection MySQLConnection; MySQLConnection = new SqlConnection("server=cmptrname\databsename;uid=testuser;password=password;database=mytest");
btw while your reading do you have any examples of opening a db with the connection string in the web.config file? because i get some error "connection string has not been initialised" using the
system.Configuration.ConfigurationManager.AppSettings.Get
i though i would stick to the top code first before i tackled the web config error first....
Thanks heaps
I need help. The security principal is the correct DomainUser, but the error message says I am not authenticated.
So here is my error Message:
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Me.User.Identity.Name=CompanyDomainRyan; System.Security.Principal.WindowsIdentity.GetCurrent=CompanyDomainRyan; HttpContext.Current.User.Identity.Name=CompanyRyan
Generated by:
Dim secPrinc As System.Security.Principal.WindowsIdentity = System.Security.Principal.WindowsIdentity.GetCurrent
Me.lblError.Text = ex.Message & vbCrLf & vbCrLf & "Me.User.Identity.Name=" & Me.User.Identity.Name & "; System.Security.Principal.WindowsIdentity.GetCurrent=" & secPrinc.Name & "; HttpContext.Current.User.Identity.Name=" & HttpContext.Current.User.Identity.Name
Here is the Connection String:
Private ConnString As String = "Server=10.144.162.111;Database=DNS;Trusted_Connection=True"
IIS has 4 websites hosted on it. The parent level allows anonymous with windows auth off, but the website level has anonymous off with widnows auth and digest.
In IIS, is there a problem with having the parent level anonymous and website level windows auth?
All the research I found dealing with this problem is that the solution is to set SQLserver to mix mode. I have SQL server already set to mix mode. I am not sure what else to do. Has anybody run into this problem?
my connection string: <connectionStrings>
<add name="ConnectionString" connectionString="Data Source= server name ;
Initial Catalog=FILESHAREDB.MDF;
Integrated Security=false;"providerName="System.Data.SqlClient"/>
</connectionStrings>
Server Error in '/SendItNow' Application.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.Source Error:
Line 41:
Line 42: 'open the connection and execute the stored procedure
Line 43: mConnection.Open()
Line 44: mResourceID = mCommand.ExecuteScalar()
Line 45: mConnection.Close()Source File: H:MIS DepartmentIntranetSendItNowApp_CodeDalResourceDB.vb Line: 43 Stack Trace:
[SqlException (0x80131904): Login failed for user ''. The user is not associated with a trusted SQL Server connection.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +172
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +381
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +357
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +494
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
ResourceDB.SaveResource(String filename, String fromContactEmail, String toContactEmail, String message, Int32 ID) in H:MIS DepartmentIntranetSendItNowApp_CodeDalResourceDB.vb:43
Resource.SaveResource(String filename, String fromContactEmail, String toContactEmail, String message) in H:MIS DepartmentIntranetSendItNowApp_CodeBllResource.vb:31
ContentFiles_Default.ImageButton1_Click1(Object sender, ImageClickEventArgs e) in H:MIS DepartmentIntranetSendItNowContentFilesDefault.aspx.vb:33
System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +86
System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115
System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Hi all,
I am new in asp.net. In my web.config file, I wrote the connection strings as below and then I got the above error. I already changed SQL server Authentication mode to "SQL server and Windows". Any idea? Thanks in advance.
<connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=xxx;Initial Catalog=mydb;User ID=test;Password=test;Persist Security Info=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Our server has integrated security setup, upon startup of the server,
we have a continuous flow of error msg:
Login Failed: Reason: User '_' not defined as a valid user of trusted
connection.
The timing of these messages only around 30 seconds apart
The only incident from Technet I can find is Q186314, but we don't have replication setup, anyone knows where I can look into ?
I recycle the server but didn't help.
I appreciate any help I can get ..
Thanks.
I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
View 2 Replies View RelatedI have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.
I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?