September 14, 2001 at 11:47 am
How can I create a new login and assign an database to it using SQL-DMO.
I am finding that confusing.. i referred to the Online Books, but I am finding terms confusing... login, databaseuser, user... whats the difference between all of these.
What is my aim? I am creating a web front-end to create new databases. I give my db name and the password so my asp's should create a database by that name and also the username by the same name with the password entered by the user.
Also how can I assign roles to the user. From where can I find the values of each role that I have to assign.
Please guide me thru this.
Thankx
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
September 14, 2001 at 5:35 pm
Paras,
I've put together some sample code for you, but if I can offer some advice - until you understand the difference between logins, users, and roles, you're headed for trouble. Those of us who participate in the discussion area will be glad to help you on specific questions, but full scale tutoring is beyond the scope of what we can do here. In addition, my preference is that anyone with a question try to solve it first - and if you can't get it working, post what you tried.
Andy
Sub Test()
'9/14/01 Andy Warren
' Shows how to add a login, user, and a role using SQL-DMO. Using VB, set a
'reference to Microsoft SQLDMO. If you use VBScript, you have to remove the
'type declarations from the DIM's and use CreateObject instead of new.
Dim oServer As SQLDMO.SQLServer
Dim oLogin As SQLDMO.Login
Dim oRole As SQLDMO.DatabaseRole
Dim oUser As SQLDMO.User
'get a server object using a trusted connection
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect
End With
'create a login object and populate it
Set oLogin = New SQLDMO.Login
With oLogin
.Name = "SSC_TEST_USER"
.SetPassword "", "SSC"
.Database = "master"
End With
'add it the server
oServer.Logins.Add oLogin
'just to show how to do it, we'll add a role here, but not add the user
Set oRole = New SQLDMO.DatabaseRole
With oRole
.Name = "SSC_BETA"
End With
'add the role to the database - Northwind for this example
oServer.Databases("Northwind").DatabaseRoles.Add oRole
'if you didnt add the login as a database user, have to do that first,
'could just set it as default database when adding the login if you wanted,
'but they have to be auth in the db before you can add to a role
Set oUser = New SQLDMO.User
With oUser
.Login = "SSC_TEST_USER"
End With
oServer.Databases("Northwind").Users.Add oUser
'add the login we added earlier to this role, this is one method
oServer.Databases("Northwind").DatabaseRoles("SSC_BETA").AddMember "SSC_TEST_USER"
'clean up
Set oRole = Nothing
Set oLogin = Nothing
oServer.DisConnect
Set oServer = Nothing
End Sub
September 15, 2001 at 3:22 am
Which book is good to refer for SQLDMO. I am not able to find a good book to refer. I am relying on Online Books which has been bundled with SQL Server 7. But it does not explain me everything. Its just a kinda reference material. I have been referring to Orelly's TSQL Programming. Its a very nice book but DMO is not within the scope of that book.
quote:
Paras,I've put together some sample code for you, but if I can offer some advice - until you understand the difference between logins, users, and roles, you're headed for trouble. Those of us who participate in the discussion area will be glad to help you on specific questions, but full scale tutoring is beyond the scope of what we can do here. In addition, my preference is that anyone with a question try to solve it first - and if you can't get it working, post what you tried.
Andy
Sub Test()
'9/14/01 Andy Warren
' Shows how to add a login, user, and a role using SQL-DMO. Using VB, set a
'reference to Microsoft SQLDMO. If you use VBScript, you have to remove the
'type declarations from the DIM's and use CreateObject instead of new.
Dim oServer As SQLDMO.SQLServer
Dim oLogin As SQLDMO.Login
Dim oRole As SQLDMO.DatabaseRole
Dim oUser As SQLDMO.User
'get a server object using a trusted connection
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect
End With
'create a login object and populate it
Set oLogin = New SQLDMO.Login
With oLogin
.Name = "SSC_TEST_USER"
.SetPassword "", "SSC"
.Database = "master"
End With
'add it the server
oServer.Logins.Add oLogin
'just to show how to do it, we'll add a role here, but not add the user
Set oRole = New SQLDMO.DatabaseRole
With oRole
.Name = "SSC_BETA"
End With
'add the role to the database - Northwind for this example
oServer.Databases("Northwind").DatabaseRoles.Add oRole
'if you didnt add the login as a database user, have to do that first,
'could just set it as default database when adding the login if you wanted,
'but they have to be auth in the db before you can add to a role
Set oUser = New SQLDMO.User
With oUser
.Login = "SSC_TEST_USER"
End With
oServer.Databases("Northwind").Users.Add oUser
'add the login we added earlier to this role, this is one method
oServer.Databases("Northwind").DatabaseRoles("SSC_BETA").AddMember "SSC_TEST_USER"
'clean up
Set oRole = Nothing
Set oLogin = Nothing
oServer.DisConnect
Set oServer = Nothing
End Sub
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
September 15, 2001 at 6:43 am
So far I have not seen any great books on DMO. This one is ok:
http://www1.fatbrain.com/asp/bookinfo/bookinfo.asp?theisbn=1861002807&vm=
MSDN had a pretty good article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmag00/html/sqldmo0501.asp
And I also have several articles right here on the site about using DMO. DMO itself is pretty easy to use, though I agree that more sample code would be nice in BOL. The key to using DMO is having a good understanding of what you're trying to accomplish (how would you do it in Enterprise Manager) and a good understanding of objects, collections, and VB in general. If you don't understand objects, you're better off using stored procs. Compared to DMO, I'd say adding a login in TSQL is very easy - just execute sp_addlogin.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply