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

Question

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

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

How should I do it ? What I want:

script for creating an initial user which can do (1) and (2).
script for creating new login+new user+add new roles.

Andomar · Accepted Answer · 2010-09-17 12:23:10Z

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.

answered Sep 17, 2010 at 12:23

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related