Category: User Management

How Do I Kill All Sessions from a Windows Group?

Posted on by David Levy

Introduction

The stored procedure I am posting today will kill all sessions for users that are members of a given domain group. I use this stored procedure to keep ad-hoc (write down Access) users out of the way of nightly builds on my data warehouse. I have created two jobs for each group of users that I want to keep out of the way. The first job denies connect to the Windows Group then calls this stored procedure to kick all of the users off. I run this job just before the build begins. The second job grants connect to the group after the nightly build finishes.

The Script

The stored procedure gets all users logged in via Windows Authentication by looking for the slash in their login name, cursoring through the list of users getting all windows groups for each user. If any windows groups that user is a member of match the one passed in then the session is killed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE PROCEDURE dbo.sp_dba_kill_spids_for_domain_group(@domain_group sysname)

AS

SET NOCOUNT ON

DECLARE @spid           smallint,
        @loginame       nvarchar(128),
        @command        nvarchar(max)

CREATE TABLE #Logins
(
    account_name        sysname NULL,
    type                char(8) NULL,
    privilege           char(9) NULL,
    mapped_login_name   sysname NULL,
    permission_path     sysname NULL
)

DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT  RTRIM(login_name),
            session_id
    FROM    sys.dm_exec_sessions
    WHERE   login_nameLIKE '%%' --Look for the slash between domain and login

OPEN csr

FETCH NEXT FROM csr INTO @loginame, @spid

WHILE @@FETCH_STATUS <> -1
 BEGIN
    INSERT #Logins
        EXEC xp_logininfo @loginame, 'all'
   
    IF EXISTS(  SELECT  *
                FROM    #Logins
                WHERE   permission_path = @domain_group    )
     BEGIN
        SELECT  @command = 'KILL ' + CAST(@spid AS nvarchar) + ' --' + @loginame
        PRINT @command
        EXEC sp_executesql @command
     END

    TRUNCATE TABLE #Logins

    FETCH NEXT FROM csr INTO @loginame, @spid
 END

CLOSE csr

DEALLOCATE csr

DROP TABLE #Logins

SET NOCOUNT OFF

GO

Conclusion

As usual, I hope you find this stored procedure helpful. Be careful that you know who is in a group before killing the sessions. I have seen situations where people found out they were in the wrong group because their sessions kept getting killed. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

How Can I Tell if a Windows Login has Rights to My Server?

Posted on by David Levy

Introduction

Here is another utility stored procedure that I use. This stored procedure will tell you how a user has access to your server. This is a great stored procedure to use for things like server dashboards where you only want people to see the dashboard for servers that they have access to without granting them all the rights that would go with the dashboard.

The Script

This stored procedure is quite simple. For a given login name call xp_logininfo, passing in the login name, piping the results into a temporary table. Once the table is built check to see if it has any rows and whether or not we should raise an exception based on the @hide_exceptions flag. If yes then raise an exception else just move along. Lastly, return any information returned about the login to the caller.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE PROCEDURE dbo.sp_dba_logininfo @loginame nvarchar(128), @hide_exceptions bit = 0

