Solved

SQL Server 2005 programatically set Permissions in VB.Net

Posted on 2006-07-12
6
342 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

830 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