I am trying to grant permissions to database objects and other SQL Server apparatus and getting an error stating it is invalid to grant certain permissions because the targeted principal has already been granted sysadmin permission.
So in my little loop I opted to check if the current principal is already a sysadmin before granting the specific permissions that were failing.
if is_srvrolemember('sysadmin', @principal) begin print 'Grant permission' .... .... end
And, I thought I was good.
But, No, permissions were never granted, even for those principals that should not have been skipped over.
declare @principal sysadmin declare @principalIsSysadmin smallint declare @principalID int declare @CHAR_TAB char(1) declare @log nvarchar(600) set @CHAR_TAB = char(9); select @principalIsSysadmin = IS_SRVROLEMEMBER('sysadmin', @principal) , @principalID = SUSER_ID(@principal) set @log = @CHAR_TAB + @principal + ' - principal ID ' + cast ( isNull(@principalID, -1) as varchar(30) ) + ' is ' + cast ( isNull(@principalIsSysadmin, -1) as varchar(30) ) print @log
As I reviewed the output, was able to decipher that the suser_id function was returning null for the specific principal.
The remediation, of course, is to check if targeted principal exists.
If not create it.
if suser_id('LAB\jsmith') is null begin create login [LAB\jsmith] from windows; end
Went back and read the documentation….
If the optional login parameter is provided, the Windows login that is being queried must be present in sys.server_principals, or IS_SRVROLEMEMBER will return NULL.
This indicates that the login is not valid.
I am so glad that IS_SRVROLEMEMBER is not binary.
That is, it’s return code is not a bit, but int.
All the praise in the world, but there goes Microsoft being overly generous.
tinyint would have been sufficient.
My point remains having it return more than 1 or 0, should have been enough clue that something is amiss with my principal, permission requirement for the requester, etc.