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?
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
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.
I 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)
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]
How to grant DROP table permission within a database to an SQL login. I could see in Databae properties-> permission tab, there is an option to grant CREATE TABLE, EXECUTE permission etc, but not DROP table permission. How to grant it?
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
Hi, iam using VS2008 for developing an application. From my code iam mentioning a connectionString in which iam using a database named ASAOPS.When iam trying to connect to it with a user named asauser it is throwing an exceptions that "asauser is not with the trusted sql connection" for one time and "login failed for the asauser" for later time. can anyone help me in solving this problem?
I have two domains. forest domain abc which has the exchange infrastructure. forest domain xyz which does not have exchange. There is a two way trust set up.
I have set up database mail which sends internally but not externally on the xyz domain its uses mailrelay address to comunicate with exchange. The receive connector is set up correctly on exchange on domain abc as I have managed to send email from a sql server to external contact in the abc domain.
The IP addresses are correct on the receive connector.
The problem is I am unable to send to an external contact from the sql server in the xyz domain. Not sure if this an exchange issue or database mail.
I created a CLR procedure with a service reference in Visual Studio 2010 that is being used in SQL Server 2008 R2. We recently tried to deploy it to a 2012 SQL Server, but were unable to due to compatibility issues. When I tried to upgrade the package to Visual Studio 2013 I got this error message:
Unable to cast object of type 'Microsoft.VisualStudio.Data.Tools.Package.Project.DatabaseFileNode' to type 'Microsoft.VisualStudio.Data.Tools.Package.Project.Internal.FolderNode'
I tried to create this using the 2012 Data Tools, but was unable to add a service reference with the GUI. It appears Microsoft has removed this functionality. My company has a third party app that we have limited control over, which is why we need this functionality.
I am using SQL server 2012. An user tried to export tables from GIS application to the SQL database.
After export, and login to the SQL server, we see all the tables has his name as the schema but not dbo.
He was added as a login and user as in a windows group. Meaning he is a member of the windows group.
I assume when export, the default schema should be dbo. but apparently not. I went to the setting and explicitly make the default schema for the windows group user to dbo. But he tried again, it still use his username as schema prefix to the tables. just wondering why is this?
I've been using my DotNetNuke database for weeks on my local PC with SQL Server Express. It attaches as a "User Instance". Basically, I am using all the DotNetNuke defaults. Today I decided to attach the DotNetNuke database in SQL Server Management Studio Express and peek at the tables. I attached the database; then I detached it when I was done. This all went well. But when I tried to open my DotNetNuke web site again I now get a message that it cannot connect. Attaching and detaching the database in SQL Server Management Studio obviously changed something about the database, but I don't know what. My connection string to the database has not changed.
The specific error displayed in the browser is: ERROR: Could not connect to database specified in connectionString for SqlDataProvider
In event viewer is this message:
Exception information:
Exception type: SqlException
Exception message: Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
I have a table which I am unable to drop the index. I tried to drop it via the TSQL and GUI interface (Indexes dialog box), but when I clicked on table refresh, the index still there.
Below is the result after DBCC commands:
1. DBCC checktable(TB_LOCLoan)
Checking TB_LOCLoan The total number of data pages in this table is 146542. Table has 1596232 data rows. Msg 605, Level 21, State 1 Attempt to fetch logical page 462136 in database 'DM_LOCLoan' belongs to object '1744009244', not to object 'TB_LOCLoan'.
2. DBCC newalloc(DM_LOCLoan)
TABLE: TB_LOCLoan OBJID = 832005995 INDID=0 FIRST=60168 ROOT=642778 DPAGES=146542 SORT=0 Data level: 0. 146542 Data Pages in 18393 extents. INDID=5 FIRST=463752 ROOT=463739 DPAGES=5304 SORT=1 Msg 2525, Level 16, State 1 Table Corrupt: Object id wrong; tables: alloc page 463616 extent id=463752 l page#=463752 objid in ext=-832005995 (name = -832005995) objid in page=832005995 (name = TB_LOCLoan) objid in sysindexes=832005995 (name = TB_LOCLoan) TOTAL # of extents = 18393
3. From Error Msg 2525, I have tried the suggested action
use DM_LOCLoan go select indid, name, object_name(id) from sysindexes where id=832005995 and distribution=463752 go
This query does not return any row.
I am running out of clue of how to proceed! Anybody come across this problem before? I am very eager to solve this problem as the table is hanging here, I can't even drop it to recreate and continue data loading.
According to sysindexes, I have a table with a primary key and an index. I'd like to drop the index. However, when I give the drop command, the message "Cannot drop the index 'eventlog._INDEX', because it does not exist in the system catalog." is returned.
I'm not sure what to look for. How do I remove the index?
I created a trigger that will throw a message whenever a new record is inserted in the table. Now I want to remove this trigger. I am not able to remove.
CREATE TRIGGER prod_culture_trig ON Production.Culture AFTER INSERT AS SELECT 'New culture entry added';
I get the following error message:
Msg 2714, Level 16, State 2, Procedure prod_oulture_trig, Line 4 There is already an object named 'prod_oulture_trig' in the database.
This error confirms that we have the trigger already existing. Now I run the code
DROP TRIGGER prod_oulture_trig;
I get the following error message - Msg 3701, Level 11, State 5, Line 1 Cannot drop the trigger 'prod_culture_trig', because it does not exist or you do not have permission.
What permission do I require? This is a test database on my computer with me as the administrator.
Hi all, I am trying to create a CONSTRAINT but for some reason T-SQL does not allow me to do that.
When I try to DROP a CONSTRAINT it says:
Msg 3728, Level 16, State 1, Line 13'DF_TBL_SyncTable_DEVUK' is not a constraint.Msg 3727, Level 16, State 0, Line 13Could not drop constraint. See previous errors.
When I try to ADD a CONSTRAINT it says: Msg 1781, Level 16, State 1, Line 14Column already has a DEFAULT bound to it.Msg 1750, Level 16, State 0, Line 14Could not create constraint. See previous errors.
For some reason I can't win here. Can't drop it nor can I create one. Any solution?
Code Snippet ALTER TABLE TBL_SyncTable DROP CONSTRAINT DF_TBL_SyncTable_DEVUK ALTER TABLE TBL_SyncTable ADD CONSTRAINT GOD_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK
Thanks for comments + suggestions.
*UPDATE*
I am trying to use the following code to check if the SCHEMA exists but still no luck. For some reason when I create it and wrap an IF statement around it, it doesn't detect the SCHEMA. Is something wrong with my code?
Code Snippet
IF EXISTS(
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'dbo'
AND CONSTRAINT_NAME = 'DF_TBL_SyncTable_DEVUK'
AND TABLE_NAME = 'TBL_SyncTable'
) SELECT * FROM TABLE_1 ELSE ALTER TABLE TBL_SyncTable ADD CONSTRAINT DF_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK
I have inherited a new SQL Server 2008 database server and can not figure out how my user databases are being backed up. This database server is running under a VM.
All the user databases are being backed up nightly per the SQL server log. The backups are written to a virtual disk and is kicked off by the NT AUTHORITYSYSTEM user. I can not see the virtual disk. A restore task does not provide any information about the last backup. I have created a new database, and it is automatically included in the next set of backups.
I have looked at the windows event viewer with out any luck. There are no SQL Server Maintenance Plans or Agent jobs that call a backup. I have also checked the Windows Task Scheduler and can not find any task that does a backup.Could the backups be called from another server ?
I have created a Endpoint with "Mirroring Server" on the mirroring instance. Due to the keyword server i am not able to drop the endpoint now.
Also am not able to configure a mirror in a windows workgroup also( the two systems principal and mirror are under same network ). I have followed many ways as per msdn articles and other blogs.
I am not able to connect to the mirror server.
I have tried giving the ip addr and the port no, also the computer name:port no, computername.local:port no etc. Both the systems are running on sql server 2008 only
I always get the error as Connection cannot be established to the destination.
(I have also enabled the ports in firewall by creating inbound and outbound rules)
The issue probably lies in NAMING server address i guess.
I am unable to drop the above login. The error is Error:15141 The server principal owns an event notification and cannot be dropped.
I have looked in the table sys.server_event_notifications and there are rows returned that have a server_principal_id of the user I am trying to drop. No events have been created on this server, so I am assuming these notifications are either created by default or are somehow related to Database Mail?
All the event notifications belong to service name "SQL/Notifications/ProcessWMIEventProviderNotification/v1.0" and begin with SQLWEP (i.e. SQLWEP_RECHECK_SUBSCRIPTIONS, SQLWEP_B415ADB8_A604_4057_976F_600002FA5AF6). How can I find out what these are for and how they were created?
What purpose do these event notifications have? Is there some syntax to change the owner of these event notifications so I can successfully drop the login? If the only way is to directly update the system view, is this safe and what repercussions could this have?
I have a procedure where an id is passed into the procedure. It will either be a numeric value or it will be the word "ALL." All that is taking place is a select statement for the specified user id. However, if All is passed in then I 1st need to get a list of all possible user id's and produce the results for all of the associated user ids. This is my syntax, but it keeps producing a compile error of:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#tbl_temp', because it does not exist or you do not have permission.And I placed a comment above the line that is throwing this error.
Currently there are various teams accessing the database. For costing reasons, we need to track usage.Is there an efficient way to monitor User access to the database.Can we track which user has executed which query(SELECT,insert etc),the login time and such parammeters?
Hi: 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.
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.
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.
