SSDT Database Users Being Dropped If Not In Source With DropObjectsNotInSource

Sep 18, 2012

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?

View 14 Replies


ADVERTISEMENT

How To Take Backup Or Restore A Database Using SSDT

May 2, 2013

I was wondering if there is a way to take backup or restore the database using SSDT.

View 6 Replies View Related

SQL 2012 :: Suppress SSDT Database Reference Error

Aug 21, 2015

In database projects, VS2015, I want to create some views for me database that will reference another database table using 3 part naming reference.

Works fine in SSMS but when I try and build me project I is throwing up a reference error.

I can't import the other database into this project, so is there a way to suppress the error? I don't really want to exclude these view from the project.

View 5 Replies View Related

SQL Server 2005 Express: The Database Principal Owns A Schema In The Database, And Can Not Be Dropped.

Jan 11, 2006

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?

View 4 Replies View Related

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

drop role [ReadOnlyRole]
go

View 12 Replies View Related

Database User Cannot Be Dropped

Mar 2, 2005

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?

View 2 Replies View Related

Recovering Dropped SQL Database

Sep 21, 2006

I am in a big trouble ,

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



thanks in advance

View 1 Replies View Related

SQL 2012 :: SSDT Database Project - Cross DB Trigger Enable / Disable Gives Warning 71502

Jul 13, 2015

I have 2 dbs (SQL 2012) - one contains a trigger that is enabled/disabled by a procedure in the other database. This all works fine.

If I create a Database Project solution in Visual Studio 2012 SSDT (or 2013) for both databases, the stored procedure generates a SQL71502 stating that my trigger name can't be resolved.

To recreate the issue:

CREATE DATABASE DbWithTrigger
GO
USE DbWithTrigger
GO
CREATE TABLE dbo.TblWithTrigger(
Id int NULL,
SomeValue varchar(30) NULL

[code]....

-- Test to confirm

EXEC CrossDbTriggerCall
INSERT DbWithTrigger.dbo.TblWithTrigger VALUES(1, 'Blah blah')

In Visual Studio 2012:

1. Create a new solution with a project named DbWithTrigger
2. In project settings set the Target platform to SQL 2012
2. Import the DbWithTrigger db into this project
3. Create a new project named DbCallsTrigger
4. In project settings set the Target platform to SQL 2012
5. Import the DbCallsTrigger db into this project
6. Add a Database Reference in DbCallsTrigger for DbWithTrigger

When you build the solution both dbs build successfully, however there are two warnings. One is easily resolved by replacing DbWithTrigger in the body of the procedure with [$(DbWithTrigger)] (db variable name for the reference) but I can't find out how to get rid of the other. Is it a bug?

View 1 Replies View Related

Database Connection Timeout / Dropped

Jan 25, 2013

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.

View 8 Replies View Related

Why Out Of The Blue Would VPN Users Be Unable To Connect To Database And Local Users Are Unaffected?

Mar 6, 2008

I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!

View 6 Replies View Related

Royal F-up, Dropped User Default Database

Jan 27, 2004

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.

View 14 Replies View Related

Difference Between Database -- &&> Users And Security --&&> Users

Nov 28, 2006

Hi Team,

In SQL Enterprise Manager, when we expand "Database -->Users", we see the

users there. When we expand "Security --> logins" we see the same users there.

Can you differentiate these two.

Thanks

Santhosh

View 1 Replies View Related

Cannot Open Objects Using SSDT

Jun 4, 2015

I have SQL server 2014  database installed on a Windows 2012 server and SSDT also got installed ( 2013 shell)

I can connect to a database and see all objects using SSDT , but I cannot write any queries or modify any of the objects,

I am new to the SSDT, and this is the first time I am going to configure SSDT to work with SQL Server.

View 7 Replies View Related

Transient Deadlock During SSDT Publish

Jul 10, 2015

I have a SSDT project that intermittently fails with the following error when I try to publish via Visual Studio by double-clicking the publish.xml file...

(330,1): SQL72014: .Net SqlClient Data Provider: Msg 1205, Level 13, State 68, Line 5

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

(326,0): SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF 
            WITH ROLLBACK IMMEDIATE;
    END

This is a hyperV client running on my laptop. I'm connecting to the client's VPN inside the hyperV client and using the "runas" method to open Visual Studio to spoof the domain credentials i use on the clients network. This allows me to connect to servers on the client's domain via apps as if I was logged in with appropriate credentials (not sure how to phrase that better). I can elaborate if deemed necessary, but even the client's FTEs are having same problem and they're sitting in the building.

Windows Server 2012 R2 StandardSQL Server 2014 CU6 (12.0.2480)Visual Studio Premium 2013 (12.0.31101.00) Update 4 

Windows Server 2012 R2 (VMWare)SQL Server 2012 SP2 (11.0.5058).Here are some observations...

Happens about 60% of the time during the day (I just keep re-running until it deploys successfully)...late at night and on weekends, it doesn't seem to happen with anywhere near as high a frequency.Only seems to happen when I use "Always re-create database" option (don't recall incrementals having this problem...but admittedly I'm not doing many of those as we're still in the full-speed-ahead-development phase)This does NOT happen when I publish to my local SQL Server instance..Deadlock xEvent trace on target instance. Unfortunately it didn't seem to be able to capture all the info and the deadlock graph doesn't render (error below) checking the Single User Mode during the publish. This seems to increase the frequency with which this issue occurs.

