Solved

SQL Server 2005 programatically set Permissions in VB.Net

Posted on 2006-07-12
6
332 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

15 Experts available now in Live!

Get 1:1 Help Now