AS

 BEGIN

    CREATE TABLE #Logins
    (
        account_name        sysname NULL,
        type                char(8) NULL,
        privilege           char(9) NULL,
        mapped_login_name   sysname NULL,
        permission_path     sysname NULL
    )

        INSERT #Logins
            EXEC xp_logininfo @loginame

    IF (SELECT COUNT(*) FROM #Logins) = 0 AND @hide_exceptions = 0
     BEGIN
        RAISERROR('Specified user does not have access to this server.', 14, 1)
     END

    SELECT  account_name,
            type,
            privilege,
            mapped_login_name,
            permission_path
    FROM    #Logins

    DROP TABLE #Logins
 
 END

GO

Conclusion

As usual, I hope you find this stored procedure helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

How Do I Identify Invalid or Unneeded Logins?

Posted on by David Levy

Introduction

I was looking at one of my servers and was caught off-guard by how many individual users had been granted rights. To make it worse I recognized a few user names as former coworkers. Clearly time for some house cleaning, now time to find a script.

The Process

I went looking in my script library for anything that might help me do this analysis faster and found a good start, a script to validate user logins. Well more like a note really, based on a conversation between Thomas LaRock (Blog|Twitter) and Tim Ford (Blog|Twitter) on Twitter about a stored procedure called sp_validatelogins that makes sure a windows login on your SQL instance matches a valid login on your domain. Thomas LaRock does a great job of documenting it here.

Next I needed to figure out what users should have been in groups. My first answer is all of them, but being realistic I decided I would identify all users that have access to the server by both individual login and group then see if the group already has the same rights the user needs or if it could / should. It turns out this was easy, a cursor of windows users with a call to xp_logininfo to check for group membership will tell me exactly what to look at, including the groups and the command to get them because I often end up pasting that into an IM.

As I started working through test data I began to notice users in databases that no longer exist on the server. Again, this one was pretty easy. A query against sys.server_principals with a right outer join to sys.database_principals quickly shows all users that exist in a database that do not have logins on the server. Adding a select to a temp table and wrapping it all in sp_MSforeachdb put it all together into a nice recordset that I could then work off of.

The Script

Here is the script I ended up with, run it in text mode rather than grid for better results. I would advise against trying to automate this any further or even taking any actions without carefully researching the impact of each change before making it. Messing with security is a quick way to get a lot of attention.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
SET NOCOUNT ON

CREATE TABLE #invalid_logins
(
[sid] varbinary(85),
[name] sysname
)

CREATE TABLE #Logins
(
account_name sysname NULL,
type char(8) NULL,
privilege char(9) NULL,
mapped_login_name sysname NULL,
permission_path sysname NULL
)

INSERT #invalid_logins
EXEC sp_validatelogins

DECLARE @name sysname

DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR
SELECT sl.name
FROM sys.syslogins sl
LEFT OUTER JOIN #invalid_logins il
ON sl.name = il.name
WHERE sl.isntname = 1
AND sl.isntgroup = 0
AND il.name IS NULL

OPEN csr

FETCH NEXT FROM csr INTO @name

WHILE @@FETCH_STATUS <> -1
BEGIN
INSERT #Logins
EXEC xp_logininfo @acctname=@name, @option='all'
FETCH NEXT FROM csr INTO @name
END

CLOSE csr
DEALLOCATE csr

PRINT 'The following logins are no longer valid and should be removed from this server:'
SELECT name
FROM #invalid_logins

PRINT 'The following logins have access by login and by group and might not need access by login:'
SELECT COUNT(*) AS num_of_groups,
account_name,
REPLACE(RTRIM(( SELECT permission_path + ' '
FROM #Logins
WHERE account_name = l.account_name
FOR XML PATH('')
)), ' ', ', ') AS group_names,
'EXEC xp_logininfo @acctname=' + QUOTENAME(account_name) + ', @option=''all''' AS command_to_see_groups
FROM #Logins l
GROUP BY account_name,
[type]
HAVING COUNT(*) > 1
ORDER BY num_of_groups DESC

DROP TABLE #invalid_logins
DROP TABLE #Logins

PRINT 'The following result set shows users that were deleted from the server but not the individual databases'
CREATE TABLE #databases_with_orphan_users
(
db_with_orphan_login sysname,
orphan_login sysname,
permissions_count int
)

EXEC sp_MSforeachdb 'USE ?
INSERT #databases_with_orphan_users
SELECT ''?'' AS db_with_orphan_login,
dp.name,
(SELECT COUNT(*) FROM sys.database_permissions WHERE grantee_principal_id = sp.principal_id) AS permissions_count
FROM sys.server_principals sp
RIGHT OUTER JOIN sys.database_principals dp
ON sp.sid = dp.sid
WHERE sp.sid IS NULL
AND dp.type NOT IN (''R'')
AND dp.name NOT IN (''guest'', ''sys'', ''INFORMATION_SCHEMA'')
AND dp.type_desc NOT IN (''APPLICATION_ROLE'')'

SELECT db_with_orphan_login,
orphan_login,
permissions_count
FROM #databases_with_orphan_users

DROP TABLE #databases_with_orphan_users

Conclusion

I hope you find this script helpful. I have a feeling this script will be one of my favorites because it takes a lot of tedious analysis and boils it down to a quick hit list. Please let me know if you run into any issues with it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

Get Users By Connection

Posted on by David Levy

Introduction

I was recently working a production issue that required me to log in via the Dedicated Administrator Connection (DAC). When I tried to log in I was greeted with the following message:

Could not connect because the maximum number of ‘1’ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process.

After searching through my scripts folder and not finding anything that would tell me who was connected via the DAC I turned to Google. I found a lot of posts detailing how to use the DAC but none telling how to tell who is using it.

Once the emergency was taken care of I made a point of going back and writing a query to tell who is using the DAC. To help the community or at least that part that relies on Google like myself I am also blogging it and adding appropriate search tags.

The Query

I know, I know, enough with the backstory already lets see some code. The code I have posted here will show all users by connection, although I have included a commented WHERE clause to filter to just the DAC connection. Here is the SQL:

1
2
3
4
5
6
7
8
9
SELECT  ep.name,
        ep.protocol_desc,
        es.login_name,
        es.nt_user_name,
        es.host_name
FROM    master.sys.dm_exec_sessions es
        INNER JOIN master.sys.tcp_endpoints ep
            ON es.endpoint_id = ep.endpoint_id
--WHERE ep.endpoint_id = 1 --Uncomment WHERE to show who is on DAC Connection

Conclusion

Hopefully this script is helpful. Unfortunately this script will not tell you who is connected via the DAC when nobody is able to connect to the SQL instance in question, netstat from a command line on the server may help there. Please let me know if you run into any issues, have any ideas that would make it better or just want to share how you are using it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

Stored Procedures to Store and Get Database Users with All Permissions

Posted on by David Levy

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Introduction
Traditionally when doing a restore, moving a database, etc. a DBA would simply go into Management Studio and use the Generate Scripts wizard to script all logins and permissions. This approach can take several minutes and as I recently learned does not script database level permissions.

Solution
Bothered by the amount of effort and the fact that I kept forgetting to script out the permissions until just after I had wiped them out by starting a restore, I set out to create scripts that I could just include as steps in my restore jobs. Given that the restore wipes out the database, I knew I had to have 2 steps. The first step stores the permissions before the restore, while the second puts them back after. In the spirit of keeping the restore jobs simple, I wrapped up all of the logic into 2 stored procedures that do not require any arguments.

The first stored procedure, sp_dba_StoreDatabasePermissions, stores the logins roles and permissions to a table in msdb. I use msdb here because everybody has it and it is not master. The table is named for the database it corresponds to followed by ‘_permission_store’. The permission store table has 2 columns, the first is the actual SQL command while the second is the order to run it in. The ordering is done in groups, with all roles ranked to be run first, followed by users, adding users to roles and finally the permissions that correspond to the users and roles. The stored procedure makes use of a synonym to point to the permission store, cutting down on the use of dynamic SQL and enhancing readability. The logic to get the permissions is based on the logic of a script by Narayana Vyas Kondreddi.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dba_StoreDatabasePermissions]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sp_dba_StoreDatabasePermissions]
GO

