Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Logins, Users, and Roles - Getting Started

By Andy Warren, (first published: 2003/02/19)

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!

Total article views: 35269 | Views in the last 30 days: 14
 
Related Articles
FORUM

Login gets deny permission

Login gets deny permission

ARTICLE

New SQL Server 2014 Permissions: CONNECT ANY DATABASE

CONNECT ANY DATABASE is one of three new permissions in SQL Server 2014 that can be granted to serve...

FORUM

permissions

transfer logins and permissions

FORUM

Generating SQL Logins\users and permissions for a database

Generating SQL Logins\users and permissions for a database

FORUM

Database Reader Permission

Query for Database Reader Permission to a Login

Tags
administration    
programming    
security    
sql server 7    
sql-dmo    
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones