I have a problem at the company I work for. For historic reasons many developers and users know the sa passwords for SQL. Obviously this is not a good situtation to be in. Now the obvious answer it to just change this but for reasons unknown to sanity half of our applications are hardcoded to use sa passwords.
Now as a DBA coming into this this puts me in a tricky position as its impossible for me to control who is doing what etc. Is it possible to put triggers or something to monitor logins which will deny access to anyone accessing via the sa password not through an application. Ie if an application calls then the sa will have to be allowed but if a user using Query Analyser for example tries to use sa then they are denied access ?
Long term all these passwords are going to change but it the short term I still need to create some control on the system. Anyone have any thoughts ?
I have windows application (Basically a accounting system). I want to use either SQL authenticaion, or Windows authentication for my users of the application (Basically each db user will have their own account in Users for that db, so I can have sql control the access).
All access to the db is via stored procedures, but I want to have a set schema for each user type:
Supervisor Manager Data Entry etc.
All with a predefined access to the proper stored procedures(So far fairly simple).
In addition to this I want to be able to set the specifics of that user in my .net application via a interface (so a user by default will have the pre-defined schema, when a supervisor opens this interface, they can add/remove access to other stored procedures)
Here's the issue. Is this the proper methodology to achieve this, because it's quite of bit of programming work. I don't want to do it then need to re-due everything.
Also should I use Windows Authentication, or SQL, or have a completly seperate table in the DB where I do manual authentication then have the .net application restrict the access?
Any help or comments on this approach will be helpful. Also ifanyone has found any articles or information similiar to what I am trying to accomplish, please point me in the correct direction.
I've been parachuted into a place with a few dozen servers, several thousand external users (and scheduled to grow). Right now the whole place - IT and non-IT alike - l logs in with SA.....
(where's the 'Puking' icon??)
They've got Windows logins, but that's about as tight as it gets. Honestly I've never seen anywhere as insecure....and not sure where to start.
I got a big Access file (1 400 tables) to convert to SQL and I wouldlike to be provided with some automated tools, except upsizing wizardand DTS, to convert it on my own.I got a lot of forms and query to convert too.Can someday provide me with at least one tool name ?Thanks,Guy
Please someone tell me, is it possbile to run Reporting service in MS-Access environment? I mean to say i have to use MS-Access as user interface to design and generate report (instead of Visual studio IDE) and the Sql server 2005 should be Report Server. If it is possible then how can i implement it.
I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
Currently I can not open an MS Access file that was brought from one of the Europe offices, I got a error message "ODBC--Cnnection to Supportdb_sql7.mdb failed when I tried to open it. Supportdb_sql7.mdb is the file name.
I'm using SQL server 7 on Win NT. I have Access 97 as a front end, with linked tables though ODBC to SQL Server. Everytime I open a table in Access, a session appears when I type sp_who2. I close that table in Access, but I when I type sp_who2 the table session is still present. Does anyone know a cause for this?
I am researching why sometimes when we close are queries and tables in Access we have sessions in SQL server that becomes orphans/ghost. I try to kill the session but can't, so therefore I have to recycle the database.
I am very new to VB.Net but my goal is to build a web app that ineracts with an Access database. This is a very small scale app so I am not interested in using SQL server. I created my Access database and the udl file for connection information.
I created a class that opens a connection to the database when the webpage loads.
I get the following error though,
System.Data.OleDb.OleDbException was unhandled by user code ErrorCode=-2147467259 Message="The Microsoft Jet database engine cannot open the file 'C:InetpubwwwrootTest1RaceData.mdb'. It is already opened exclusively by another user, or you need permission to view its data." Source="Microsoft JET Database Engine" StackTrace: at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at clsDBConn.Open_Connection() in c:inetpubwwwrootTest1App_CodeclsDBConn.vb:line 12 at Default2.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootTest1Default2.aspx.vb:line 8 at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
The database is not open so I know it is a matter of security. I have configured IIS to use an admin account and the Access db does not have security turned on. In the UDL file I have selected read/write access and the test connection works.
Can anyone help me narrow down the root of this error,
we canīt open most of the sql server tables from our Access project any more, which has to be related to collation or extended property problems.
trying to open any table will result in the information "The stored procedure has been executed but did not return any records" after the first try and later on nothing will happen any more; tables wonīt open.
basically this started to happen after recreating the sql database due to resolution 1 in Microsoft Knowledge Base Article - 318989: collation of the server is Latin1_General_CI_AS collation of the old database was SQL_Latin1_General_CP1_CI_AS, which led to the error described in the article. collation of the new database is set to <Database Standard>, now i have the problems described above.
anybody some good help on that?
ps: we canīt really rebuild the user database all the time, because we got flight data in about 4 billion records in there, so we will need anything quick and easy... thanks
Event 17204, FCB::Open failed: Could not open file F:MSSQLDatafilename.mdf for file number 1. Â OS error: 5(Access is denied.).When I look at the file permissions of filename.mdf, there is no MSSQLSERVER group permissions listed nor can I add it. I have tried to add MSSQLSERVER and NT SERVICEMS SQL $MSSQLSERVER but neither exist. There is also a ReportServer.mdf in the same folder with MSSQLSERVER permissions!Did I somehow delete this group? What can I do to restore this permission?
I cannot open now my database after changing it name from the folder where it is save and also changed back to its original name.Message recovery pending now is appearing as shown below.Any chance that I could recover my database back?
I have a sequence problem in a package and I'm unable to figure it out: I have a control flow with some elements in this specified order: a script task -> Execute SQL task -> Data Flow. The script task is responsible for setting a value on a package variable; this same variable will then be used to decide if the Data Flow is disabled or not (there's an expression on the DataFlow). However, it looks like the variable is being tested before the script actually writes it, how can I guarantee that the DataFlow's expression is evaluated only after the script task has ended? Also, I have tried putting each component inside their own sequence container and I get the same result
Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?
I have a main report and wanted to embed a sub report in the main table. This is pretty easy enough.
The main report lists the suppliers we have. And the sub report lists any sub suppliers affialiated to those suppliers.
I have set the sub report visibility settings to hidden and is toggled by the suppliers ID.
The problem is that NOT ALL suppliers will have sub-suppliers, yet when you run the report the user gets presented with the option to toggle all suppliers' ID.
Is there any way to have a main report that only allows the user to click on a suppliers ID when that particular supplier has a sub supplier?
I installed SQL 2005 including backward compatibility, MSDN libraries and SP2 a new Windows 2003 server (chose mixed mode authentication). Installation was successful and I then installed an off the shelf database with Windows authentication which also installed successfully. I created a new group in AD, added the database users into the group and gave db-owner rights to the group in SQL as advised by the installation guide. However, when I try to open the database it gives an error message saying "Cannot open database.[DBNETLIB][ConnectionOpen(Connect()).] SQL server does not exist or access denied". I also tested it adding an individual user (myself) as a user to no avail. When I set up DSN in ODBC on my computer I can successfully test the connection but can't run the application. The connection is via TCP/IP. Any suggestions?
I am receving this error: Failed to open the connection: [Database Vendor Code: 17 ], from .Net 2003/C# to Crystal Report XI with SQL Server 2000 has backend database.
Is there a security permission that has been overlooked?
//Get Table inf from report DB = reportDocument..Database; tables = DB.Tables;
//Looping through all the tables in CR and apply connection info for(int i = 0; i < tables.Count; i++) { table = tables[ i ]; tableLogOnInfo = table.LogOnInfo; tableLogOnInfo.ConnectionInfo = connectionInfo; table.ApplyLogOnInfo(tableLogOnInfo);
Hi everyone: I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like the TRY-CATCH-FINALLY statement (in SQL-92 for SQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error is raised. Can it be done? Thanks for any help.
Can someone tell me how I can manage different versions of my OLAP Database, in Analysis Services 2000 without having to use Archive database ? I want only the design of my cubes and my dimensions. Is there any way to get the script of a cube?
Is there any tool available to do this kind of actions ?
Hi,I'm writing an application that involves data that has a set of usersthat are allowed to perform certain operations on it.i.e. Only the row owner can modify a row, but there is a set of userswho can view it.At the moment, I've started to implement this by calling a UDF at thebeginning of each stored procedure that validates that the user isallowed to call the procedure on that particular row (trusting a higherteir to verify the user), and throws an error if they are not.I don't particularly like this solution, as I need a UDF for eachprocedure, and will have to re-write the udf's if the access ruleschange (which they might).Can anyone suggest a method of implementing a more generic rowpermissions system?Cheers,Ben
We have an odd OLE DB error here. We can create the connection but only lookup a handfull of tables that are in the database. (Maybe only 10 of 50 - of which I need the ones not being displayed) Even if I choose a table from the incomplete list and try and close the dialog I get the following error. We are trying to pull data from an IBM AS400 if that makes any difference. It did work in SQL2000 - Hope you can help us!!!
---
ERROR at HTEIMPORT(table) [connection manager "IPaddress.user.database"]; SSIS Eroor Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E33. An OLE DB record is available. Source: "Microsfot OLE DB Service Components" Hresult: 0x80040E22 Description: "Non-NULL controlling IUnknown was specified, and either the requested interface was not IUnknown, or the provider does not support COM aggregation.".
Error at SQLTaskName [OLE DB Source [423]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AquireConnection method call to the connection manage "IPAddress.useratabase" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AquaireConnection method call failed.
Additional information: Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
I have a few hundred data files that I need to process in groups from oldest to most recent. To that end, I have created staging folders for each date that appears in the file names (example: 20060213) and moved all of the data files to the appropriate folders.
I also have these folder names in a recordset that has been sorted in ascending order, so the oldest date should be the first record, the second-oldest date should be second and so on. My thinking is to use this recordset with a ForEach Loop Container to loop through each folder, but I am not sure if Integration Services will necessarily loop through the records in order.
Can someone tell me whether the records will be processed in order or suggest a better alternative to make sure the directories are processed in the correct order?
I have a one table with 30-second updates for many day's worth of data. What I'd like to do is only select out the datetime between, say 07:00:00.000 and 11:00:00.000 for each day.
select * from TableName where "timestamp" between '2007-06-04 07:00:00.000' and '2007-06-04 11:00:00.000'
This works for one specific day. But I'd like a return of values for all possible days in the table (as opposed to just 2007-06-04).
Hey Folks! I have a typical requirement by my client. On submitting a Update (Bulk) button a huge database operation starts. A huge bulk update operation need to be performed. This would take 2-3 minutes some times. Client wants a cancel button in this case where he can be given a way to cancel the database Transaction. Please let me know in case if there is a way out. Thanks, in advance. Regards, Uday.D
Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst
A similar question has been asked in a different thread, but didn't get a conclusive answer. Is there a way to set process priority on a specific process (alternatively - processes started by a specific user), similar to what the Task Manager allows in Windows?
I need to archive some data from an OLTP database. Unfortunately, some of the tables are growing rather large, and copying the data, and especially removing it based on record age, is a resource-intensive operation. My database must be available 24/7, and I can't have queries timing out, or I start losing business real fast. I was thinking of firing off the archive script and setting the process priority on it to something lower than normal, so it won't interfere with my OLTP processes.
I have a report I'm trying to modify for a client that has two auto-grow tablix. Â We want the second tablix to start after the first one completes, however the first tablix will overwrite the second one if it contains multiple rows, rather than pushing it down. How can I control this?
I'm using Reporting Services 2000 and I have a situation where I need to specify the file formats available to export. I know I can change the RSReportServer.config file to change these at a global level, but what I really need is to be able to configure these differently for different user groups. For example:
group 1 - has all available export formats available
group 2 - only has the ability to export to excel
group 3 - has no option to export to any format.
Is there a way for this to be done? If so, can anybody give me any pointers on how it can be done?
1. To control an SSIS package , start, check status, and emergency stop + rollback a package from a web page. Does anyone know of an example or good articles to start with.
2. I have one of the iterations of the data invoke and use a COM object (third party) It will value the items and change a field.
I can always do #2 as a second step but I need all the help I can get on #1.
VS2005/SSR2005 Reporting: How can one control an individual report fields visibility (toggle on/off) at run time, based on data values (of the same field or another field) in the report. Without user having to sit there and "Click".
