Posted by jpluimers on 2013/01/18
In most database index nodes are doubly linked to allow bi-directional scans. http://is.gd/8CMb7w, however not for InterBase and FireBird, there the reverse link isn’t used because it can be inconsistent due to write order of index pages.
The result is that in Firebird and InterBase, indexes are single-directional (either ascending or descending).
This is for your safety: it guarantees index consistency, even if because of EMP, your machine suddenly reboots after your tank fired a missile.
via Twitter / Avalanche1979: @SQLPerfTips For Firebird the ….
(Wow, did I really wrote 1200 blog posts?)
Posted in Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7, Sybase | Leave a Comment »
Posted by jpluimers on 2013/01/02
On development machines it can be comfortable to add the local Administrators group to SQL Server, and make them equivalent to SA (SQL Server Administrator).
This especially as SA is disabled by default when you install using Windows Authentication mode (which is the default). You can Change Server Authentication Mode to include SQL Server mode, but then you still have to enable SA (you can even rename SA)
This is basically what you want to do:
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin';
GRANT CONTROL SERVER TO [BUILTIN\Administrators];
There are a few gotchas here:
- The name of the group BUILTIN\Administrators depends on the localization of your system.
This is one of the reasons I usually advise clients to have server systems run on the most common ENU (English USA) localization of Windows.
Another reason is that it is far easier to find information ENU English Messages back on the internet.
- You need to be SQL Server Administrator to begin with.
There is a little trick to get this done: you can stop the SQL Server service, then restart SQL Server it in single-user mode.
In single-user mode, members from the BUILTIN\Administrators group can connect as a member of the sysadmin fixed server role.
- If you want to access SQL Server as member from BUILTIN\Administrators, you need to run your SQL client tools with the UAC elevated permission (otherwise the Administrative bit is not set, and the BUILTIN\Administrators is not recognized).
That’s what the batch file below solves.
You need to start it as member of BUILTIN\Administrators with elevated privilege (the easiest way is to run it from an elevated command prompt).
It will: Read the rest of this entry »
Posted in Batch-Files, Database Development, Development, Scripting, Software Development, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2012/12/25
In SQL Server 2000 and up, the easiest way to re-add a user that got orphaned is with a script like below.
The script does not correct the SID, but in stead sets new permissions (in this case, db_datareader and db_datawriter). Most of the times that is not a problem.
The script uses these stored procedures:
- sp_dropuser – drops a user from the current database
- sp_droplogin – drops a login from the current server
- sp_addlogin - adds a login to the database server
- sp_adduser – adds a user to the current database (you can add both a SQL user – with name and password – and a Windows user)
- sp_addrolemember – adds a member to a certain role
More modern versions have alternatives to these stored procedures, but the stored procedures work with the widest ranges of SQL Server versions.
-- Execute this script as SA or DB Administrator
use MyDatabase -- the databae where you want the user to be re-added to
-- you cannot perform a 'use [MyDatabase]' without destroying the context (and declared variables)
-- it is possible with exec (@exec_stmt) (as sp_droplogin does it), but it is a bit cumbersome for a relatively simple script
declare @loginname sysname
declare @passwd sysname
set @loginname = 'MyUser'
set @passwd = 'MyPassword'
-- from current database
exec sp_dropuser @loginname
-- from server
exec sp_droplogin @loginname
-- to current server
exec sp_addlogin @loginname, @passwd
-- to current database
exec sp_adduser @loginname
-- roles to add the user to
exec sp_addrolemember db_datareader, @loginname
exec sp_addrolemember db_datawriter, @loginname
If the user didn’t exist in the database, or didn’t exist as a login on the server, you can get two errors like these: Read the rest of this entry »
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »
Posted by jpluimers on 2012/12/21
Interesting; is on my research list to see if the installation process gets easier and more standardized.
SQL Server FineBuild Introduction
FineBuild provides 1-click install and best-practice configuration of SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.
via: SQL Server FineBuild.
Posted in Database Development, Development, Power User, Reporting Services, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, Windows, Windows 7, Windows 8, Windows Server 2003, Windows Server 2003 R2, Windows Server 2008, Windows Server 2008 R2, Windows Vista, Windows XP | Leave a Comment »
Posted by jpluimers on 2012/11/29
Great post: SQL Server: JOIN vs IN vs EXISTS – the logical difference.
Be aware of the three valued logic when NULL gets involved.
EXIST comes closes to what you expect.
And be aware that with IN, you cannod pass one parameter containing more than 1 IN value: sql server 2005 – Passing multiple values for one SQL parameter – Stack Overflow..
Posted in Database Development, Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »