Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2005 programatically set Permissions in VB.Net

Posted on 2006-07-12
6
Medium Priority
?
358 Views
Last Modified: 2008-01-09
I have an application that Allows the End user to create SQL 2005 logins and users, however I have been unable to find any references online or otherwise as to how I can programatically either set permissions or assign Server or Database Roles. I am Using SQL 2005, VS2005-VB.Net. Using SMO, I'm sure there is a way, but I'm now against a deadline so now I would also accept any other way to perform this operation. Please Help!

This is how I'm creating the Login/Users:

Private Sub AddUserButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddUserButton.Click
        Dim db As Database
        Dim usr As User
        Dim Lgn As Login
        Dim UserListViewItem As ListViewItem

        Try
            If PasswordTextBox.Text = ConfirmTextBox.Text Then
                db = CType(DatabasesComboBox.SelectedItem, Database)
                ' Create the Database Login
                Lgn = New Login(mSqlServerSelection, UserNameTextBox.Text)
                Lgn.LoginType = LoginType.SqlLogin
                Lgn.DefaultDatabase = db.Name
                Lgn.Create(PasswordTextBox.Text)
               
                ' Create the user object
                usr = New User(db, UserNameTextBox.Text)
                usr.Login = UserNameTextBox.Text
                usr.Create()

                ShowUsers()

                ' Select the user we just created and make sure it's viewable
                UserListViewItem = UsersListView.FindItemWithText(UserNameTextBox.Text)
                UserListViewItem.Selected = True
                UserListViewItem.EnsureVisible()
            Else
                MsgBox("Password and Confirm Password do not match, or Password is blank. Please try again.", MsgBoxStyle.Exclamation, "Retype Pasword")
            End If
        Catch ex As SmoException
            Dim emb As New ExceptionMessageBox(ex)
            emb.Show(Me)
        End Try
    End Sub
0
Comment
Question by:webdev007
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Einstine98
ID: 17093928
why don't you use a TSQL statemnt to do the job?

Create User
OR
Grant xRight to User
?
0
 

Author Comment

by:webdev007
ID: 17094191
I have just been trying to dig into using the latest techniques with SQL 2005 and SMO. I don't have much experience with TSQL statements, but if you have any example code that would helpful as I said I am now desperate for any solution.
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17094306
to create a login (server wide)
CREATE LOGIN <UserLoginName>
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';

To Create a User on a database for that login

USE AdventureWorks;
CREATE USER <UserLoginName> For Login <UserLoginName>

To
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:webdev007
ID: 17094409
Ok. Then how would I assign say a Database role with permission for db_accessadmin, db_datareader, and db_datawriter. And also a Server Role with SecurityAdmin?
0
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 1500 total points
ID: 17094486
sp_addrole [ @rolename = ] 'role' [ , [ @ownername = ] 'owner' ]
sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'
0
 

Author Comment

by:webdev007
ID: 17094566
Ok thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question