View 10 Replies View Related

Restore Of Database Backup To Another Database Makes Users DBO

May 7, 1999

Anybody figure a way around this:

In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.

When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.

We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.

This is bogus and did not happen in 6.5 because of the aliases.

Anybody come up with a better way?

View 1 Replies View Related

VS2013 SSDT Crash On Schema Compare?

Aug 25, 2014

We are getting regular SSDT/VS crashes when doing schema compares (source: project, target: server). Sometimes the compare succeeds but around 80% of the time we get a crash.

We are using VS2013 Update 3 and the latest version of SSDT.

Application : devenv.exe
Version du Framework : v4.0.30319
Description : le processus a été arrêté en raison d'une exception non gérée.
Informations sur l'exception : System.Runtime.InteropServices.COMException
Pile :
   à System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32, IntPtr)

[code]....

View 5 Replies View Related

Can Install SSDT Without Any Visual Studio Installed

May 28, 2015

Just wanted to find if there is a way i can install SSDT without a Visual studio Install in the machine?I intend to use sqlpackages.exe which is from SSDT for publishing a dacpac, and do not intend to install any Visual Studio for this purpose.

View 3 Replies View Related

SSDT Tries To Alter Timestamp Column In TFS Build

Apr 21, 2015

We're trying to perform an upgrade test against a copy (backup/restore) of our customer database as target. There are some tables with  timestamp column in the database. The way we do this is by having a database project with a publish profile targeting that copy of customer database and then with TFS build server is used to build the database but only to generate a publish script (/p:UpdateDatabase=False) set in the build definition - msbuild argument.

Example of table definition:

CREATE TABLE dbo.CodeTable1
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,Code CHAR(6)
,[Timestamp] TIMESTAMP NULL);

We would like to have the "Code" column to have CHAR(7), so in the project we modify the table definition:

CREATE TABLE dbo.CodeTable1
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,Code CHAR(7)
,[Timestamp] TIMESTAMP NULL);

Expecting SSDT build will generate alter script:

ALTER TABLE dbo.CodeTable1 ALTER COLUMN Code CHAR(7);

To our surprise the generated script was:

ALTER TABLE dbo.CodeTable1 ALTER COLUMN Code CHAR(7);
ALTER TABLE dbo.CodeTable1 ALTER COLUMN [Timestamp] TIMESTAMP NULL;

Which will cause error when the script is executed: "Cannot alter column 'TIMESTAMP' to be data type timestamp."

Why is SSDT generating the change script for that timestamp column??

We then try a local build in VS, the issue is not happening, SSDT correctly generates alter script only for the "Code" column to CHAR(7);

Both local machine and TFS Build server are having VS 2013 Update 4- SSDT 12.0.50318.0 installed.

As we tried to troubleshoot further, we found out that it seems it only happens on a restored database from a backup copy of our customer database. It doesn't happen for databases created by SSDT build from scratch or that we manually created. We've tried make sure all database properties are the same as the database that correctly built.

But still if the target database is the one we restored from a customer's copy, SSDT always tries to alter timestamp column (on server build).

View 5 Replies View Related

SQL 2012 :: Downloading SSDT Without Live Internet Connection?

Apr 30, 2012

I am working in a secured company which doesn't allow live internet connection, therefore I can't install SSDT using Web Platform Installer and we also don't want to install the intire 2012 client tools.

View 2 Replies View Related

SQL 2012 :: SSDT Installation On Multi-server Platforms

Jun 25, 2014

We have a BI-Stack configuration for server SQL 2012 that includes a gateway server that houses the app, a second server which houses SQL Server DB and SSIS, and a third server that houses SSAS and SSRS. Where should SSDT tools be installed? I assume on the gateway? This is a production environment, so should VS and/or SSDT not be here at all?

View 1 Replies View Related

