Help me with CREATE LOGIN scripts in windows application ... (sql server 2008)

Question

Hi,

i'm developing a simple windows application (C#, WinForms, ADO. NET). Application should have a following functionality:

1. create a login and corresponding user in my database (database1);
2. add roles to new user (role names: rol1, rol2).
3. New login can (1) and (2) and so on.

How should I do it ? What I want:

1. script for creating an initial user which can do (1) and (2).

2. script for creating new login+new user+add new roles.

Answer 1

Here's a basic script for adding a new login, user and role:

-- Create login
if not exists (select * from sys.syslogins where name = N'TheLogin')
    CREATE LOGIN TheLogin WITH PASSWORD=N'ThePassword', 
        DEFAULT_DATABASE=TheDatabase, CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

-- Create user
use TheDatabase
if not exists (select * from sys.sysusers where name = N'TheLogin')
    CREATE USER TheLogin FOR LOGIN TheLogin
IF DATABASE_PRINCIPAL_ID('TheRole') IS NULL
    create role rol1
exec sp_addrolemember 'TheRole', 'TheLogin'

-- Grant rights
grant select on TheView to TheRole

A login is server-wide, and controls if you can log into the server. A user is database specific, and controls what rights you have in a database.