Checking the Windows Authentication username

Question

I have a simple stored procedure which needs to check if the account name "DOMAIN\alex" specified as parameter is a valid account on that system. At least, valid enough to create a new login account for. I just can't find a simple function to do this in T-SQL and I don't want to write some .NET plugin or whatever. So, does anyone know which function I can use to validate the account name?

The purpose of this Stored Procedure is to check if Windows knows the account name before it's used to create a new login. This login is then linked to a database user with restricted access.

But to create the login, I need to make sure that the Windows account is an existing account, else the "CREATE LOGIN" will fail. (And I hate failing statements, which require exception handlers.)

Answer 1

0

Even if you solve your question, you're going to end up with lots of SQL logins. Difficult to control. They'll go stale when people leave. You'll have to change the permissions of all of them when changes are needed.

It would be much easier and better to do it like this:

Create a database role with the restricted privileges these logins need.
Create a Windows group and add the necessary Windows logins to it.
Create a SQL login for that group.
Grant the group access to the database as a member of the role.

Then you'll only have one SQL login and, if the permissions need to change, you just change the role's permissions and you're done.

answered Jan 29 '10 at 11:58 AM

David Wimbush
4.2k ● 25 ● 29 ● 31

Unfortunately, several other stored procedures make use of the user account name for reporting purposes. Users that haven't been reporting for 3 or more months are also reported, thus stale accounts will disappear.

Jan 29 '10 at 12:37 PM Alex

(comments are locked)

Answer 2

0

to find a login on a server use:

SELECT [s].[name] FROM [sys].[syslogins] AS s
WHERE [s].[name] = @requiredusername

You can use

IF EXISTS (SELECT [s].[name] FROM [sys].[syslogins] AS s
    WHERE [s].[name] = @requiredusername)

to test for its appearance and to then allow a process if it is or isnt found...

answered Jan 29 '10 at 11:15 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

That only works if the login has already been added. In my situation, the login isn't added yet and the SP is to add the login account if it does not exist.

Jan 29 '10 at 11:25 AM Alex

use a TRY/CATCH block to try the account creation and you can then report back that the account either succeeded or failed. Or you can try to reference Active Directory through OPENQUERY ...

Jan 29 '10 at 11:46 AM Fatherjack ♦♦

(comments are locked)

Checking the Windows Authentication username

Follow this question

Related Questions