SQL 2012 :: Cannot Drop Orphaned Account

Dec 30, 2014

All is happened when a server crashed some weeks ago and it was removed from my network. After that, under my SQL Server 2012 I get an orphaned account which cannot be removed. This account is a computer account related to an old SCOM installation.

If I try to execute the command DROP USER [NETWORKSERVERNAME$] I get the following error message:

The database principal has granted or denied permissions to objects in the database and cannot be dropped. 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.But if I run the following command to know all permission granted to the account, I get an empty result:

select * from sys.database_permissions where grantee_principal_id = user_id ('NetworkSERVERNAME$');

Furthermore, following the instructions provided by the official KB for troubleshooting orphaned users, I get another error [URL].

sp_change_users_login 'update_one', 'NetworkSERVERNAME$', 'NetworkSERVERNAME$'
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'NetworkSERVERNAME$' is absent or invalid.

The only thing I can retrieve is 15 permissions that this account granted to another account in the past:

select *
from sys.database_permissions
where grantor_principal_id = user_id ('NetworkSERVERNAME$'
--
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
17 SERVICE 65538 0 5 19 SN SEND G GRANT
And more 14 rows…

resolve my issue and safe delete the account SERVERNAME$? I need this because I have to reinstall SCOM with the same computer name on another server, but installation fails due to this behavior.

View 8 Replies


ADVERTISEMENT

Orphaned Publication Information Preventing Drop Of Database

Jul 7, 1999

I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.

View 2 Replies View Related

SQL 2012 :: Restoring Orphaned MDF File

Sep 23, 2014

Been asked to restore an orphaned MDF file leftover after a botched uninstall - no .bak file. Tried to reattach, but got an error, I don't think it had been detached. My initial answer was, "No, very likely can't be done".

Am I right? Or is there a way of attaching it that doesn't require it to have been detached?

View 4 Replies View Related

SQL 2012 :: Transactional Replication - How To Remove Orphaned Articles

Jan 27, 2015

We are runnning on SQL Server 2012 SP1 + CU9.

I have found some articles with no publication in our transactional replication.

For example, running this:

select p.publication,
a.publication_id,
a.article
from dbo.MSArticles as a
left outer join dbo.MSpublications as p
on a.publication_id = p.publication_id

shows this:

NULL1org_Community
NULL3org_Community
Purchasing to EDW5org_Community
NULL1org_Division
NULL3org_Division
Purchasing to EDW5org_Division

How can I get rid of the articles that are not part of a publication?

I can't use sp_droparticle because it requires a publication which these articles do not have.

View 1 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

Whether To Use Local System Account Or Domain Account For Service Account

Jan 5, 2006

During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services.  I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.

View 6 Replies View Related

SQL Server 2012 :: How To Run A Job Using Different Account

Jul 21, 2014

I have a job that needs to execute with different account. I figured i need a proxy so i have created one.Now i need to configure a job that runs a store procedure using that proxy account.

View 3 Replies View Related

SQL 2012 :: Proxy Account Not Being Used

Jan 14, 2015

I have a frustrating problem where I am using the Ola Hallengren jobs to backup to a network share. (This isn't something specific to his scripts).

For various reasons the SQL Server account can not be granted access to the share so I thought I would use a proxy account which does have access (this has been fully tested). I am using a CmdExec proxy.

The problem comes now that when I run the job it still thinks access is denied when running the xp_create_subdir command.

When I recreated this problem locally on my machine, as soon as I add the SQL Server account access to the share the backups work, so why isn't the job using the proxy account?

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

SQL 2012 :: Service Account Change And Mirroring

Apr 22, 2015

As the title suggests we are looking to change the service account of a SQL mirror implementation. I will be using the same account on all 3 servers involved in the configuration.

I know each server requires the account of the other two adding but as this will be the same account I assume this doesn't apply?

Also for mirrored databases already set up would I need to reconfigure the security for each one?

Is there anything I am missing?

View 0 Replies View Related

SQL 2012 :: Not Able To Drop File Group

Dec 11, 2014

I am not able to to drop few file groups that has been created to add partition range.

Steps taken so far:

--Empty files started
DBCC ShrinkFile(YEAR2015_FG,EmptyFile);
GO
--Removing files

Alter DATABASE ETL_MART REMOVE FILEGROUP YEAR2015_FG;
GO
-- Remove parition scheme depednecy

[code]...

View 2 Replies View Related

SQL 2012 :: Domain Account Errors Out When Use As Service Accounts

Jul 23, 2014

Installed sql server 2012 enterprise. Runs with the built in account fine.

I tried entering a domain account to run as the service account from sql configuration it fails with the error "the specified network password is not correct".

I tried from services.msc and entered successfully but when I try to restart it fails that the log in credentials are wrong.

the domain account and password I entered are just fine. What's it I should do or missing?

View 3 Replies View Related

SQL 2012 :: What Account To Use For Identity When Creating Credential And To Use As Job Owner

Aug 12, 2014

We are running SQL Server 2012 on Windows 2008 Server. I created a credential with a proxy account. In creating the credential, it asked for an Indentity and Secret. I used my windows login and password. Now, I have tested the credential and proxy account by executing a Job which calls a SSIS Package. What is the 'best practice' to use when creating a credential? Should the credential be created with another windows login, created with the same abilities as my windows login, with a non-expiring password? Should that new windows login be used as the owner of my job with the Agent?

View 8 Replies View Related

SQL 2012 :: Changing SA Account Name Causes Maintenance Jobs To Fail

Sep 23, 2014

SQL 2012 Standard on Windows Server 2012 Standard

After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.

Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?

SQL 2012 Standard VPS Windows 2012 Server Standard

View 3 Replies View Related

SQL 2012 :: One Script To Drop All Temporary Tables

Jan 16, 2014

I used code below to drop one temporary table. How to make code to drop all temporary tables?

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END

View 9 Replies View Related

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 View Related

SQL 2012 :: Error - Cannot Drop Table Because It Is Being Used For Replication

Sep 2, 2014

The error message is "cannot drop the table because it is being used for replication"

my sql version is in sql server 2012 and Im ussing merge replication.

I want to drop only one table.

View 7 Replies View Related

SQL Server 2012 :: Drop And Update Column?

Feb 16, 2015

I am looking for standard sql code for below 2 concern.

1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx

2-I want to update all the table where table name start with ArchiveBbx

example:-

Update table Archivebbxfbcc
set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))