CREATE PROCEDURE [dbo].[sp_dba_StoreDatabasePermissions]

AS

 BEGIN

    DECLARE @table_name sysname,
            @create_cmd nvarchar(4000)

    SELECT  @table_name = db_name() + '_permission_store'

    IF NOT EXISTS(SELECT * FROM msdb.sys.sysobjects WHERE name = @table_name)
     BEGIN
        SELECT  @create_cmd = 'CREATE TABLE [msdb].[dbo].' + QUOTENAME(@table_name) + ' (command nvarchar(max), run_order int)'
     END

    EXEC    sp_executesql  @create_cmd

    SELECT  @create_cmd = 'IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = N''permission_store'') CREATE SYNONYM permission_store FOR [msdb].[dbo].' + QUOTENAME(@table_name)
    EXEC    sp_executesql  @create_cmd

    INSERT  permission_store
    SELECT  'IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ''' + name + ''') CREATE ROLE [' + name + ']' AS Command,
            0 AS run_order
    FROM    sys.database_principals
    WHERE   type_desc = 'DATABASE_ROLE'
                AND is_fixed_role = 0

    INSERT  permission_store
    SELECT  'IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ''' + name + ''') CREATE USER [' + name + '] FOR LOGIN [' + name + ']' + ISNULL(' WITH DEFAULT_SCHEMA=[' + default_schema_name + ']', '') AS Command,
            1 AS run_order
    FROM    sys.database_principals
    WHERE   type_desc in ('WINDOWS_GROUP', 'SQL_USER','WINDOWS_USER') --Updated to include WINDOWS_USER on 10/5/2011. Thanks to Alex Hatcher for catching that.

    INSERT  permission_store
    SELECT  'EXEC sp_addrolemember @rolename=''' + dp_r.name + ''', @membername =''' + dp_m.name + '''' AS Command,
            2 AS run_order
    FROM    sys.database_role_members drm
            INNER JOIN sys.database_principals dp_r
                ON drm.role_principal_id = dp_r.principal_id
            INNER JOIN sys.database_principals dp_m
                ON drm.member_principal_id = dp_m.principal_id
    WHERE   dp_m.name NOT IN ('dbo')

    INSERT      permission_store
    SELECT      'IF EXISTS(SELECT * FROM sys.objects WHERE name = ''' + obj.name + ''' AND USER_NAME(schema_id) = ''' + USER_NAME(obj.schema_id) + ''' AND type = ''' + RTRIM(obj.type) + ''') ' +
                CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
                + ' ' + perm.permission_name + ' ' + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
                + CASE WHEN cl.column_id IS NULL THEN '' ELSE '(' + QUOTENAME(cl.name) + ')' END
                + ' TO ' + QUOTENAME(usr.name) COLLATE database_default
                + CASE WHEN perm.state <> 'W' THEN '' ELSE ' ' + 'WITH GRANT OPTION' END AS Command,
                3 AS run_order
    FROM        sys.database_permissions AS perm
                INNER JOIN sys.objects AS obj
                    ON perm.major_id = obj.[object_id]
                INNER JOIN sys.database_principals AS usr
                    ON perm.grantee_principal_id = usr.principal_id
                INNER JOIN sys.database_principals AS adm
                    ON perm.grantor_principal_id = adm.principal_id
                LEFT JOIN sys.columns AS cl
                    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
 END
GO
EXEC sp_MS_marksystemobject 'sp_dba_StoreDatabasePermissions'
GO

The second stored procedure in the pair, sp_dba_GetDatabasePermissions, is very simple. First it checks for the synonym in the database and creates it if it is missing. After verifying the synonym, the stored procedure opens up a cursor against the permission store and begins executing commands ordered ascending by run_order. After running all commands, the stored procedure checks the value of the optional parameter @keep_permission_store to see if it should clean up the permission store table or leave it out there. The default behavior is to drop the permission store when done with it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dba_GetDatabasePermissions]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sp_dba_GetDatabasePermissions]
GO

CREATE PROCEDURE [dbo].[sp_dba_GetDatabasePermissions] @keep_permission_store bit = 0

AS

 BEGIN


    DECLARE @command nvarchar(max)

    SELECT  @command = 'IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = N''permission_store'') CREATE SYNONYM permission_store FOR [msdb].[dbo].' + QUOTENAME(db_name() + '_permission_store')
    EXEC    sp_executesql  @command

    DECLARE commands CURSOR FAST_FORWARD READ_ONLY FOR
        SELECT      command
        FROM        dbo.permission_store
        ORDER BY    run_order

    OPEN commands

    FETCH NEXT from commands INTO @command

    WHILE(@@FETCH_STATUS <> -1)
     BEGIN
        PRINT @command
        EXEC sp_executesql @command
        FETCH NEXT from commands INTO @command
     END

    CLOSE commands

    DEALLOCATE commands

    IF @keep_permission_store != 1
     BEGIN
        SELECT @command = 'DROP TABLE ' + base_object_name FROM sys.synonyms WHERE name = 'permission_store'
        DROP SYNONYM dbo.permission_store
        PRINT @command
        EXEC sp_executesql @command
     END
 END
GO
EXEC sp_MS_marksystemobject 'sp_dba_GetDatabasePermissions'
GO

Other Uses
It seems like every day I find more uses for these stored procedures. Most recently I have been running them at replication subscribers before making any changes at the publisher. The @keep_permission_store flag of sp_dba_GetDatabasePermissions comes in really handy here. Passing a value of 1 allows permissions to be put back several times, a lifesaver when things do not go right on the first try.

I also use these stored procedures to copy permissions between databases on different servers. I started out running sp_dba_StoreDatabasePermissions then selecting from the permission store table ordered by rank in text, copying, pasting and running on the new server. After adding the print statement to sp_dba_GetDatabasePermissions it was just easier to call sp_dba_StoreDatabasePermissions then sp_dba_GetDatabasePermissions right away, copying the commands from the messages window and pasting them into a query window to run wherever I want.

Wrap-Up
These stored procedures have served me well and I hope they will serve you well. I would love to hear how people are using them and any suggestions for enhancements. As always, scripts on the internet are like Halloween candy, check them thoroughly before consuming.