SQL 2012 :: Rename A File In SSIS (SSDT) Using Expression

Jul 23, 2015

I have a file named abc-72015.txt.

I have a package that renames the file from abc.txt to abc-72015.txt

I want to do the reverse. I wan to rename it from abc-72015.txt to abc.txt.

I have a variable named myFileValue with a value of abc.txt.

Can I do this by setting the variable as an expressions and use a function to rename it as abc.txt?

What would the syntax be?

View 4 Replies View Related

Integration Services :: Oracle Connectors (3.0) With SSDT (2013)?

Nov 14, 2014

In previous versions of BIDS we were able to customize the DataFlow toolbox to incorporate the Oracle Source and Oracle Destination items - however, it appears this is no longer the case in SSDT (no option to customize toolbox).  I've installed the 3.0 Microsoft Connectors for Oracle - when I create a new connection in the Connection Manager I can see the Attunity drivers, but under Other Source/Other Destinations in the Toolbox I cannot find the Oracle Source/Destination components.

View 5 Replies View Related

After Replicating A Database, SP's In The Same Database Are Not Able To Modify By Other Users.

May 21, 2008

hi there,

After replicating a database, the stored procedures in that same database are not able to edit by other users than service account (sys admin users), When the normal users try to edit any of the stored procedures, sql server 2005 is throwing the following error...

Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.

any suggessions are most welcome.

by
CHINNASAMY

View 1 Replies View Related

SSDT In Visual Studio 2012 - Selected Identifier Is Not Highlighted

Jan 22, 2015

In VisualStudio 2012 in C# projects when you double-click on a identifier (variable name, class name, etc) then all occurrences of that sting in the current file are highlighted.

It doesn't work in SQLServer projects in sql files. It is a shame that although we are in VisualStudio we are still limited in functionality.

Is there a way to enable this feature?

View 4 Replies View Related

SQL Server 2014 :: Can't Find Data Flow Task In SSDT 2012

Nov 5, 2015

I Can't find the Data Flow Task in SSDT 2012.

Has it been replaced by another task or do I need to do something to make it visible?

Edit: It popped up under my favorite tasks.

[URL]

View 2 Replies View Related

Users Database

Jan 31, 2008

how can i create a users database to store users infos (names pictures...) ??
thx for taking time to read this

View 2 Replies View Related

Accessing Database Users

Feb 8, 2007

Hi,
 I would like to know if there is any way of Accessing database users from frontend and changing their persmissions.
Thanks
Niranjana. 
 

View 10 Replies View Related

Users Not Listing In Database.

Jan 25, 2001

Hello Experts,

I copied a database from my production server to the development server and now i dont see the users in the database from the enterprise manager,database and users folder. But, When i run a query to against the sysuser table from a query analyser I can see those users here. Why cant I see in from the Enterprise manager. Any advice please..


Appreciated any comments on this.
Thanks,

Vilke.

View 2 Replies View Related

How To Force Users From A Database?

Dec 1, 2000

Does anyone know a way to force out all users from a particular database? I need to script this to perform some maintenance at a particular time every day.

Thanks for your help,

Les

View 2 Replies View Related

Dropping Users In Database

Dec 23, 1999

Is there a way in SQL to drop any users from a database. I am trying to schedule some nightly DB maintenance and some users are still in there sometimes. I need to be able to kick the out to do dbcc checkdbs, etc. Is there a way for me to do this without stopping the SQL services?

View 3 Replies View Related

Database Users...URGENT!!

Jun 15, 2000

I restored a database from one full backup.The users are missing now in the new databse.When I try to create the same users again it says that they are already existing!Please help what shall I do to restore/recreate the users?Do I need to do anything with the Master?

View 4 Replies View Related

Copying Users From One Database To Another

Feb 25, 2001

I have a question regarding copying users from one server to another that are running different versions of Microsoft Sequel Server.

I have Server A running 6.5 version and Server B running 7.0 version. How can I copy just the list of users from Database1 on Server A to Database2 on Server B.

Can anyone help me.

Thanks.

Lakshmi.

View 2 Replies View Related

Users Cant Access New Database

Dec 22, 2004

EMERGENCY 911 HELP PLEASE

Ok I created a database SQL is the engine and Access XP is the gui. When the users try to access the databae they get error message, it opens up but they cant open the forms for some reason. I was wondering if this had to do with me have Access XP and them having Access 2000, but I highly doubt it because when I had a user log on to my machine (which has access xp) they still were not able to access the forms. Can someone help me out PLEASE PLEASE PLEASE... this is so frustrating :(

One thing I'm noticing is that the users connection keeps dropping???

View 12 Replies View Related







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