Adding Users to SQL Server

[Jeff Carlin]

Does anyone have VB code that correctly adds a new user to an SQL Server database? I assume it has to use sp_addlogin and/or sp_adduser, but I'm not sure.

For example, I'd like to add user "myname" to a database so they can subsequesntly log in. Anyone know how to do this?

Also, if anyone can suggest a quick read on this subject, I'd appreciate it.

You can use the SQLDMO object to manipulate the server objects such as adding logins and assigning users to databases.
I don't have code to hand, but the online help is pretty good.

John.

[quick65]

Private Sub AddMyUser()

dim oSQLDMO as new SQLDMO.SQLServer
dim oDatabase as SQLDMO.database
dim oUser as SQLDMO.user
dim oLogin as SQLDMO.login

' Before you begin, you must be logged in as an
' SQLServer user with sysadmin rights

oSQLDMO.LoginSecure = False
oSQLDMO.Connect strSrv, strLogin, strPwd

' Are we truely a sysadmin?
If Not oSQLDMO.Issysadmin Then exit sub

' First, create a new sqldmo.login record using the
' desired username

set oLogin = new sqldmo.login
oLogin.Name = "MyUser"
oLogin.SetPassword = "MyPassword"
oSQLDMO.logins.add oLogin
set oLogin = Nothing

' Next, for each database that you want this login to
' have access to, create a user, set the login, set the
' role, and add it to the database. In my example, I
' want to add a user as an database owner.

for each oDatabase in oSQLDMO.databases
if oDatabase.Name = "MyDatabase" then
set oUser = new SQLDMO.User
oUser.Login = "MyUser"
oUser.Role = "db_owner"
oDatabase.Users.Add oUser
set oUser = Nothing
end if
next
set oDatabase = nothing

oSQLDMO.Disconnect

End Sub

[Div]

If you want to add NT accounts on the fly, I have code for that.

[GregC]

For adding users on the fly, will your code retrieve all available users on the network? We want to add all network users with NT Authentication to our new SQL Server 2000 without manually adding each user (> 100).

[Div]

Yes. But ONLY if you are not using active directory. I have the code to do EXACTLY what you want.

[GregC]

Awesome!
Can you send it to me?
[email protected]

[paulw]

Why don't you just grant access to the NT groups?

P.

[Div]

I don't know about any other cases, but in my case, I had this to consider --

-Person A is responsible for editing/deleting/adding users to the SQL Server
-Person A should only have/need the ability to do this on the SQL Server
-No additional rights should be granted either on the domain or the server
-Changes will have to be made on the fly

Considering this, the only way to achieve it would be to NOT use NT groups. Maybe there is an easier way. I don't know it if there is.