Sql-server – SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

loginssql serversql-server-2008t-sqlusers

Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this procedure to then drop the logins, search all databases & drop any related users.

Id also like to drop all SQL logins that have not been logged in for over X months – and again, drop any related users across all databases.

Before I go writing all this from scratch – I cant help but feel I'd be re-inventing the wheel – someone must surly have written a script to do this already but i'm not having much luck finding one!

If no one has a script up their sleeve to do all this, scripts to do parts of them would be just as good – I've no problem merging them into a single script.

Best Answer

I don't have a full script for the task. But maybe this script could be a start.

It returns a list of logins that don't have permissions granted. Using sp_msforeachdb it searches on all databases using sys.database_principals and sys.database_role_members. You could insert the result in a temp table and then do whatever you need to do with that list.

I'm sure I found this script some time ago somewhere and made some small modifications to fit my needs. But can't find where I found the original one. So sorry if no link provided to the author(s).

SET NOCOUNT ON
CREATE TABLE #all_users (db       VARCHAR(70),
                        SID      VARBINARY(85),
                        stat     VARCHAR(50))
EXEC MASTER.sys.sp_msforeachdb
     'INSERT INTO #all_users  
         SELECT ''?'', CONVERT(varbinary(85), sid) , 
          CASE WHEN  r.role_principal_id IS NULL AND p.major_id IS NULL 
          THEN ''no_db_permissions''  ELSE ''db_user'' END
         FROM [?].sys.database_principals u LEFT JOIN [?].sys.database_permissions p 
           ON u.principal_id = p.grantee_principal_id  
           AND p.permission_name <> ''CONNECT''
          LEFT JOIN [?].sys.database_role_members r 
           ON u.principal_id = r.member_principal_id
          WHERE u.SID IS NOT NULL AND u.type_desc <> ''DATABASE_ROLE'''

IF EXISTS 
   (SELECT l.name
       FROM   sys.server_principals l
              LEFT JOIN sys.server_permissions p
                   ON  l.principal_id = p.grantee_principal_id
                   AND p.permission_name <> 'CONNECT SQL'
              LEFT JOIN sys.server_role_members r
                   ON  l.principal_id = r.member_principal_id
              LEFT JOIN #all_users u
                   ON  l.sid = u.sid
       WHERE  r.role_principal_id IS NULL
              AND l.type_desc <> 'SERVER_ROLE'
              AND p.major_id IS NULL
   )
BEGIN
    SELECT DISTINCT l.name     LoginName,
           l.type_desc,
           l.is_disabled,
           ISNULL(u.stat + ', but is user in ' + u.db + ' DB', 'no_db_users') 
           db_perms,
           CASE 
                WHEN p.major_id IS NULL AND r.role_principal_id IS NULL THEN 
                     'no_srv_permissions'
                ELSE 'na'
           END                 srv_perms
    FROM   sys.server_principals l
           LEFT JOIN sys.server_permissions p
                ON  l.principal_id = p.grantee_principal_id
                AND p.permission_name <> 'CONNECT SQL'
           LEFT JOIN sys.server_role_members r
                ON  l.principal_id = r.member_principal_id
           LEFT JOIN #all_users u
                ON  l.sid = u.sid
    WHERE  l.type_desc <> 'SERVER_ROLE'
           AND ((u.db IS NULL
                       AND p.major_id IS NULL
                       AND r.role_principal_id IS NULL
                 )
                   OR (u.stat = 'no_db_permissions'
                          AND p.major_id IS NULL
                          AND r.role_principal_id IS NULL
                      ))
    ORDER BY 1, 4
END
DROP TABLE #all_users

Sample result:

LoginName            type_desc  is_disabled         db_perms                                        srv_perms
app_agentjobreader   SQL_LOGIN      0       no_db_permissions, but is user in mydb DB           no_srv_permissions
app_web_general      SQL_LOGIN      0       no_db_permissions, but is user in mydb_second DB    no_srv_permissions
app_web_maker        SQL_LOGIN      0       no_db_permissions, but is user in mydb_third DB     no_srv_permissions

Related Solutions

Sql-server – SQL Server drop and create all users in every DB

OK, there are several things that it could be but I'm not sure if we can determine which from here. These include the following:

AD propagation issues
AD caching "stickiness" on the target SQL Server
Domain Trust issues (particularly if the target server is in a different domain)
Windows Group membership/visibility issues
UAC blocking users access to certain groups needed for the new server
Kerberos issues (very common)
Etc...

And yes, dropping and re-adding the Users does seem to fix it in some of these cases, even though this should in theory never be needed because the SIDs should always match for Windows Users in transferred databases. It may be that the target SQL server simply lacks the same necessary server-level principal definitions as the original servers (and this as a side-effect creates explicit ones), or it may be that the act of adding the Login/user causes SQL Server to forcibly rerun the query and reload it's AD information for that user.

In any event, the real problem tends to be that dropping a user is a potentially disruptive and destructive act. It may work, but it may also cause some bad side-effects. Nonetheless, if you want to try that, then the best procs I know of for that are from Ted Krueger and they are located here: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users.

Other (possible) solutions that you could employ include:

Automated procedure that does not drop the User, but does add and associate a server-level Windows Login for it.
Create a Windows AD group for access to the database, add all of the users to that, and then add that to the SQL Server as a Login and Database as a User. (this is the recommended approach for managing large-scale access anyway).
...

Sql-server – sp_setapprole and creating users / logins

to do it correctly is not very simple, although once setup, your done.

Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation.

Any way to give these server scope permissions by means of impersonating will fail by default.This is by design.

You have 2 options:

Tell SQL Server that you as a DBA trust any user in that database to have privilegde escalation to server wide permission if impersonation is used. You acomplish this by setting the option TRUSTWORTHY to ON However if you don't fully understand what this option does first read: SQL Server 2012 Best Practice Whitepaper (Specifically page 18 and 19 about "Database Ownership and Trust")
Use signed stored procedures to accomplish your task.

I'll first give you the example of option 1:

CREATE LOGIN [appRoleProxy] WITH PASSWORD ='1234abcd!@'
GO

EXEC sp_addsrvrolemember    @loginame='appRoleProxy',
                            @rolename='securityadmin'

GO

CREATE DATABASE [Stefaan]
go

ALTER DATABASE [Stefaan] SET TRUSTWORTHY ON
GO

USE [Stefaan]
GO

--add the proxy account to the dbo role since your stored procedure will be running in the context of this account and is also accessing your user database.
ALTER ROLE [db_owner] ADD MEMBER [appRoleProxy]
GO

CREATE APPLICATION ROLE appRole WITH PASSWORD ='Passw0rd!';
GO

CREATE PROCEDURE dbo.usp_testproc 
@LoginName nvarchar(128),
@Password nvarchar(128),
@DBName nvarchar(128)
WITH EXECUTE AS 'appRoleProxy' --Impersonate a SQL server login with the proper server level permissions
AS
DECLARE @SQL varchar(max)
BEGIN
    IF NOT EXISTS ( SELECT  name
                    FROM    sys.server_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
                + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
                + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
            EXECUTE(@SQL);
        END

    IF NOT EXISTS ( SELECT  name
                    FROM    sys.database_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
                + @LoginName + ']';
            EXECUTE(@SQL);
        END

    IF EXISTS ( SELECT  name
                FROM    sys.server_principals
                WHERE   name = @LoginName )
        BEGIN
            EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
        END
END
GO

GRANT EXECUTE ON [dbo].[usp_testproc] TO [appRole]
GO


sp_setapprole   @rolename='appRole',
                @password='Passw0rd!'



EXEC usp_testproc 'testlogin','password23@','stefaan'

Now for an example of option 2 I'd like to point out a answer a gave on another question: Msg 4834 “You do not have permission to use the bulk load statement”

Best Answer

Related Solutions

Sql-server – SQL Server drop and create all users in every DB

Sql-server – sp_setapprole and creating users / logins

Related Question