Programmatically Setup SQL Permissions From Windows App?

    Question by:
    On

    Topics:

    I'd link to setup SQL Database users and permissions from a Windows App (VS2005 C#).

    I currently have to Install and set SQLExpress 2005 permissions using the (free) Management Studio Express on every computer I install my App.

    Would be nice to have a pop-up window (secured) within my Win App that I could simply set new database users and permissions.

    I suppose someone else has already done this, but I didn't find anything on the Internet.

    I currenly open the Management Studio -> Security -> Logins -> New Login -> <create user then set 'User Mappings'...

    I'm using 'Integrated Security' and realize that I need to access the OS (XP-Pro) users and groups, don't know if that's possible.

    Q. How can I programmatically (code)  setup Users, Groups, Permissions on SQLExpress 2005?

    Good Question?
    0
     

    ?

    The member who asked this question verified this comment provided the solution that solved their problem.

    Accepted Solution on 2007-09-27 at 09:04:39ID: 19972384

    Some example script:
    -- creat LOGIN
    CREATE LOGIN [xxx] WITH PASSWORD=N'xxxxx', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'xxx', @rolename = N'rolename'  -- e.g. sysadmin

    use [your database]
    CREATE USER [aaaa] FOR LOGIN [xxx] WITH DEFAULT_SCHEMA=[dbo]

    exec sp_addrolemember 'db_owner', [aaaa]

    verified

    answer

    Enjoy your free answer!

    Join now to get more tech resources.

    Become a member

    Comments

     
    thuannguy

    Expert Comment

    2007-09-27 at 08:22:09ID: 19971986
    Hi,
    In short, SQL Server 2005 provides all the function you need to create a LOGIN, mapping user, grant permission. You can find them in the SQL book online. And then you can write a small application to execute these functions. Of course your application needs to connect to the sql server using a powerful login, e.g. SA :P.
    Good Comment?
    0
     
    Sham Haque

    Expert Comment

    2007-09-27 at 08:31:53ID: 19972058
    my advice would be to create some Active Directory groups, assign those fixed roles in the db, with specific permissions, and have your app add users to those AD groups.

    this worked very well in my previous company, and makes admin of users much simpler and gives full transparency to non-SQL admins as to permissions users have.
    Good Comment?
    0
     
    kvnsdr

    Author Comment

    2007-09-27 at 08:33:42ID: 19972080
    Do you have any SQL code example?
    Good Comment?
    0
      

    Accepted Solution

    2007-09-27 at 09:04:39ID: 19972384Best
    thuannguy earned 500 total points
    Some example script:
    -- creat LOGIN
    CREATE LOGIN [xxx] WITH PASSWORD=N'xxxxx', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'xxx', @rolename = N'rolename'  -- e.g. sysadmin

    use [your database]
    CREATE USER [aaaa] FOR LOGIN [xxx] WITH DEFAULT_SCHEMA=[dbo]

    exec sp_addrolemember 'db_owner', [aaaa]
    Good Solution?
    0
     
    kvnsdr

    Author Comment

    2007-09-27 at 16:08:42ID: 19975425
    I guess the xxx in Create Login doesn't matter too much?
    Good Comment?
    0
     
    kvnsdr

    Author Comment

    2007-09-27 at 16:10:08ID: 19975432
    Ooops, I meant:

    I guess the 'aaaa' in Create Login doesn't matter too much?
    Good Comment?
    0
     
    thuannguy

    Expert Comment

    2007-09-28 at 08:42:23ID: 19979503
    Hi,
    In my example, [xxx] is the LOGIN name, and [aaaa] is the name of a database user. I should use more meaningful names :(. You can use any name you like for the database username, except built-in names, such as sa, dbo...
    Good Comment?
    0
     
    kvnsdr

    Author Comment

    2007-09-28 at 10:05:25ID: 19980179
    Cool,

    Thank You
    Good Comment?
    0
     
    navyjax2

    Expert Comment

    2008-10-29 at 12:29:57ID: 22834703
    The "CHECK_EXPIRATION=OFF, CHECK_POLICY=ON" part isn't required, these are set that way by default.  See http://msdn.microsoft.com/en-us/library/ms189751.aspx.

    If you are adding a Windows account, the user name ("[xxx]") becomes [DOMAIN\username], and then you have to specify "FROM Windows".  Here's my variant from the code from above:

    CREATE LOGIN [DOMAIN\username] FROM WINDOWS WITH DEFAULT_DATABASE=[myDatabase], DEFAULT_LANGUAGE=[us_english]
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N' DOMAIN\DOMAIN\username', @rolename = N'sysadmin'

    USE [myDatabase]
    CREATE USER [DOMAIN\username] FOR LOGIN [DOMAIN\username] WITH DEFAULT_SCHEMA=[dbo]

    EXEC sp_addrolemember 'db_owner', [DOMAIN\username]

    I used this and it works great.
    Good Comment?
    0

    Featured Post

    Course: AWS Professional Certification

    These classes are designed to help you pass the AWS Certified Solutions Architect – Associate, AWS Certified Developer, and AWS Certified SysOps Administrator Associate exams—all essential to a career as an AWS professional.

    Ask Your Tech Question. Get Expert Solutions.We will email you as soon as we have your answer.

    We will never share this with anyone.

    Select topics

    You may select up to five topics.

    Top Expert Contributor

    Essential articles and videos from the Experts

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    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.
    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…

    More valuable questions with Expert answers

    Question: I'm trying to understand the following code. Not sure I understand this peace of code. (CODE) (CODE)

    Answer: It looks like the modulo is used to cut down the random number to the number of characters length, which in this case is 9. Is this just to create some extra randomness? This is code I've used before, it might suit your purposes better. (CODE)

    Question: I am trying to figure out how to use the replace function by using a pattern. There may be another function that can do what I am looking for. In SQL I have the following string as an example: (CODE) What I would like to do is remove the...

    Answer: You will need to use a function since you need the replace to occur multiple times per string. Below is a sample function that you can adjust to fit your needs by simply changing the delimiter characters in the @Start and @End assignments. (CODE)

    Question: Using LanSweeper v6022 in an office with Windows 10 computers, I would like to accomplish the following: 1. Generate a report to find if the built-in local administrator account is active. 2. Generate a report to find if antivirus is...

    Answer: Lansweeper uses SQL compact / SQL server. and pretty much most of your requirements can be driven from web console. Please go through Lansweeper support page below to understand step by step process. Antivirus :...

    Question: Hi I'm trying to fix drilldown reports open connection failed error , this only appears on aspx.vb postbacks, either through page navigation or through drill down. Appreciate if you could help me resolve this issue. Regards, Member_2_7969420

    Answer: This is a common problem with embedding Crystal Reports in ASP pages. Essentially, the authentication details are reset on a postback and need to be reassigned in the Page_Init handler. First, import these: (CODE) Then, in the Page_Init...

    201607-LO-Qu-086

    Extend your technology team with the Experts Exchange community.

    — trusted by —

    Who answers my questions?Our community has technology experts around the world.

    Fernando Soto

    5,180

    Solutions

    Expert in:

    • .NET Programming
    • C#
    • Visual Basic.NET
    • ASP.NET
    • Editors IDEs

    it_saige

    1,274

    Solutions

    Expert in:

    • .NET Programming
    • C#
    • Visual Basic.NET
    • ASP.NET
    • Active Directory

    Paweł

    70

    Solutions

    Prakash Samariya

    69

    Solutions

    Karrtik Iyer

    293

    Solutions

    Expert in:

    • .NET Programming
    • C#
    • Visual Basic.NET
    • ASP.NET
    • C++

    jorge_toriz

    341

    Solutions

    Expert in:

    • ASP.NET
    • .NET Programming
    • C#
    • MS SQL Server
    • MS SQL Server 2005

    mrwad99

    659

    Solutions

    Expert in:

    • C++
    • Editors IDEs
    • MS Development-Other
    • Programming-Other
    • Proofreading

    Miguel Oz

    2

    Articles

    1,524

    Solutions

    Expert in:

    • .NET Programming
    • ASP.NET
    • C#
    • Visual Basic.NET
    • MS Development-Other

    funwithdotnet

    167

    Solutions

    Expert in:

    • .NET Programming
    • ASP.NET
    • Visual Basic.NET

    RELATED TOPICS view all topics

    1. .NET Programming
      (130,871)
    2. ASP.NET
      (122,580)
    3. Visual Basic.NET
      (91,309)
    4. MS Development-Other
      (47,891)
    5. Editors IDEs
      (24,919)
    6. Programming-Other
      (51,584)
    7. MS SQL Server
      (155,365)
    8. JavaScript
      (118,144)
    9. XML
      (19,816)
    10. WCF
      (4,124)
    Receive Monthly Emails of Tech News and Trends from Experts Exchange