Critical Status - Database-Oriented Issues Plus

It seems that everything has a critical status, especially when it comes to databases and software development! Here's a place for listing issues and ideas relating to database and development issues. I'll list problems and ideas mainly related to SQL Server 2005. Post your thoughts, advice or complaints! Later this year will be the 25th anniversary of being in the trenches of the IT industry. I specialize in database applications development. I'd like to share my experiences with everyone and learn of those of others. You never stop learning.

Tuesday, April 21, 2009

EXTRACTING A DATABASE USER'S LOGIN NAME THATS NOT STORED IN THE SYSLOGIN TABLE

I'M WRITING SCRIPTS FOR HANDLING A DOMAIN ACCOUNT CHANGE OVER. DID YOU KNOW WHEN YOU ENTER A NEW DATABASE USER AND GIVE IT A DOMAIN ACCOUNT LOGIN NAME, THAT DOES NOT EXIST IN THE INSTANCE\SECURITY\USERS LIST, THAT IT DOES NOT REGISTER IT IN THE SYSLOGIN TABLE? SO IF YOU NEED TO PULL THAT LOGIN NAME, YOU CAN USE SUSER_SNAME() BY
PASSING THE .SYS.SYSUSER.SID VALUE FOR THE DATABASE USER ACCOUNT IN QUESTION. I ORIGINALLY THOUGHT I WAS GOING TO HAVE TO ACCESS ACTIVE DIRECTORY DATA THROUGH A SMALL VB APP. THE AD ADMIN GROUP WILL SURELY NOT GIVE THAT ACCESS TO ANYBODY AND CAN FORESEE LOTS OF POLITICS AND PAPERWORK INVOLVED IN EVEN A FAIR SIZED ORG OF SAY A FEW HUNDRED PEOPLE! HOPE THIS SAVES YOU ALL SOME TIME!

Friday, April 10, 2009

How to change databases within a stored procedure or function

This one was a royal pain in the ASS!!!! Not fully correct info on the web or people saying it CAN'T be done. NEVER NEVER NEVER tell me it CAN'T be done!!!!

here is the revelant code from an SP for truncing logs. rest your weary head!!!

USE [RIXTOOLBOX]
ALTER PROCEDURE [dbo].[spTruncateLogs]

@DBName SYSNAME

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @command varchar(500), @LogFile VARCHAR(500), @LOGSTR VARCHAR(10);
SET @LOGSTR = '%LOG%'
SET @command = 'DECLARE c CURSOR FOR
SELECT
NAME
FROM
[' + @DBName + '].SYS.SYSFILES
WHERE
name LIKE ''' + @LOGSTR + ''';'

EXEC (@COMMAND)
OPEN c;
FETCH NEXT FROM c INTO @LogFile;

SET @COMMAND =
'ALTER DATABASE "' + @DBName + '" SET RECOVERY SIMPLE';
EXEC (@COMMAND);
CHECKPOINT

BACKUP LOG @DBName WITH NO_LOG

SET @command =
'
USE ' + @DBName + ';
DBCC SHRINKFILE (' + @LogFile + ', 1);
';

EXEC (@COMMAND);
CLOSE c
DEALLOCATE c

SET @COMMAND =
'ALTER DATABASE "' + @DBName + '" SET RECOVERY FULL';
EXEC (@COMMAND);

SET @command =
'SELECT
*
FROM
[' + @DBName + '].SYS.SYSFILES
WHERE
name LIKE ''' + @LOGSTR + ''';'

EXEC (@COMMAND);

END