SQL Server 2008 :: How To Temporarily Block A Table From User Access
Feb 10, 2015
I need to run a script in production that adds primary key to a table. Because table is large, I can't run it in one shot, the log file is not so large to accommodate it. Instead, I created a new table with same structure plus new surrogate primary key, and I populate it in a cursor loop.
I already ran it many times in test server, and no problems with that. But the problem will be in production when applications will be accessing this table and try to insert/update it while I am running my loop.
So I am looking for a solution how to block users to access this table, it's OK if they receive an error. Setting database to single user mode will not work because I don't want to block them from all the rest tables in this database.
I have transactional replication configured where the publisher and subscriber are on two different servers. Yesterday a database upgrade was carried out, and the DBA dropped replication by issuing sp_removedbreplication on the published database. The subscription is still set up.
I have two questions:
1 - What is the safest way to temporarily switch off replication without losing the publication or subscription? As far as I was aware (my replication knowledge isn't great), simply disabling the relevant agents would do the job.
2 - I now have the task of creating the publication again (fortunately we have a saved script). If I recreate this publication, will I be able to point the existing subscription at it?
I am designing an application where multiple users can be assign to a product for review. If a user doesn't have access to the product, they are not allowed to see it. I have attached my table design. All users are assign to a role. See attached screen
I am testing Insert, Update etc. and messing up my database with "dirty" data. Is there a way to temporarily drop FOREIGN KEY constraints to truncate a table after testing without dropping the whole table and rebuilding it? Newbie
Hi, Is there anyway I can impliment this in my application without installing it as a COM. My Web Hoster says they won't install it. The Data Access Application Block
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
I download the DAAB CF version. However, most of them use SQLCEClient to connect to the database. Is there any DAAB that use SQLClient? One workaround that I made is I replaced all SQLCEClient objects with the SQLClient objects. I only test one method "ExecuteDataset" and it works so far. However, my question is "does my workaround a good solution?"
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time. I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server. Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines?? Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server? Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
I'm trying to use the Data Access Application block, and am having some issues with configuration. I am using it in a class library, and it seems that with v 3 you need to configure the DAAB first, making changes to the configuration file. However, in a class library, I do not seem to have the web.config or app.config file to change. So where do I need to store the configuration settings? Thanks, Paul
We are expanding an application's database hardware to have a mirrored failover server. Normally in this case we just have the application add "Failover Partner=MirredServerName" in the connection string.
However, this application is using the Enterprise Library Data Access Block and we can't find any documentation describing how to setup the Failover Partner using this library. Any help would be appreciated.
My problem is that I can't query a couple of tables. I created them using DTS myself, in a db called contracts, and called one of them 010425. I've logged in as SA, and making sure the correct DB is shown in Query Analyser.
Here's the query...and very simple it is;
SELECT TOP 10 * FROM [dbo].[010425]
I just keep getting the following error message;
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.010425'.
But it shows up in Sysobjects and EM!
I can query the system tables, using EXACTLY the same syntax...no problem there.
I've got SQL Server 7 running on W98. Everything is happening locally.
I am trying to execute oracle procedure (anonymous block) with input and output parameters from ms sql code and return 2 values back:a number and a string.I am using **EXEC() AT** to execute oracle code and return a string back to ms sql. It works fine when returning numbers, but if I add a string output parameter, I am receiving an error:Msg 0, Level 11, State 0, Line 0...A severe error occurred on the current command. The results, if any, should be discarded.
The server log has the following entry in exception.txt:
Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION reading address 00000005031C07E0 at 0x0000000070F9C183 There is also a SQLDump file generated: * BEGIN STACK DUMP: * 10/28/15 12:45:38 spid 63 * Exception Address = 000000005040C183 Module(MSVCR100+000000000003C183)
[code]....
It looks like the problem is in passing varchar2 from oracle into ms sql. I had logging code in oracle block, and it works fine. I have found a few examples of using **exec() at** but there is always only integer output passed back.
I have the following stored procedure to test scope of variables
alter proc updatePrereq @pcntr int, @pmax int as begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int set @z = 1 declare @max int set @max = 5 while @z <= @max begin exec dbo.updatePrereq @z, @max set @z = @z + 1 end go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
I know it is a simple question. I am writing a query to import a table (MyTable) from an Access Database (MyDatabase.mdb). MyDatabase.mdb is in C: in local. This is the query from BOL.
SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:MyDatabase.mdb';'admin';'mypwd', MyTable) AS a
By BOL, admin and mypwd should be user_ID and password. But I am doing testing in local, and don't set user_ID and password. How to handle two things.
Poor Performance Temporarily Cured by Re-booting SQL Server
Please can you help?
SQL Server 2000. Accessed by VB.Net 2003. Server set up by SQL consultant (who is no longer available).
We have a regular problem, which is most noticeable when analysing data (rather than data inserts/deletions) for producing xml or csv files or reports, even though the amount of data is relatively small. However structure of database means that there are still quite a lot of records and a lot of links between tables (database is currently being redesigned but in the mean time the problem still exists).
The problem is that access slows down dramatically and in some cases all but stops (one example is a csv file being produced where only a few lines have been output in 30 minutes). In every case the symptoms can be solved by re-booting the SQL server, after which the entire csv file is output in 10 minutes. Obviously though we wish to solve the problem and not just the symptom.
Additional information: Transaction logs are backed up at 13:00 and in the evening (both periods of very low usage).Full backup is run in the evening and at 13:00 (both periods of very low usage), with integrity check performed prior to full backup. Database is optimized after the full backup including shrinking the database leaving 15% of the data space free.
This is my code... public partial class test : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) {SqlDatabase database = (SqlDatabase)DatabaseFactory.CreateDatabase("ConnectionString");DbCommand command = database.GetSqlStringCommand("SELECT UserName FROM Users WHERE UserID = '6264'"); using (IDataReader reader = database.ExecuteReader(command)) {if (reader.Read()) {lblTest.Text = "test"; } } } }
This is my error: Server Error in '/' Application.
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. 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.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.Source Error:
Line 21: //DbCommand command = database.GetStoredProcCommand("CorrUsers_GetByUserId_s"); Line 22: DbCommand command = database.GetSqlStringCommand("SELECT UserName FROM Users WHERE UserID = '6264'"); Line 23: database.ExecuteReader(command); Line 24: using (IDataReader reader = database.ExecuteReader(command)) Line 25: {Source File: d:webwwwrootchacha078dev.cha.toplingo.com est.aspx.cs Line: 23 Stack Trace:
[InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.] System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +1261381 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection() +195 Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection(Boolean disposeInnerConnection) +106 Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) +62 test.Page_Load(Object sender, EventArgs e) in d:webwwwrootchacha078dev.cha.toplingo.com est.aspx.cs:23 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436
Any help with this matter would be very much appreciated. Thank you for your time. Sincerely, Jeffrey Pham
Is there anyway to find the permissions assigned to user. What level of access the user has in each db and the tables & is he able to see jobs. Each object and permissions
Several apps connecting to a SQL 2008 server show the users having a hostname of "Various" when doing an sp_who2 or looking at relevant DMVs.
I joined dm_exec_sessions to dm_exec_connections and was able to get the IP addresses and they resolve back to real clients. So why they'e not displayed and I see "Various" instead?
I am using a SQL server 2008 database.I am working on a windows user.I noticed when I create a user in a database for a login, even that login does not exist in the database, the user is still successful created.I suppose it should error out.
Or I miss something?
I am using this script: IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'mydomainmyuser') CREATE USER [mydomainmyuser] FOR LOGIN [mydomainmyuser] WITH DEFAULT_SCHEMA=[dbo] GO
How do I grant a user permissions to only one table in a database. How would it affect him using our Main App which is NAV with regards to his user's permission in NAV
Hi there! I need such thing: I have a data in Table A. When I get this data, I also increment this data by one, what I want is block Table A in order to other people will not get access to data.. I think about transactions, but are transactions reaaly blocks a table's content? P.S. For example in MySQL there are Lock table command for blocking table for reading/writing. I need such thing/ Thanks
I just notice that my MASTER database has some user tables and user SP ..and I am thinking to move them to 1 new user database but I am worried it will break something ..
What should I do ?
Moreover I wonder why Transaction log of MASTER can be full ( The recovery model is simple ) It should be fine , isn’t it?
I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.
I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.
Hello,I am getting a SqlException with title "SqlException was unhandled by user code" and then it says "Invalid object name 'Access Table'.here is my code (this is from my login page:)<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection Dim cmd As SqlCommand Dim cmdString As String = "SELECT [Password] FROM [AccessTable] WHERE" & _ " (([Username] = @Username) AND ([Password] = @Password))"
conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance") cmd = New SqlCommand(cmdString, conn) cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50) cmd.Parameters("@Username").Value = Me.Login1.UserName cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50) cmd.Parameters("@Password").Value = Me.Login1.Password conn.Open() Dim myReader As SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) If myReader.Read() Then FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False) Else Response.Write("Invalid credentials") End If myReader.Close()
End Sub </script> The error is comming from the myReader = cmd.Execute(CommandBehavior.CloseConnection)Thanks for any suggestions or ideas.
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual (empno, accrued_vacation, accrued_sick_effective_date, accrued_sick, import_date)
I have around 600 databases in my server, a user need select access of all the databases. will i have to go one by one in all the dbs and create that user and give datareader role to him. or is thr any shorter way to do so????
