Solved

Programmatically Setup SQL Permissions From Windows App?

Posted on 2007-09-27
1,565 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.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    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.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    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.

    856 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

    13 Experts available now in Live!

    Get 1:1 Help Now