My SQL 7.0 server is currently querying the SAM database on the PDC for Windows NT authentication. How can I force it to use the SAM database on the server(BDC) that I specify?
View 1 RepliesSelect A.* from A inner join B on ( A.ID= B.ID )
I know there is some key word that you use to force SQL server to generate a new query plan ?What can that be ?
We have experienced an issue with back backup / restore of a database originating from SQL Server 2000 to SQL Server 2003.
We have the following setup:
SQL Server 2000
- DatabaseA
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseA
- DatabaseB
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseB
SQL Server 2005 Standard
-asdfUser is NOT Setup as a user yet.
-We restore DatabaseA and DatabaseB to the SQL Server 2005 Standard. The databases are restored with the security permissions of asdfUser being the DB Owner of DatabaseA and DatabaseB.
-We create a new SQL user named asdfUser on the SQL Server 2005 box. We then try to add the UserMapping of DBOWNER for the DatabaseA and DatabaseB. We receive an error message stating that the asdfUser already have permissions to the databases. We proceed with the user creation without those permissions.
-We proceed to the login properties of the asdfuser and view their UserMappings. The asdfUser does not have access to DatabaseA or DatabaseB. We then add the UserMapping of DBOWNER to both DatabaseA and DatabaseB. We Try to select OK and we receive an error message that states that the user already has those permissions.
-When we query the UserID's of the asdfUser that is in the database and the UserID of the asdfUser that is created, they are two different values.
I assume this is a bug... any word on a fix?
This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled storedprocedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the natureof a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highlyspecialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by thesavings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of yourapplication to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this: CREATE PROCEDURE ProcName @Param int /* ... other parameters */ WITH RECOMPILE AS /* ... procedure code follows */
If we omit "WITH RECOMPILE", what will be the consequence? Thanks
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:Â
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
I had created 2 Sql server instance in 2 servers created using VMware. From the primary server I log shipped the required databases into the secondary. Both the servers were in the same domain whose active directory was also in another server in the same virtual lab environment. My question can we have the primary sql server in one domain and the secondary sql server to which the logs are shipped in another domain by including a router also between the 2 networks for connectivity?
View 6 Replies View RelatedHi,
We are using VB as frontend and SQL Server 7.0 as backend applications in our company. The users are using 'User DSN' to connect the databse with NT logged in user security. User can connect the database if he is having the permissions on the database. We are not using SQL Server authontication like 'sa' user. Till here it is working fine. Our problem is, We want to avoid the users to connect the database using SQL Server Enterprise Manager. We have the SQL Server Enterprise Manager software installed on some machines. because some times we may need that. Right now the users (If he is having the permissions on the database) can directly open the database using Enterprise Manager and they can manipulate the data directly.
We need only specific users like The users who are in System Administrators group has to access the database using Enterprise Manager.
Please help on this.
Thanks,
Arun.
I need to investigate about what happened to our production server at the last weekend.i restored it to another server which is development. I restored it under a name "old_master_2015_07_10".
But if I run a query
SELECT *
FROM [old_master_2015_07_10].[sys].[servers]
it actually selects from master.sys.servers, not from my old_master... In order to prove it, I created a linked server in this, development server, and if I run SELECT * FROM [old_master_2015_07_10].[sys].[servers], it selects it. And in database selection drop-down box I also selected old_ master_ 2015_07_10. What I think it apparently recognizes familiar names like sys.servers and redirects the query to the master.
What I can do to select really from old_master_2015_07_10 database? I already thought about renaming sys.servers to something different, but did not do it not to break something in master in case if SQL Server will run it in master as well.
Hello all.I'm using ADO to connect to a SQL Server database and run a T-SQLquery.The script template I'm using can be found here:http://groups.google.com/group/micr...56?dmode=sourceWhen I run a VBScript, I get no popups. When I run an HTA, I get thefollowing popup:"This page is accessing a data source on another domain. Do you want toallow this?" [Yes] [No]How can I turn off this warning within the script - without having togo into the Tools...Internet Options...Security...Trusted Sites menu?Any help would be greatly appreciated. Thanks!- Dave
View 3 Replies View RelatedWe are trying to track Linked servers configuration. Is it possible to query Linked server configuration like mappings, options, etc?
View 2 Replies View RelatedHi
I'm going to put my database in setup file that I've been ready before.
My Software could be use from database after installing in every system , but other users won't be able to access to data or structure of database , even with adding of database to SQLServe Enterprise Manager .
How Should I can do about this ?
Tanks very much
We can restrict users from accessing the databases from the security. But is there anyway , we can restrict users from seeing the available databases on the server, user can access and see the database he or she has access to other databases will not be visible?
View 13 Replies View RelatedHow do I add an Importer as a user in my sql server database security folder.
Regards
Karen
I have a database used in SSRS in which I get a backup daily and restore it to database "DATA"
The issue I come across is SSRS has a datareader user(ssrsuser) for access to the "report store" web where users get reports
access gets erased on each restore. Can I add something to my job to restore this user after each restore or keep the settings?
declare @Bakpath varchar(80)
set @bakpath = 'C:EDataExtractedDataextract.bak'
Use Master
Alter Database [Data]
SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE [Data] FROM DISK = @bakpath --location of .bak file
WITH REPLACE
GO
We are unable to connect with Client SQL database server using VPN connection, even we are able connect with their intranet site.
We tried to connect using IP address, but no success. After that tried to ping that server with server name and IP address using command prompt, but got request time out error.
I have 2 SQL 2008 R2 Servers which are always running (on is Prod/one is Test).
is there a way to sync Prod to Test WITHOUT using the .bak and overriding Test's Security settings?
We have a production server "prod"  which has a user say 'test' that lets the users connect to the application and  a logon trigger which stops them from connecting to the server through SSMS. I  log ship 'prod' to the 'rep' database and the user 'test' is obviously created in the logshipped database 'rep' during the logshipping.
Now the login 'test' does not have any login from server 'rep', But people can still login to the 'rep' server and query the 'rep' db . I checked with SUSER_SNAME and found the SID of the user in rep server which I never created and which is not even present in the login names, Even If I create a new username in the 'prod' db, after logshipping the new user is replicated in the 'rep' server.
someone was alter user  databases owner access.
how to find  who alter evoke  the database user access changes in sql server.
I have a SQL Server 2014 database that is partially contained. I am trying to connect to the database via Excel using a contained user login. When I choose Data - Other Sources - From SQL Server, I get the Data Connection Wizard window to Connect to the Server.
This window allows me to enter the server name and a User Name and Password (for SQL authentication), however it does not allow me to specify a database. When I click the Next button, I get a 'login failed' error which I assume is happening because the contained user is not a SQL login.
I have the same issue trying to create an ODBC connection using the ODBC Data Source Administrator.Â
Is there another way for me to connect to a SQL Server database using a contained login with Excel?Â
SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:
The server principal "XYuser" is not able to access the database "Ydb" under the current security context
This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john
If I do the query below, SQL Server does a table scan (thousands of rows) for fn_TestCol(), then evaluates the CONTAINS clause:
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
AND fn_TestCol(col1) = 0
How can I force it to evaluate CONTAINS clause, which returns only a few rows, first? The best I've come up with is this:
SELECT sub.col1, sub.col2
FROM (
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
) sub
WHERE fn_TestCol(sub.col1) = 0
It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.
I'm using SQL Server 2012 and was attempting to move the msdb, model, and tempdb databases to a new location and accidentally gave their log files an mdf extension instead of ldf when providing the new pathfilename. After the server wouldn't start I checked my script and noticed my error. I have good backups of my system databases, so I was hoping to start the MSSQLSERVER service in single-user mode (using the -m startup parameter) and then just restore master using sqlcmd.Â
Unfortunately the service was starting but I couldn't connect via sqlcmd using any of the three protocols (it said the server was not found or not accessible each time). I also tried using the dedicated Admin connection but I got the same error. Then I went into the Templates folder and copied the master, msdb, model, and tempdb templates into the DATA folder and tried to restart SQL Server but still no luck (now the MSSQLSERVER service won't start at all). Is there an easy way to fix this mess without having to reinstall from the setup application?
I have a problem with an instance of SQL Server that refuses torespond to a shutdown request. I've managed to shutdown the SQLManager and DTC services but the sqlservr.exe process is permanentlyin a "Stopping" state.I cannot logon to the instance to issue a SHUTDOWN WITH NOWAITcommand. Short of rebooting the entire server, is there a way I canforce the process to end?Tony
View 3 Replies View RelatedHi,
I am at a loss here, unless I misunderstand the whole point about server encryption. My 2005 SQL server has a certificate from a trust CA, I have turned on the 'force encryption' flags on the server. My understanding is any client will be "force" to connect with encryption?? I found out that unless I turn on encryption on my clients, the server will allow connections without the requiring encryption. Am I missing something here? Thanks for any help you can provide.
Hello - the very nature of this question seems to make no sense I know - but we received a huge volume of data (29 tables) in flat file format. I first imported them into MS Access because of its portability and it seemed to be more forgiving on imports. Now I have a complete MS Access DB with all tables, so I figured importing to SQL server should be a snap. However, on the import, I had 14 tables import successfully, and 15 failed!
Here is an example of one of the error messages I received:
Insert Error, Column 3 - status 6; Data Overflow...this was on a date/time field in access, and here is the data contained in the referenced row/column: "8/19/4999"
the year "4999" is obviously the problem (at least i think), and I have no idea why this successfully imported to MS Access, but not to SQL Server....
what i'd like to be able to do (not the best practice, i know) for now is ignore these types of errors - and just force SQL server to take the data straight from MS Access and replicate it. We received this data from a 3rd party, and there's no telling how many data entry errors like this could be in each table - many of the tables have over 500,000 rows, and i don't want to have to go through fixing each of these errors by hand...anyone have any ideas?
I have added an email task to the ON Error Event of my SSIS package, so that I will always know when there are errors.
However I would like the SQL Server job executing the package to succeed even if the package fails.
What setting do I change in the SSIS packageto achieve this? MaximumErrorCount?
When I create/alter a store procedure in SQL Server 2005, SQL server always checks for syntax errors first and won't let me save the change if it detects any error. Is there a way we can force the SQL server to save the store procedure that fails the syntax check?
I know SQL server will allow such invalid store procedures if you detach & re-attach the entire database from one SQL server to another server. However, if I try to manually create the same store procedure from one server on a different server with a script, then it won€™t let you save the store procedure if the linked server (or the table) can€™t be accessed from the new sql server.
How do you get around this?
Thanks
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies View RelatedIs it possible to force row level locking in Sql server 2015 before inserting the data and release the same afterwords..find the code for which we would like to impliment the same
DECLARE @TravelAgentid Varchar(20)
DECLARE @Date DATETIME2(7)
DECLARE @InsDate DATETIME2(7)
set @TravelAgentid ='A101'
[code]....
I have setup a Database Audit Specification as follows:
Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public
Now, when I perform a SELECT query with a bound parameter such as:
SELECT * FROM myTable WHERE name='queryname'
What I see through the Audit Logs is something like:
SELECT * FROM myTable WHERE name='@1'
I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using
(1) SQL Server Enterprise Edition,
(2) SQL Server Standard Edition, or
(3) by an external tool.
I want the following query to return a row even when table 'X' is empty. How would I do this?
SELECT TOP 1 @Var1, @Var2, @Var3 from X
The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included.
When table is empty, it reurn nothing. It only return a row when table is not empty.
We are discovering that adding Force Order to a query is substantially increasing performance. Any issues around using this ?
Craig
I have another post here regarding SQL 2005 running a query 50% slower than on 2000. It was discovered that 2005 runs the query in series whereas 2000 runs it in parallel.
Even with "Cost Threshold For Parallelism" set to a default value – 0, 2005 still executes my query in series. Does anyone know how to force a query to run in parallel in SQL 2005. I specifically want to set it at the database level.