Transact SQL – Ensuring that Windows Account is not yet provisioned, prior to granting access

Background

Trying to script provisioning of Windows Account in SQL Server.

The basic things we want to do is Add the Login to the SQL Server Instance, add same to a couple of user databases, and grant membership to database roles.

 

Script

Here is our original try

Create Login


if SUSER_SID('LAB\wsSales01$') is null
begin

	print 'Create Login LAB\wsSales01$ ... '

		create login [LAB\wsSales01] from windows;

	print 'Created Login LAB\wsSales01'

end

I was happy and went my merry way.

But, it seems the account never got created.

And, why do I think so …

Later added the login to individual databases using the sample script below…

 

Add User to Database


use [hrdb]
go

if user_id('LAB\wsSales01$') is null
begin

     create user [LAB\wsSales01$]
       from LOGIN [LAB\wsSales01$]
     ;

end
go

 


Grant user access to roles


exec sp_addrolemember 
			  @rolename = 'db_datareader'
			, @membername = 'LAB\wsSales01$'

exec sp_addrolemember 
			  @rolename = 'db_datawriter'
			, @membername = 'LAB\wsSales01$'


Validate User

Script



exec sp_helpuser @membername = 'LAB\wsSales01$'


Output

sp_helpuser__20161220_0454pm

 

Explanation

  1. Everything looks good and lines up outside of the fact that the Login name and DefaultDB are blank

 

Remediation

Script

Here we tried two functions SUSER_SID and SUSER_ID.



declare @login sysname

set @login = 'LAB\wsSales01$'

select 
		  [login] = @login	
		, [SUSER_SID] = SUSER_SID(@login)
		, [SUSER_ID] = SUSER_ID(@login)


 

Output

s_user_201612120_0502pm

 

Explanation

  1. SUSER_SID returns the Login’s SID
  2. And, SUSER_ID comes back as NULL

 

Documentation

 

Function Description Link
 SUSER_SID Returns the security identification number (SID) for the specified login name.
For Windows Account or Group, consistent across SQL Server Instances.
On the other hand for SQL Logins, generated ID and instance specific
 Link
 SUSER_ID SUSER_ID returns an identification number only for logins that have been explicitly provisioned inside SQL Server. This ID is used within SQL Server to track ownership and permissions. This ID is not equivalent to the SID of the login that is returned by SUSER_SID. If login is a SQL Server login, the SID maps to a GUID. If login is a Windows login or Windows group, the SID maps to a Windows security identifier.SUSER_SID returns a SUID only for a login that has an entry in the syslogins system table.  Link

 

 

Explanation

  1. It appears that in the case of Windows Accounts and Groups, the system will always come back with a SUSER_SID
  2. On the other hand, the SUSER_ID is more discriminate, as it will only come back with a value when the account has been explicitly granted access to the SQL Server Instance

 

Script

Here is our revised conditional login create statement

Create Login – Revised

Create Login – Code


--replace SUSER_SID with SUSER_ID
--if SUSER_SID('LAB\wsSales01$') is null
if SUSER_ID('LAB\wsSales01$') is null
begin

	print 'Create Login LAB\wsSales01$ ... '

		create login [LAB\wsSales01] from windows;

	print 'Created Login LAB\wsSales01'

end

 

Validation

exec sp_helpuser @membername = ‘LAB\wsSales01$’

Output

sp_helpuser__20161220_0604pm

 

Explanation

Now, we have a not null value for Login Name.

 

Summary

In summary, you likely want to use SUSER_ID and not SUSER_SID to determine whether a Windows Account has been explicitly registered with a SQL Server Instance.

It amazes how much engineering discipline went into SQL Server’s API Design.

It is so complete and one can make skims over the extensive documentation.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s