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.
Here is our original try
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$'
exec sp_helpuser @membername = 'LAB\wsSales01$'
- Everything looks good and lines up outside of the fact that the Login name and DefaultDB are blank
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)
- SUSER_SID returns the Login’s SID
- And, SUSER_ID comes back as NULL
- It appears that in the case of Windows Accounts and Groups, the system will always come back with a SUSER_SID
- 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
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
exec sp_helpuser @membername = ‘LAB\wsSales01$’
Now, we have a not null value for Login Name.
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.