Solved

SQL Server 2005 programatically set Permissions in VB.Net

Posted on 2006-07-12
6
344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

751 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