sql server login

Question

hi,

im writing a vb.net application which connect to a db using integrated security. however, i wanted to implement a login screen so that the user enters their NT user name and password for initially connecting. this is because our information governance team want to verify the someone else is not using a machine without authorisation. by attempting to connedct initially using the user name and password, i can "verify" they are who they say they are and then continue to use integrated password.

also, i want to create database roles and assign NT user names to those roles. how can i assign a nt login to a database role?

sorry, database security is a very poor area for me and all help is gratefully received.

many thanks

Answer 1

The problem is the guy leaving his terminal unlocked. Once he's done that, he can walk away after logging in to your application.

Whap the governance team on the head with a fluffy pink elephant.

---

You can create a new database login that's tied to an NT account like:

CREATE LOGIN [domain\user] FROM WINDOWS 
    WITH DEFAULT_DATABASE=[YourDb], DEFAULT_LANGUAGE=[us_english]

Make sure to add the login as a user to your database:

use YourDb
EXEC sp_grantdbaccess 'domain\user'

You can add a login to a role like:

EXEC sp_addrolemember 'YourRole', 'domain\user'

Of course, you can do this from Sql Server Management Studio too. Click Security -> New -> Login in to get started.

Answer 2

I'd suggest that your "information governance team" need to offer a solution, instead of spouting rubbish like this.

Does your company have a framework (Single Sign On, for example) that would be the correct way of launching apps.