Database

Mycroft Holmes wrote:

<layer>almost no one uses it. <layer>The normal design is for the application to have a SQL userid/password that is authorised to access the parts of the database required

"Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This dramatically reduces the chances of error or oversight.

For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you use Windows authentication mode, revoking that user’s access takes place automatically when you disable or remove the DBA’s Active Directory account. If you use mixed authentication mode, you not only need to disable the DBA’s Windows account, but you also need to comb through the local user listings on each database server to ensure that no local accounts exist where the DBA may know the password. That’s a lot of work! "

J4amieC wrote:

very specific situation

Mycroft Holmes wrote:

There is a reason you rarely see question about integrated security in a database forum, almost no one uses it

Mycroft Holmes wrote:

I have never seen anything but a test/toy application use integrated security.

Bastard Programmer from Hell

Eddy Vluggen wrote:

I don't like adding passwords to authenticate a user that's already logged
in.

Eddy Vluggen wrote:

Therefore, it doesn't exist

Mycroft Holmes wrote:

Why on earth would you need to do that

Mycroft Holmes wrote:

Nah probably just not commonly used!

Mycroft Holmes wrote:

Actually that might be an interesting survey.

Bastard Programmer from Hell

Eddy Vluggen wrote:

Your users need access to an additional secret

DROP DATABASE insert_name_of_database_here

create table Campaign
(
    CampaignName varchar(50) Constraint PK_Campaign PRIMARY KEY,
    CampaignMission varchar(max)
)
insert into Campaign(CampaignName, CampaignMission) values('StarTrek','5 year mission to
 explore new worlds. To seek out new life and new civilization');

create table Operations
(
    OperationCode VARCHAR(30) CONSTRAINT PK_Operations PRIMARY KEY
)
insert into Operations(OperationCode) values('EngageWarp');
insert into Operations(OperationCode) values('ComeInPeace');
insert into Operations(OperationCode) values('ShootToKill');

create table CampaignOperations
(
   CampaignName VARCHAR(50),
   OperationCode Varchar(30),
   [Procedure] VarChar(max),
   CONSTRAINT FK_Campaign_CampaignOperations FOREIGN KEY (CampaignName) REFERENCES Campaigns(CampaignName),
   CONSTRAINT FK_Operations_CampaignOperations FOREIGN KEY (OperationCode) REFERENCES Operations(OperationCode),
   CONSTRAINT PK_CampaignOperations PRIMARY KEY (CampaignName, OperationCode)
)
insert into CampaignOperations('StarTrek','EngageWarp', 'Reporting.StarTrekWarpAlgorithm');
insert into CampaignOperations('StarTrek','ComeInPeace', 'Reporting.StarTrekConTheLocals');
insert into CampaignOperations('StarTrek','ShootToKill', 'Reporting.StarTrekTakeMoneyAndWomens');

CREATE PROCEDURE ExecuteCampaignOperationLogic
(
	@CampaignName varchar(50),
	@OperationCode varchar(10)
)
AS
BEGIN
	DECLARE @PROCEDURE varchar(100)
	 
	
	SELECT	@PROCEDURE = [CampaignOperations].[Procedure]
	FROM [CampaignOperations]
		WHERE CampaignName = @CampaignName
		  and OperationCode = @OperationCode

	CREATE TABLE #foobar
	(
		foo varchar(10),
		bar varchar(10)
	)
	INSERT INTO #foobar (foo, bar) exec @PROCEDURE
        SELECT * from #foobar
END