MS SQL Server : Programatically add DB login

Merrion

OK - I have a database with a table [User permissions] which controls what the user of the print monitoring application is allowed to do (i.e. see only own documewnts, or all etc.)

When a user submits a print job, if they have not been added to this table then they are added with the minimum permissions. I would like this step to also set up their database log in so have set a trigger thus:-

/* ---------------------------------------------
 * Add an NT login to this database from the 
 * NT Domain.
 */
CREATE TRIGGER User_Permissions_Insert
  ON dbo.[User Permissions]
 FOR INSERT
AS
	
	DECLARE @DOMAIN VARCHAR(200),
	        @FULL_USERNAME VARCHAR(255)
	
	
       SELECT @DOMAIN = ISNULL([Parameter Value],'')
	  FROM dbo.[PUMA Parameters]
	 WHERE [Parameter Name] = 'NT Domain'
	
	IF @DOMAIN = ''
	   SELECT @FULL_USERNAME = [User Name]
             FROM inserted
	ELSE
	   SELECT @FULL_USERNAME = LTRIM(@domain) + '\' + Ltrim([User Name])
             FROM inserted


        -- Add the NT login to the database logins list
        EXECUTE sp_grantlogin @FULL_USERNAME

        -- And grant them access to the current database
        EXECUTE sp_grantdbaccess @FULL_USERNAME


	RETURN 

However when I cause the trigger to fire I get the error message:-
The procedure 'sp_grantlogin' cannot be executed within a transaction.

Any ideas how I would go about doing this, or is a trigger the wrong solution for this case?

bonkey

Merrion wrote:

However when I cause the trigger to fire I get the error message:-
The procedure 'sp_grantlogin' cannot be executed within a transaction.

Any ideas how I would go about doing this, or is a trigger the wrong solution for this case?

I would hazard a guess that a trigger is the wrong solution.

Each individual SQL statement is considered as a transaction in its own right. The trigger fires inside that transaction, which is what gives a trigger the ability to roll back the data-modification it is responding to should it wish to.

You probably want to remove all "direct" access to the table, and create stored procedures to do your inserts (etc.), and then code the logic for the user-addition into the stored procedure.

Incidentally, I'm not sure of how you expect this to work. If the user doesn't already exist as a login, then obviously they are not performing the insert into this table themselves (or are doing so via some shared login). However, if this is the case...why does the user need an individual login created for themselves anyway?

Alternate suggestion: consider looking at using NT Group logins. in MSSQL2000 (and 7? Can't remember) you can grant permission to an entire NT Group rather than user-by-user. Do this to whichever group you use to control "access to the printer" permissions.....and your login problem is solved automagically.

jc

Merrion

Alternate suggestion: consider looking at using NT Group logins. in MSSQL2000 (and 7? Can't remember) you can grant permission to an entire NT Group rather than user-by-user. Do this to whichever group you use to control "access to the printer" permissions.....and your login problem is solved automagically.


Yup - I think that is the way to go. To explain how I find myself in this bizarre situation: a system service on the print server (with its own log in) is adding data to a database, including the user name of the user that has submitted a print. The user needs to be able to log in from their machine and see what they have printed so needs a log in in that database.

bonkey

Merrion wrote:

The user needs to be able to log in from their machine and see what they have printed so needs a log in in that database.

Go with NT Groups, or (as an alternative) with a single SQL-Authentication-based login which has its permissions fully locked down so that it can't possibly do anything other than read the info you want it to.

There's pros and cons to both approaches. If it was me, and I had time, I'd implement both with a configuration option to swap between them.

jc

Zaph0d

How do the users connect to the database? Is it done via a webserver or other interface system? Is there an installed client on the user machine that connects directly to the database via odbc?

Nearly all applications I have worked on, that use SQL Server as a component, only use system logins rather than one login per user. The identity of the end user is passed to the database by the requesting system and the database uses internal, custom designed permission tables to determine which actions and which data are authorized for the user in question.

Merrion

It's a client application (in VB.NET) connecting via OLEDB.
Security has to be on a per-NT-user basis as most users are only allowed to see the documents that they have printed...and this app does not have a seperate log in screen (nor would I want that).