Programmatically Setup SQL Permissions From Windows App?

kvnsdr
kvnsdr used Ask the Experts™
on
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?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Sham HaqueSenior SAP CRM Consultant

Commented:
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.

Author

Commented:
Do you have any SQL code example?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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]

Author

Commented:
I guess the xxx in Create Login doesn't matter too much?

Author

Commented:
Ooops, I meant:

I guess the 'aaaa' in Create Login doesn't matter too much?
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...

Author

Commented:
Cool,

Thank You

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial