Solved

SQL Server 2005 programatically set Permissions in VB.Net

Posted on 2006-07-12
6
333 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now