Solved

Programmatically Setup SQL Permissions From Windows App?

Posted on 2007-09-27
9
1,632 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
Comment
Question by:kvnsdr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 6

Expert Comment

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

Expert Comment

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

Author Comment

by:kvnsdr
ID: 19972080
Do you have any SQL code example?
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 6

Accepted Solution

by:
thuannguy earned 500 total points
ID: 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]
0
 
LVL 1

Author Comment

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

Author Comment

by:kvnsdr
ID: 19975432
Ooops, I meant:

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

Expert Comment

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

Author Comment

by:kvnsdr
ID: 19980179
Cool,

Thank You
0
 
LVL 2

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

710 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