SQL 2012 :: Change Minimum Permissions To Allow Read Access To Change Tracking Functions
May 12, 2015Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies
ADVERTISEMENT
SQL 2012 :: Change Tracking Anchor ID
Dec 30, 2014How to reset the value of the change tracking anchor id?
CHANGE_TRACKING_CURRENT_VERSION()
It's a bigint that increments on every DML operation and I just wondered how you could reset it back to zero. Turning change tracking off and on doesn't seem to do it.
SQL 2012 :: Change Read-only And Read-write
Aug 15, 2014Can a user of db owner role of a database change the databse option to read only and read-write?If not what permission I need to grant to the user?
View 1 Replies View RelatedChange Tracking For SQL Server
Jul 20, 2005Has anyone come across a change tracking tool for SQL Server. Specificallythe scenario I want is the following :A Production DB needs some modifications to its content.This tool will copy the DB to a dev environment.The Dev environment will be 2 copies of the DB, 1 as a control set and theother the change DB.The developer makes their changes in the Change DB. They test them out andthen when they decide the changes are ready to pushto production they hitthe "go" button on the tool.The tool calculates the delta between the Control and the Change DB and thenpushes the changes to producitonAny ideasThankss
View 2 Replies View RelatedTracking Change History, By Column...
Jun 17, 2004This one is giving me quite a bit more difficulty then I ever imagined it would...
Essentially I would like to use one table to store the change history for multiple tables. I would like to use an update trigger to check which fields have changed in each record, and write a single record for each field that changed containing the table name, field name, previous value and new value to a history table.
I can't seem to find a good way to do this.
Tracking Object Change Dates
Oct 7, 2005Is there a table where last DDL change date exists? For instance, if I have added a column to a table is there anywhere I can find when this occurred? Auditors are askng about this.
View 7 Replies View RelatedDatabase Change Tracking Software
Jul 20, 2005Does anyone know of software that tracks changes to a database? Forexample, it would track anytime an SP or view was recompiled, or ifyou added or deleted a column to a database?
View 1 Replies View RelatedSQL Server 2014 :: Change Data Capture - Minimum Latency?
Jan 28, 2015I am trying to use change data capture to load the data into the secondtable from table 1 which is coming from UI.
What will be the minimum latency??? Can we use incase of latency less than 5 seconds.
SQL Server 2008 :: Change Tracking On Table?
Jun 8, 2015best way to track changes for a very pesky table.We pull down a table from an Oracle database (via linked server) into our SQL Server on a daily basis. The data in this table is just truncated and reloaded daily. There is no "history" kept in the oracle database and values are not being "updated" its just dropped and repopulated.
So, I have toyed around with CDC and even creating my own custom auditing method but I can't come up with a reasonable solution that doesn't involve a massive audit table that doesn't provide much useful info.
For example, there are two date fields in the table that my customer wishes to have history tracked for. Every day, this table is truncated and reloaded - however those dates may stay the same for many many many months. If I turn on CDC, I will get tons of audit records for a delete and an insert every day but the values for the two date fields may not have even changed.
The table has tons of fields in it but I only care about the 2 date fields for history purposes.Here is a snippet of the table (I took out all the additional fields in the table and just left the two date fields that need to be tracked):
CREATE TABLE [dbo].[Fake_Name](
[lin] [char](6) NOT NULL,
[boip_no] [char](6) NOT NULL,
[dt_tc] [varchar](25) NULL,
[Code] .....
method to track changes to this table with it being truncated every day?
DB Engine :: Risk Of Enabling Change Tracking
Oct 6, 2015We are developing an application that requires change tracking.We tested it in development and test environments and we are preparing our production deployment.The very first thing that needs to be done is an
ALTER
DATABASE [db_name] SET
CHANGE_TRACKING =
ON (CHANGE_RETENTION
= 2 DAYS,
AUTO_CLEANUP =
ON)
We are holding on this first step because this statement alone executed for a good 4min on the development server. The production environment is many times larger and busier, and we can’t afford service disruption, so we are at the point where we need to understand what’s involved in running this ALTER DATABASE statement.Is there any documentation on what is happening behind the scene when this statement executes such that we can assess the risks of running it in production?
FullText Change-tracking And Update-index Were Disabled
Jul 30, 2007How can i enable my fulltex change-tracking and update-index in my table?
I recreated my fulltext catalog and start the full population, but although my fulltext index status shows active, my full-text change-tracking and the update index were disabled. - and I don't know how to enable them.
Thanks in advance
DB Engine :: How To Find Last Updated Change Tracking Time
Jul 30, 2015In a change tracking enabled database I can find the latest change tracking version number by using
Select CHANGE_TRACKING_CURRENT_VERSION() As Latest ChangeTrackingID.
Which will give latest change tracking id (example 1022), Is there a way to find the datetime of this latest change tracking id.
Change DB From Read-Only To Online
Dec 17, 2007Hi,
I restored my database and and have now decided that we do not need the Transaction Logs. I have forgotten the SQL Syntax to change the database to online.
Please could sommeone assist?
David Caddick
HELP - View Permissions Not Change In EM
Nov 6, 2000Is there a way to set it so that a user can view permissions in EM but not change them? I have tried using the SecurityAdmin role on the database, but this lets the user change the permissions. I really need to be able to do this, is there any way or can anyone make any other suggestions about this i.e., can you place the user in this role yet revoke the ability to commit a change?
View 2 Replies View RelatedHow To Change The Sql Queries Generated Internally By Functions Supported By MSADO Dll For MS SQL Server2005
May 3, 2007 We are trying a cluster setup on MS SQL 2005 with one m/c as Publisher (Primary) and another as Subscriber (Secondary).
When Publisher and Subscriber both are running, everything goes fine but when Publisher server goes down, subscriber server troubles.
We are using MSADO15.DLL for database connectivity.
We have a table where ID column is set to primary key with Auto Increment true.
We use AddNew() function of this library to insert a new record.
We fill a structure with all necessary values to pass it to AddNew() with ID field set to 0.
When we use AddNew() on the above table to insert a record, the ID auto inserted in table is correct but returned structure contains wrong ID value.
We tried to trace this problem using SQL Server Profiler Tool of SQL Server.
AddNew() function performs following operations in back end
1. Inserts the record to the table using INSERT
2. Calculates the auto increment field ID using SELECT @@IDENTITY
3. Fills this ID in the structure passed to AddNew() and returns it.
But ID returned by SELECT @@IDENTITY query is wrong.
There are other ways also to retrieve the last ID inserted in table by
IDENT_CURRENT(€˜table_name€™) which return right ID.
Can we change the call of SELECT @@IDENTITY to IDENT_CURRENT in AddNew() functions behavior of MSADO DLL?
Or there is another way of retrieving right ID?
DB Engine :: How To Convert Unique Clustered Index Into Clustered Primary Key To Use With Change Tracking
Sep 4, 2015We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies View RelatedPermissions To Change Table, Views And Procedures
May 20, 2008Hi
What permissions do I need to set so that a user can change tables, views and procedures?
SQL 2012 :: Permissions For Read Only Replica?
Jul 2, 2014I have an availability group with read only replicas, readable secondary set to yes and allow all incoming connections. I have also configured the read only routing (at least I'm pretty sure this is correct).
If I login to SSMS with a user in the sysadmin role I can view the objects in the read only replica database. If I login with a user in the public role I'm unable to get past the obvious error:
"The database databaseA is not accessbile. (ObjectExplorer)"
I've also tried adding the "ApplicationIntent=ReadOnly" option.
How Can I Change The Read-only Database For Add, Edit And Delete Users?
Jan 25, 2006Sorry about my English, it is not my natural language and thanks for your help. I have installed the Personal Site Starter Kit, everything work perfect except register users. When a new user try to register as a new user he receives an error, caused because the database is "read-only". In IIS the database has read and writing permissions and the directories where the aplication is. How can I change the database permissions?
Server Error in '/personalweb' Application.
Failed to update database "C:INETPUBWWWROOTPERSONALWEBAPP_DATAASPNETDB.MDF" because the database is read-only.
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: Failed to update database "C:INETPUBWWWROOTPERSONALWEBAPP_DATAASPNETDB.MDF" because the database is read-only.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Failed to update database "C:INETPUBWWWROOTPERSONALWEBAPP_DATAASPNETDB.MDF" because the database is read-only.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735078
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +3612
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +305
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.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) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
Is It Possible To Change Permissions Of SQL Server System Stored Procedures And DBCC Commands?
May 8, 2008Hi all,
I would like to enable users that do not belong to groups (server roles) such as sysadmin, serveradmin and don't have db permissions such as ddl_admin or db_owner to run some of the system stored procedures (such as sp_addumpdevice sp_configure sp_serveroption ...) and DBCC commands (such as DBCC CHECKFILEGROUP - requires ob_owner or sysadmin permission).
Is it possible to change permissions of SQL Server system stored procedures?
Is it possible to change permissions of SQL Server DBCC commands?
Thanks,
Assaf
DB Engine :: Collation Change And DDL Change In Same Script
Nov 18, 2015We ran into weird/interesting issue with below details.
Version:Â Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)Â Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
How Do I Change The SQL Server For Access ADP?
Jul 23, 2005I have MS SQL Server 2000 evaluation version on my desktop PC. I havethe SQL Server client tools (only) on the laptop. The two computers arenetworked. I had an Access MDB database on my laptop, but I convertedit with the Access upsizing wizard to an Access ADP database with theAccess front-end on the laptop connecting to the SQL Server on thedesktop. Now I want to move from this test environment to the "real"server. How do I go about this? Do I make the change from the laptop orthe desktop? Do I use the Access drop-down menu, Tools > DatabaseUtilities > Transfer Database? Or maybe from the desktop with: SQLServer Enterprise Manager > Copy Database Wizard?
View 1 Replies View RelatedChange Data In MS Access
Apr 17, 2008I have a database with a table of 1,123 records that I need to change in a
hurry! The problem is I do not know how to do it.... For example there are 2
fields that are numerical and formatted buy currency 1 record reads 400 and
the other reads 400, but I need to double the amount for both fields and all
of the records. Please help..... Thanx in advance for any and all help..
SQL Server 2012 :: How To Get Row Of First Change
May 12, 2015I would like to get extract the first row of some time based data where any of the other values changes. For example, in the following 10 rows
ABDTE
112015-01-01
112015-01-02
112015-01-03
212015-01-04
112015-01-05
1NULL2015-01-06
1NULL2015-01-07
212015-01-08
212015-01-09
222015-01-10
I want to remove the rows where A and B are the same as on the previous row. So rows 2, 3, 7 and 9 should be eliminated. Note that A and B can have the same values multiple times, just not in succession in the extract. I've tried ranking but I can't figure out how to keep it from lumping all the values of A and B in the same group. The following incorrectly eliminates rows 5 and 8:
;with data as (
select 1 as A, 1 as B, '2015-01-01' as DTE union
select 1 as A, 1 as B, '2015-01-02' as DTE union
select 1 as A, 1 as B, '2015-01-03' as DTE union
select 2 as A, 1 as B, '2015-01-04' as DTE union
[Code] .....
Of course the real data has many columns and multiple data types that can have nulls. I just want get the row when anything changes. Is there a slick way to do this in SQL?
Set The Minimum Permissions
Apr 26, 2007Hi All,How do I set just enough permissions on a database so that a developer cancreate new objects and modify them without giving him administratorprivilege. At the moment the db objects are dbo and execute permissions aregranted to indevidual through a schema.GRANT EXECUTE ON [dbo].[My_Prcsedure] TO [MySchema]Thanks
View 2 Replies View RelatedAccess Database Change To SQL Server
Apr 15, 2006I designed a database that works well in Access and I want to get it to run in SQL Server. How do I learn how and what to do to change over to SQL Server?
View 1 Replies View RelatedChange Script From MSAccess To SQL Access.
Dec 22, 2006I'm using this script I found on the web. After some tweaking here and there, it works great. I'd like to change it to pull data from a SQL source instead of Access. Anyone know how to do this, give me some pointers?
Here is the script
*****************************************************************************************************************
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<%
Dim DATA_PATH, Conn, DataRecords, email, user, pass, sendmail
'Maps to database. Change to your database path.
DATA_PATH=Server.Mappath("membersdb.mdb")
' Create and intiate data connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 30
Conn.Open "DBQ=" & DATA_PATH & ";Driver={Microsoft Access Driver (*.mdb)}; DriverId=25;MaxBufferSize=8192;Threads=20;", "admin", "password"
Set DataRecords = Server.CreateObject("ADODB.Recordset")
email=request.form("email")
'The magic query to look for registered members in the database
DataRecords.Open "SELECT * FROM MEMBERS WHERE email = '" & email & "'", Conn, 0, 1
%>
<%
'checks if email address exists in the database before sending a message.
if DataRecords.EOF then
%>
We could not find <%=email%> in our database.
<% Else %>
<%
'sets variables
email = request.form("email")
'chooses username and password from database that correspond to submitted email address.
user = DataRecords.Fields("usernames")
pass = DataRecords.Fields("password")
Set sendmail = Server.CreateObject("CDONTS.NewMail")
'put the webmaster address here
sendmail.From = "Someone@somewhere.com"
'The mail is sent to the address entered in the previous page.
sendmail.To = email
'Enter the subject of your mail here
sendmail.Subject = "Membership Login Information"
'This is the content of thr message.
sendmail.Body = "Per your request your account login information is: " & vbCrlf & vbCrlf _
& "Username=" & user & vbCrlf _
& "Password=" & pass & vbCrlf
'this sets mail priority.... 0=low 1=normal 2=high
'sendmail.Importance = 3 commented out causes a error for a call or procedure (line 41)
sendmail.Send
%>
We just sent your login information to <%=email%>.
You should receive it shortly.
<%
' Close Data Access Objects and free connection variables
Conn.Close
Set DataRecords = Nothing
Set Conn = Nothing
Set sendmail = Nothing
%><%end if%>
***************************************************************************************************************
And here's my SQL connection stuff
Private Sub Class_Initialize()
ConnectionString = "Provider=SQLNCLI.1;Persist Security Info=False;User ID=sa;Initial Catalog=ABC;Data Source=xx.xx.xx.xx"
User = "usr"
Password = "pswrd"
Set Converter = New clsConverter
Converter.DateFormat = Array("yyyy", "-", "mm", "-", "dd", " ", "HH", ":", "nn", ":", "ss")
Converter.BooleanFormat = Array(1, 0, Empty)
Set objConnection = Server.CreateObject("ADODB.Connection")
Database = "MSSQLServer"
Set Errors = New clsErrors
End Sub
****************************************************************************************************************
sure would appreciate some help!
Change An Access Database To SQL Server
Apr 15, 2006I designed a database that works well in Access and I want to get it to run in SQL Server. How do I learn how and what to do to change over to SQL Server?
View 13 Replies View RelatedSQL 2012 :: How To Change Parameter Panel From Top To Right
May 27, 2014Our client is asking to display the 'parameter panel in right side of the report' insted of top.
how can I do this?
SQL 2012 :: Change SERVER Log On Password
Aug 14, 2014What issues will I encounter if I change the MSSQLSERVER password?
View 2 Replies View RelatedSQL 2012 :: Change Name Of FILEGROUP Of DB In Server
Dec 5, 2014I create a db by following code. Now I want to change name of FILEGROUP. How can I do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
FILENAME = 'c:datamy1')
[Code] .....
SQL Server 2012 :: Change Name Of FILEGROUP Of DB
Dec 5, 2014I create a db by following code. now i want to change name of FILEGROUP. how can i do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
[Code] .....
SQL 2012 :: How To Change First TD To Add BGCOLOR In Script
Apr 1, 2015how to change the first <TD> to add BGCOLOR in this script:
declare @body varchar(max)
-- Create the body
set @body = cast( (
select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )
[code]...