Sunday, March 25, 2012

Creating user from application

I'm trying to create a user from a powerbuilder application through powerscript.
I'm trying to create first the login using sp_addlogin
but I'm getting the following error
[microsoft][odbc for sql server]the procedure sp_addlogin cannot be executed in transaction.

Plz does someone know what should I do?CREATE Procedure CreareUser (@.UserName varchar(30), @.UserPass varchar(30), @.DBName varchar(30),@.DreptScriere bit)

as

if exists (select * from master.dbo.syslogins where loginname=@.UserName)

exec sp_dropuser @.UserName
exec sp_droplogin @.UserName


if not exists (select * from master.dbo.syslogins where loginname = @.UserName)
BEGIN

EXEC sp_addlogin @.UserName, @.UserPass, @.DBName
EXEC sp_defaultdb @.UserName,@.DBName

if @.DreptScriere=0
BEGIN
EXEC sp_adduser @.UserName,@.UserName,
'db_denydatawriter'
EXEC sp_changegroup 'db_owner' ,@.UserName
EXEC sp_addrolemember 'db_denydatawriter' ,@.UserName
END
else
BEGIN
EXEC sp_adduser @.UserName,@.UserName,'db_owner'
END

EXEC sp_grantdbaccess @.UserName

END

No comments:

Post a Comment