x

Checking the Windows Authentication username

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.)

more ▼

asked Jan 29, 2010 at 10:52 AM in Default

Alex gravatar image

Alex
66 3 3 5

Is there any particular reason you don't want to write this as a CLR UDF?
Jan 29, 2010 at 11:55 AM Matt Whitfield ♦♦
Yes. CLR is disabled and the DBA doesn't want to enable it. (Unless he really has to.)
Jan 29, 2010 at 12:34 PM Alex
Shame... that would turn 'dirty hack' into 'half decent solution'...
Jan 29, 2010 at 05:04 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jan 29, 2010 at 11:58 AM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 34

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, 2010 at 12:37 PM Alex
(comments are locked)
10|1200 characters needed characters left

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...

more ▼

answered Jan 29, 2010 at 11:15 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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, 2010 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, 2010 at 11:46 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949
x18

asked: Jan 29, 2010 at 10:52 AM

Seen: 1357 times

Last Updated: Jan 29, 2010 at 11:35 AM