Solved

Programmatically Setup SQL Permissions From Windows App?

Posted on 2007-09-27
1,556 Views
Last Modified: 2012-08-13
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?

0
Question by:kvnsdr
    9 Comments
     
    LVL 6

    Expert Comment

    by:thuannguy
    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.
    0
     
    LVL 18

    Expert Comment

    by:Sham Haque
    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.
    0
     
    LVL 1

    Author Comment

    by:kvnsdr
    Do you have any SQL code example?
    0
     
    LVL 6

    Accepted Solution

    by:
    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]
    0
     
    LVL 1

    Author Comment

    by:kvnsdr
    I guess the xxx in Create Login doesn't matter too much?
    0
     
    LVL 1

    Author Comment

    by:kvnsdr
    Ooops, I meant:

    I guess the 'aaaa' in Create Login doesn't matter too much?
    0
     
    LVL 6

    Expert Comment

    by:thuannguy
    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...
    0
     
    LVL 1

    Author Comment

    by:kvnsdr
    Cool,

    Thank You
    0
     
    LVL 2

    Expert Comment

    by:navyjax2
    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.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Course: MongoDB Object-Document Mapper for NodeJS

    NodeJS (JavaScript on the server) is awesome, but some developers get confused about NoSQL when it comes to working in Node with MongoDB (NoSQL database). Do you need a better explanation of how to use Node.js with MongoDB? The most popular choice is the Mongoose library.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    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…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    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.

    680 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

    29 Experts available now in Live!

    Get 1:1 Help Now