SQL 2012 :: Unable To Drop Database User

May 7, 2014

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?

View 3 Replies


ADVERTISEMENT

Unable To Drop User

Nov 3, 2004

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?

View 2 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

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 Related

SQL 2012 :: User Impact To Drop A Noncluster Index On Table While In Use?

May 13, 2014

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 Related

SQL Server 2012 :: Cursor Function And Drop User / Logon

Aug 28, 2015

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)

[code]...

View 7 Replies View Related

Can Not Drop User From Database

Oct 12, 2005

 I can not delete user from a database in sql2005 beta 3.

View 22 Replies View Related

Drop And Add A Sql Server Login Account To Each User Database

Dec 6, 2007



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





View 3 Replies View Related

How To Drop A User Defined Database Role In 2005?

Mar 9, 2007

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?

View 3 Replies View Related

SQL 2012 :: Grant DROP Table Permission Within Database

Mar 26, 2015

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?

View 3 Replies View Related

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

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.

View 2 Replies View Related

Unable To Connect To Database For A Particular User

Mar 13, 2008



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?

View 1 Replies View Related

Unable To Send Database Mail To External User

Jul 13, 2015

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.

View 2 Replies View Related

SQL 2012 :: Unable To Add Service Reference In A CLR Database Project?

Apr 17, 2014

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.

[URL]

View 0 Replies View Related

SQL 2012 :: Unable To Export Tables From GIS Application To The Database

Oct 1, 2014

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?

View 7 Replies View Related

Unable To Open User Instance Type Database After Attaching And Detaching In SQL Mgmt Studio

Apr 15, 2007

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'.



My connection string is this:

<add name="SiteSqlServer" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;" providerName="System.Data.SqlClient" />



Any suggestions about how I can correct this?

View 3 Replies View Related

Unable To Drop Index (6.5)

Mar 8, 1999

Hi,

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.

Thank you.

Regards,
Joo Pheng

View 1 Replies View Related

Unable To Drop An Index

Jan 13, 2006

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?

View 3 Replies View Related

Unable To Drop Trigger - No Permission

Jul 9, 2009

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.

How to remove this trigger?

View 15 Replies View Related

UNABLE To ADD Or DROP A Constraint - SQL Complains!

Apr 21, 2008

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

View 4 Replies View Related

SQL 2012 :: User Database Backups

Jun 2, 2014

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 ?

View 6 Replies View Related

Mirroring :: Unable To Drop Endpoint With Name Mirror Server

Jul 16, 2015

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.

View 2 Replies View Related

Unable To Drop Login Due To Existing Event Notification

Aug 20, 2007

Hi

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?

Thanks
Martin

View 4 Replies View Related

Transact SQL :: Compiler Error Of Unable To Drop Table

May 29, 2015

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.

CREATE procedure [dbo].[TEST]
(
@id varchar(100),
@startDate datetime,
@endDate datetime

[code]...

View 10 Replies View Related

SQL 2012 :: Efficient Way To Monitor User Access To Database

Apr 22, 2014

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?

View 5 Replies View Related

SQL 2012 :: Database Schema Creation Date And User Who Created It

Nov 12, 2014

Query to find the date/time when a database schema was created and who created it.

View 2 Replies View Related

Cannot Drop User. Can Anyone Help?

Sep 20, 2007



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.

View 6 Replies View Related

Drop User With Msg 15138?

Apr 19, 2007

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.

any idea?
thanks
-D

View 5 Replies View Related

Drop User In Mssql

Aug 31, 2006

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,

View 1 Replies View Related

SQL 2005 Cannot Drop User

Oct 11, 2006

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.

View 17 Replies View Related

Drop Role, User, Login

Jul 14, 2006

Okay 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



View 3 Replies View Related

SSRS Drop Down - Allow User To Select Value To Appear In A Report

Jul 30, 2013

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.

View 1 Replies View Related

SQL 2012 :: Drop Table With FKs

Jun 26, 2015

When I drop a table should I also drop it's FK ? or they keys are removed with the table?

View 2 Replies View Related

Granting A User Permissions To Create And Drop A Table

Nov 16, 2006

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

View 3 Replies View Related







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