View 3 Replies View Related

SQL 2012 :: How To Drop Columns From Alter View

Mar 20, 2015

I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.

Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .

The view is called TEMP_EXPORT_1

I can either use the code below to return a list of columns that I want removed:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'

Or I can use the code below to return the list of columns that I want to keep:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'

Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...

Therefore I tried the following but I'm not sure of the syntax:

ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1

Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.

View 2 Replies View Related

SQL Server 2012 :: Derived Table To Extract Value Of Account For Comparison

Nov 2, 2014

In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)

This is my script:

SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)

[Code] ....

My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.

View 7 Replies View Related

SQL 2012 :: SSIS Package Fails With Same Service Account On Different Instances?

Feb 15, 2015

I have a package on the default instance which runs and completes successfully. When that package is moved to the same SQL server, but a different instance, running under the same service account, it fails. The error is below with some specific stuff removed:

Delete Data from Level 1:Error: Executing the query "-- Variable to capture FileID's
DECLARE @DeleteFil..." failed with the following error: "The DELETE permission was denied on the object '[name removed]', database '[]', schema '[]'.". Possible failure reasons:

Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This makes me think that the package under the non-default instance ends up running under a different security context.

View 8 Replies View Related

SQL 2012 :: Drop All The Auto Generated Column Statistics?

Dec 29, 2014

My question: Is it okay to drop all the auto generated column statistics? (for the following scenario)

- I am cleaning up unnecessary objects (tables, unused indexes, overlapping statistics etc) from databases and found out there are more than 1400 auto generated column statistics on one database (lets call it A).
- Database A was used to be our reporting database but from last several years we are using database B for reporting. DB A has all the historical data while DB B only has valid records.
- We are updating all the column statistics with full scan nightly on database A and it is talking almost 2.5 hours to do that. Now I want to drop all the "unnecessary" statistics those were created when DB A was reporting database and they are no longer in use. There is no way to know the creation date of the column statistics that I know of. Statistics "last update date" is of no use because of our nightly job. So I was thinking of dropping all the auto generated column statistics and let the sql server create as it needs from now.

View 0 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

Setup And Upgrade :: Unable To Register SPN Using Domain Service Account 2012

Jul 23, 2012

I am working with a client who is rolling out 50+ VM's based of a template we created.  This is SQL 2012 CU1 running on Windows Server 2008 R2.  Using the default service account the installer has it registers fine and we get the following in the SQL log.

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/server.domain.com:1433 ] for the SQL Server service.

When we change to a domain service account through SQL configuration manager we see the following and cannot connect remote using integrated authentication The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/server.domain.com:1433 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

My understanding is you should and could change service accounts using the SQL Server Configuration Manager and it would set all permissions.  Is there something we need to do in addition to get this up and working?

