Creating Users w/T-SQL
-
In SS2K I'm able to create user accounts with the Enterprise Manger but I'm curious how you would do this using T-SQL. Also, could new users be created via parameters passed from a web page?
Thanks,
Brian
-
Keep in mind NOT to have your website run with sa-privilages !
but you'll need security-admin privileges
Keep in mind, one should prefer "least authorities" above "all privileges"
TEST IT -- TEST IT -- TEST IT -- TEST IT
create proc sp_createmynewuser
@UserNaam as Varchar(128)
, @UserPWD as varchar(128)
, @defaultDb as varchar(128)
, @Userdb as varchar(128)
as
begin
set nocount on
exec sp_addlogin @loginame = @UserNaam, @passwd = @UserPWD, @defdb = @defaultDb
-- log the info
-- print 'User [' + @UserNaam +'] created for server [' + @@servername +']'
-- adduser to DB
Select @SQLstmt = 'exec sp_grantdbaccess @loginame = ''' + @UserNaam + ''' , @name_in_db = '''
+ case when charindex('\',@UserNaam,1) > 1
then substring(@UserNaam,charindex('\',@UserNaam,1)+1,128)
else @UserNaam
end
+ ''' '
exec ( 'Use [' + @Userdb +'] ' + @SQLstmt )
-- allow db_reader
Select @SQLstmt = 'exec sp_addrolemember N''db_datareader'', N'''
+ case when charindex('\',@UserNaam,1) > 1
then substring(@UserNaam,charindex('\',@UserNaam,1)+1,128)
else @UserNaam
end
+ ''''
exec ( 'Use [' + @Userdb +'] ' + @SQLstmt )
end
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply