Logins, Users, and Roles - Getting Started

,

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!

Rate

Share

Share

Rate