Convert DTS to SSIS |
Document SSIS |
30+ SSIS Tasks |
Real-time SSIS Monitoring |
Quick Starts |
BI Blitz

View 9 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 :: How To Drop And Create Synonym Without Client Error

Apr 23, 2015

A heavily-selected database will be in an inconsistent state for several hours during a batch process. For that time, a database snapshot is created and accessed instead. To allow constant client read access to the database, a database that only contains synonyms exists. Those synonyms point to the main database except during the batch process, at which time they point to the database snapshot.

To switch the synonyms, each synonym is dropped and then created pointing to the database snapshot (after its creation, of course). The drop/create occurs inside a transaction. Roughly, the SQL looks like this:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
DROP SYNONYM [dbo].[some_proc];
CREATE SYNONYM [dbo].[some_proc] FOR [snapshot_db].[dbo].[some_proc];
GRANT EXECUTE, SELECT ON [dbo].[some_proc] TO public;
COMMIT TRANSACTION;

When the batch update is completed, the process is reversed with "snapshot_db" replaced with "regular_db". The SQL snippet above is dynamic SQL. What I've pasted is the dynamic SQL that is executed as a single batch.

While this switch is happening, clients are accessing the procedures through the synonyms, potentially at a high request rate. Testing reveals that clients can get the error:

Error=-2147217900, Id=0, Meaning=IDispatch error #3092,
Source=Microsoft OLE DB Provider for ODBC Drivers,
Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.some_proc'.

This error only occurs once. If the same SPID retries its request and the transaction has not completed (for testing, a delay was added), then it blocks until the transaction completes.

Any way to prevent it besides a client-side retry?

View 2 Replies View Related

SQL 2012 :: Error Occurred While Attempting To Drop Allocation Unit

May 4, 2015

Since a couple days, we are getting this message the errorlog of one of our SQL2012 server

LogEntry: Error [36, 17, 145] occurred while attempting to drop allocation unit ID 451879652360192 belonging to worktable with partition ID 451879652360192.
(version Microsoft SQL Server 2012 - 11.0.5058.0 (X64))

I am wondering what is the best way trying to troubleshoot this issues? I do not know from which of out database this is coming.

View 6 Replies View Related

SQL 2012 :: Evaluated Drop Table And Ignore Current Context

Jul 20, 2015

I have 2 stored procedure, insert and update.

They both drop and use a static table, this will be changed for production to use a temp table. there is a slight difference in how the create the table and when it comes to executing them if the other has been run first it errors because of the column definition or names.

Now as I said I will change these to use a temp table rather than a static staging table for production but as one of the first parts of my proc uses the IF OBJECT_ID ('dbo.tab', 'U') IS NOT NULL DROP TABLE dbo.Tab;

Then I would expect the table difference to not matter as the table is being dropped...

View 2 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

Reporting Services :: Can't See Drop Down List For A Report On SSRS 2012

Oct 20, 2015

Here's my setup:

1) SSRS in native mode
2) 2012
3) I have "Browser, Content Manager, My Reports, Publisher, Report Builder" permissions on the folder that has the report

I can't see the drop down list associated with the report that's hosted inside the folder. Here's what I mean by drop down list -- the one that lists things like: properties, subscribe, etc. In other words If i click on that dropdown arrow, I don't see the list.

View 2 Replies View Related

SQL 2012 :: AlwaysOn Availability Group Replica Ends Up With User Account As Owner

Sep 5, 2014

Setting up a test AlwaysOn Availability Group for one database.

However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.

Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.

I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.

View 2 Replies View Related

SQL 2012 :: Instance Name Not Appearing In Drop Down List On Cluster Node Configuration Window

May 29, 2013

I am trying to install the MS SQL Server 2012 Failover Cluster on Windows Server 2012 . I successfully Installed the Failover cluster instance on my primary node.

But when I am running the installation process on passive node to add node in the failover cluster I am stuck with very unique kind of issue.

I am following the Standard process of Installation and I am getting the same windows for each next process, but after License Agreement window when I get the Cluster Node Configuration window, then in "SQL Server Instance Name" drop down box I am not getting the Name of Instance which is already installed on the primary node. But this Instance complete information is appearing in below given box.

Only issue is Instance name is not appearing in the drop down list, that's why I am not able to select and when I click on next it trough error and do not proceed.

View 3 Replies View Related

SA Account (DBA System Account) Granting Priveleges But SQL Server 2000 Not Applying Them

Dec 4, 2006

I have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?

yours sincerely

Craig Hoy

View 9 Replies View Related







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