Who has sysadmin rights that shouldn't?

Do your developers or DBBs ever give sysadmin rights to a login that they shouldn't? Do you ever do it? I know sometimes is tempting just to get around some SQL limitation so the work can get done. And who ever takes the rights away after they're done fixing the sort term problem?

Because of this every once in a while you'll find an end-user with sysadmin or dbcreator rights that they just shouldn't have. I'm sure that someone was just trying to solve a short term problem and with this week's udf, udf_Login_ServerRolesTAB you can find them so you can fix them. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER FUNCTION dbo.udf_Login_ServerRolesTAB (

) RETURNS TABLE
/*
* 
*
* Example:
select * from udf_Login_ServerRolesTAB()
*      
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #33 08/03/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
***************************************************************/
AS RETURN

SELECT TOP 100 PERCENT WITH TIES
       b.name [Login]
     , max(case when 16 & b.xstatus > 0 then 1 else 0 end) [Sys]
     , max(case when 32 & b.xstatus > 0 then 1 else 0 end) [Security]
     , max(case when 64 & b.xstatus > 0 then 1 else 0 end) [IsServer]
     , max(case when 128 & b.xstatus > 0 then 1 else 0 end) [Setup]
     , max(case when 256 & b.xstatus > 0 then 1 else 0 end) [Process]
     , max(case when 512 & b.xstatus > 0 then 1 else 0 end) [Disk]
     , max(case when 1024 & b.xstatus > 0 then 1 else 0 end) [dbcreator]
     , max(case when 4096 & b.xstatus > 0 then 1 else 0 end) [Bulk]
    FROM  master.dbo.spt_values A
        CROSS JOIN master.dbo.sysxlogins B 
    WHERE   A.[low] = 0 
         AND A.type = 'SRV' 
         AND B.srvid IS NULL
         AND A.number & B.xstatus = A.number
    GROUP BY b.[name]
    ORDER BY b.[name]



GO
 
GRANT SELECT on dbo.udf_Login_ServerRolesTAB to PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's test out the function:

select * from udf_Login_ServerRolesTAB()
GO


(Results)
Login                    Sys Security IsServer Setup Process Disk dbcreator Bulk
------------------------ --- -------- -------- ----- ------- ---- --------- ----
BUILTIN\Administrators   1   0        0        0     0       0    0         0
distributor_admin        1   0        0        0     0       0    0         0
NSRED\Andrew             1   0        0        0     0       0    0         0
NSRED\Eric               0   0        0        0     0       0    1         0
NSRED\SQLSVC             1   0        0        0     0       0    0         0
remotedba                1   0        0        0     0       0    0         0
sa                       1   0        0        0     0       0    0         0

As you can see, NSRED\Eric has been granted the dbcreator role and there's a SQL Login called remotedba with sysadmin rights. Do you want to allow this? Maybe. Maybe not. udf_Login_ServerRolesTAB gives you an easy way to check.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick