add new user in SQL server

[Jose Perdigao]

How can I add new user in SQL server through Access ADP?
User name should be the same name of windows login and the password should
be saved.
Is it possible?
Thanks

José Perdigão

 

[Jose Perdigao]

I'm using Access 2003 and SQL Server 2000

 

[Sylvain Lafontaine]

Well, you can use SQL-DMO or T-SQL to add a new user to a SQL-Server
database; however, this require that you can open a connection with
SQL-Server using some other credentials and I'm not sure this is what you
want.

 

[dbahooker]

syntax = 'create user username'

check it out in SQL books online; the best resource you can buy for
writing TSQL
best part is; it's free

(i would post it here; but i dont have it on this box right now-- 1st
day of new contract)

 

[Jose Perdigao]

Do you have any example or do you know any site withs examples about SQL-DMO
or T-SQL?

What I want is:
At the moment, when someone open access project and if his login is not
added in sql server, he can't do nothing. So I would like to add new users to
sql server from access project.

 

[dbahooker]

well gosh i think that i was thinking of this in SQL 2005 not in 2000
lol

now we're getting somewhere

yeah i'll find something soon

 

[dbahooker]

it's talking about a sproc named sp_adduser

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec01.mspx

Database administrators working with Transact-SQL should familiarize
themselves with the Books Online entries for these security-related
stored procedures:

sp_addalias
sp_droprole

sp_addapprole
sp_droprolemember

sp_addgroup
sp_dropserver

sp_addlinkedsrvlogin
sp_dropsrvrolemember

sp_addlogin
sp_dropuser

sp_addremotelogin
sp_grantdbaccess

sp_addrole
sp_grantlogin

sp_addrolemember
sp_helpdbfixedrole

sp_addserver
sp_helpgroup

sp_addsrvrolemember
sp_helplinkedsrvlogin

sp_adduser
sp_helplogins

sp_approlepassword
sp_helpntgroup

sp_change_users_login
sp_helpremotelogin

sp_changedbowner
sp_helprole

sp_changegroup
sp_helprolemember

sp_changeobjectowner
sp_helprotect

sp_dbfixedrolepermission
sp_helpsrvrole

sp_defaultdb
sp_helpsrvrolemember

sp_defaultlanguage
sp_helpuser

sp_denylogin
sp_password

sp_dropalias
sp_remoteoption

sp_dropapprole
sp_revokedbaccess

sp_dropgroup
sp_revokelogin

sp_droplinkedsrvlogin
sp_setapprole

sp_droplogin
sp_srvrolepermission

sp_dropremotelogin
sp_validatelogins

 

[Sylvain Lafontaine]

Sorry with the dealy, I had some problems elsewhere.

For your question, making a Google search with the words "sqldmo user"
should give you a lot of examples of doing this. However, in all these
cases, this require that your ADP project knows the credential for the sa
account or the owner account of the database; something that might not be
possible for security reasons. Usually, the administrative task of adding a
new account is done directly on the SQL-Server and trying to do this
directly on the user side will be a security nightmare.

Surely, you don't want that anyone getting access to the ADP file can open
an account on the SQL-Server without your consent?