Solved

Programmatically Setup SQL Permissions From Windows App?

Posted on 2007-09-27
9
1,600 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

770 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