Adding Logins, Users, and Roles using SQL-DMO
A few weeks ago we had a request in our DMO
discussion form for some help with adding logins and users to a database
using DMO. I posted the code sample below as part of that answer. But that
discussion really brought two good (though perhaps obvious) points - you have to
know how SQL works to use DMO effectively, and I think you need to consider
which is the right tool - DMO, T-SQL, or Enterprise Manager. In this article I'd
like to talk for a few minutes about how logins and users work, then move into a
discussion of which tool might be best for adding logins and users.
Logins exist in the master database, stored in sysxlogins, though normally
you'll use the easier to read view called syslogins. Think of a login as
something that allows a user to connect to the server, but not to access any
particular database. You can have a 'SQL' login, which is a login name plus a
password, or you can use NT/trusted security, where you add either a NT user or
group name as a login. Without diving deeply into which you might want to use,
just keep in mind that you have to have a login to connect to the server
and that it is stored in the master database.
Creating a user in a database gives a login permission to access that
database. If you use Enterprise Manager, you'll see that the drop down list for
adding users consists of all the logins that exist on that server. Users are
stored in that database, not in the master database. This makes sense if
you think about what happens when you detach the database - the users stay
inside the mdf. When you reattach the mdf to a different server, the users are
still there, though the logins may not be (see the articles Moving
Logins - A Gotcha! and Fixing
Broken Logins and Transferring Passwords) for more information on how to
handle the resulting problems.
You normally handle permissions by creating roles - think of an NT group. You
should always assign permissions to a role, not to a user (see Using
the Public Role to Manage Permissions and Fixed
Database Roles for more information about roles).
I know that's a really quick overview, but in condensed form, here is what we
need to do when we have a new database and we want to set up the first user:
- Either re-use an existing login or create a new login.
- Add a role to our database (you could skip this step, but you'll be
sorry!).
- Add the user to the database.
- Add the user to the role you just created.
- Assign permissions to the role. We're not going to spend time on this
today, but please do carefully consider how and why you grant permissions.
Now let's talk about which tool to use. If you're just doing this
occasionally, I recommend Enterprise Manager. I'm going to include some screen
shots here to show you how it would be done:
Add the Login
Add A Role
Add a User
..and Make a Member of the Role Just Created
Assign Permissions to the Role
If you're a power user (or maybe just too stubborn to use a GUI), then you
could use Query Analyzer (or anything else that would connect to the database,
including DAO or ADO). If you know the syntax, you'll see that it's very
straight forward:
| sp_addlogin 'SSC_TEST_USER','test' go use Northwind go sp_addrole 'SSC_BETA' go sp_grantdbaccess 'SSC_TEST_USER' go sp_addrolemember 'SSC_BETA','SSC_TEST_USER' go grant select, insert, update, delete on [Alphabetical list of products] to SSC_BETA go |
Finally, if you're a programmer used to working with objects, DMO offers it's
own method of accomplishing these tasks:
| 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 |
Which is better? They all get the job done,
I think much of it depends on your own preferences and experience. I like
Enterprise Manager for day to day minor changes. If I've got to set something up
that will be used a lot, I usually write some T-SQL and save as a script I can
reuse (even if I'll have to search and replace a parameter or two to reuse it).
If I need to hide the complexity and make it accessible to a user or junior
developer/DBA, I think DMO is a great choice, though you could just as easily
use T-SQL and automate the search/replace needed to make it reusable.
Got questions? Comments? Click the 'Your
Opinion' tab below and let me know. A question from another reader led to this
article, maybe your comments will generate another one!
