creating logins in stored procedure (yukon)

Hello all,
I'm using SQL Server 2005.
I want a normal user to be able to create new logins using a stored
procedure.
I've tried with create login like this:
create PROCEDURE [dbo].[test]
(
@.login varchar(15),
@.pass varchar(15)
)
AS
create login [@.login] WITH PASSWORD = [@.pass], DEFAULT_DATABASE = so
medb,
CHECK_EXPIRATION = OFF
but sql server complains about some syntax error near @.pass.
Then I tried using
EXEC sp_addlogin [@.login], [@.pass], somedb
and it works but this way a normal user doesn't have permission, so it isn't
useful for me
Any help appreciated, thanks!Try the follwing script to create the SP:
----
create PROCEDURE [dbo].[sp_createLogin]
(
@.login varchar(15),
@.pass varchar(15),
@.defDB varchar(15)
)
AS
declare @.createQuery as nvarchar(500)
set @.createQuery = 'create login ' + @.login + ' WITH PASSWORD = ''' + @.pass
+ ''', DEFAULT_DATABASE = ' + @.defDB + ', CHECK_EXPIRATION = OFF'
execute sp_executesql @.createQuery
----
use:
exec sp_createLogin 'log03','pass01', 'mytestdb'
- Ajey
"implacabile" <implacabile@.usa.net> wrote in message
news:uoa7AD0aFHA.348@.TK2MSFTNGP14.phx.gbl...
> Hello all,
> I'm using SQL Server 2005.
> I want a normal user to be able to create new logins using a stored
> procedure.
> I've tried with create login like this:
> create PROCEDURE [dbo].[test]
> (
> @.login varchar(15),
> @.pass varchar(15)
> )
> AS
> create login [@.login] WITH PASSWORD = [@.pass], DEFAULT_DATABASE =
somedb,
> CHECK_EXPIRATION = OFF
> but sql server complains about some syntax error near @.pass.
> Then I tried using
> EXEC sp_addlogin [@.login], [@.pass], somedb
> and it works but this way a normal user doesn't have permission, so it
isn't
> useful for me
> Any help appreciated, thanks!
>
>|||thanks for your effort, unfortunately this doesn't solve the problem:
since you are using execute I have to grant permission to create the login,
but I don't want to do it!
my hope was to write a sp to create logins without using exec, so that I
could grant only execute on that sp, and nothing else.
I'm starting to think that's impossibile to do this way...
well I'll grant securityadmin fixed role to the users... but I don't like
that.
thanks.
"Ajey" <ajey5@.hotmail.com> ha scritto nel messaggio
news:Oduh0I3aFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Try the follwing script to create the SP:
> ----
> create PROCEDURE [dbo].[sp_createLogin]
> (
> @.login varchar(15),
> @.pass varchar(15),
> @.defDB varchar(15)
> )
> AS
> declare @.createQuery as nvarchar(500)
> set @.createQuery = 'create login ' + @.login + ' WITH PASSWORD = ''' +
> @.pass
> + ''', DEFAULT_DATABASE = ' + @.defDB + ', CHECK_EXPIRATION = OFF'
> execute sp_executesql @.createQuery
> ----
> use:
> exec sp_createLogin 'log03','pass01', 'mytestdb'
> - Ajey
>
> "implacabile" <implacabile@.usa.net> wrote in message
> news:uoa7AD0aFHA.348@.TK2MSFTNGP14.phx.gbl...
> isn't
>|||I created a user 'UserForCreatingLogins' added to sysadmin server role &
securityadmin Db role on master.
created the below procedure WITH EXECUTE AS 'UserForCreatingLogins'
But still getting the error:
User does not have permission to perform this action.
With EXECUTE AS it should have worked.
- Ajey
"implacabile" <implacabile@.usa.net> wrote in message
news:eUSFtC4aFHA.228@.TK2MSFTNGP12.phx.gbl...
> thanks for your effort, unfortunately this doesn't solve the problem:
> since you are using execute I have to grant permission to create the
login,
> but I don't want to do it!
> my hope was to write a sp to create logins without using exec, so that I
> could grant only execute on that sp, and nothing else.
> I'm starting to think that's impossibile to do this way...
> well I'll grant securityadmin fixed role to the users... but I don't like
> that.
> thanks.
> "Ajey" <ajey5@.hotmail.com> ha scritto nel messaggio
> news:Oduh0I3aFHA.2420@.TK2MSFTNGP12.phx.gbl...
somedb,[vbcol=seagreen]
>|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:%23zPGU29aFHA.740@.tk2msftngp13.phx.gbl...
>I created a user 'UserForCreatingLogins' added to sysadmin server role &
> securityadmin Db role on master.
> created the below procedure WITH EXECUTE AS 'UserForCreatingLogins'
> But still getting the error:
> User does not have permission to perform this action.
> With EXECUTE AS it should have worked.
Impersonation does not work with dynamic SQL.
But I believe it is possible to do what you want to do.
Stored procedures that are created while SQL Server is configured to allow
direct writes to system tables retain that ability after the configuration
is changed back to normal.
So you could copy the source for sp_addlogin and use it to create your own
stored procedure, just remove the checks for role membership near the
beginning, make any other changes you need, and grant execute permissions to
that new stored proc. Just remember to set the option to allow changes to
system tables before you create the new procedure, and set it back when
you're done.
You still won't be able to use any dynamic SQL that does privileged things,
so you'll have to make it work with parameters, like sp_addlogin does.
-Mark

> - Ajey
> "implacabile" <implacabile@.usa.net> wrote in message
> news:eUSFtC4aFHA.228@.TK2MSFTNGP12.phx.gbl...
> login,
> somedb,
>|||> that new stored proc. Just remember to set the option to allow changes to
> system tables before you create the new procedure, and set it back when
how do I setup/remove that option?
thanks|||"implacabile" <implacabile@.usa.net> wrote in message
news:uetxBjBcFHA.2756@.tk2msftngp13.phx.gbl...
> how do I setup/remove that option?
> thanks
In EM, right-click the server opject, click Properties, click the Server
Settings tab, then set the checkbox labeled Allow modifications to be made
directly to system catalogs.
Be aware that in so doing, you take full responsibility for the integrity of
the system, so you'll want to be very careful.
-Mark|||"Mark J. McGinty" <mmcginty@.spamfromyou.com> ha scritto nel messaggio
news:%23xe%23xQKcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> "implacabile" <implacabile@.usa.net> wrote in message
> news:uetxBjBcFHA.2756@.tk2msftngp13.phx.gbl...
> In EM, right-click the server opject, click Properties, click the Server
> Settings tab, then set the checkbox labeled Allow modifications to be made
> directly to system catalogs.
> Be aware that in so doing, you take full responsibility for the integrity
> of the system, so you'll want to be very careful.
>
> -Mark
>
unfortunately I'm using sql server management studio from yukon, and I
cannot
find the option. however I think it should be possibile to change the
setting from
sql, maybe using sp_configure ?|||"implacabile" <implacabile@.usa.net> wrote in message
news:%232l9VqacFHA.2696@.TK2MSFTNGP09.phx.gbl...
> "Mark J. McGinty" <mmcginty@.spamfromyou.com> ha scritto nel messaggio
> news:%23xe%23xQKcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> unfortunately I'm using sql server management studio from yukon, and I
> cannot
> find the option. however I think it should be possibile to change the
> setting from
> sql, maybe using sp_configure ?
exec sp_configure 'allow updates', '1'|||> exec sp_configure 'allow updates', '1'
>
yes, I've tried that, then I've copied the src of sp_addlogin,
and tried it with a normal user, but it doesn't work: in sql server
2005 the sp creates dinamically a varchar with 'create login ....' and
then it exec it, so it doesn't manipulate directly the tables, so the
users need additional permissions (because of the exec).
I think I'll have to make the users members of securityadmin
role, or some other.