Category Archives: Security

Security, TSQL

Tweaked script to find all db_owner members

Leave a comment

Doing some security cleanup. This script is useful. Ran it against all servers in the environment and needed to fix it up a little to work. Finds members of db_owner,db_accessadmin. However, I don’t think it elaborates on what login dbo is mapped to. I’ve included a script below that checks for that – also tweaked to fix.

DECLARE @databaseName SYSNAME ,
@sql VARCHAR(1000)
DECLARE @Tbl TABLE
(
DatabaseName VARCHAR(200) ,
RoleDesc VARCHAR(100) ,
UserName VARCHAR(100)
)
DECLARE databaseCursor CURSOR
FOR
SELECT Name
FROM sys.databases
WHERE state = 0
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @databaseName
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql = 'SELECT ''' + @databaseName + ''' AS [Database],
USER_NAME(role_principal_id) AS [Role],
USER_NAME(member_principal_id) AS [User]
FROM [' + @databaseName
+ '].sys.database_role_members
WHERE USER_NAME(role_principal_id) IN (''db_owner'', ''db_accessadmin'') and USER_NAME(member_principal_id) <> ''dbo'' '
INSERT INTO @Tbl
EXEC ( @sql
)
FETCH NEXT FROM databaseCursor INTO @databaseName
END
CLOSE databaseCursor
DEALLOCATE databaseCursor
SELECT *
FROM @Tbl

dbo login mapping:

--Script : Verify the database owner and DBO User owner are the same
--When a login is defined as the DBO user, he/she has FULL control within the database
set nocount on
go
Create table #TmpTableSec1 (database_name varchar(100), Owner varchar(100))
Create table #TmpTableSec2 (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
Create table #TmpResult (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
DECLARE DBCURSOR CURSOR FOR
select name
from sys.databases where state=0 and name not in ('tempdb')
Declare @name varchar(100)
Declare @cmd varchar(200)
Declare @dbowner varchar(100)
Declare @DBO varchar (100)
OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'Database --> ['+@name+']'
set @cmd = 'select name, suser_sname(owner_sid) from master.sys.databases where name = '''+@name+''''
--select @cmd
insert #TmpTableSec1 exec (@cmd)
--select @dbowner = (select suser_sname(owner_sid) from master.sys.databases where name = @name)
set @cmd = 'use ['+ @name +']
select db_name(), name, suser_sname(sid)
from sys.database_principals where name = ''dbo'''
--select ''@DBO'' = (select suser_sname(sid) from sys.database_principals where name = ''dbo'') '
INSERT #TmpTableSec2 exec (@cmd)
--print ''
FETCH NEXT FROM DBCURSOR INTO @name
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
go
insert into #TmpResult
select a.database_name, a.Owner, b.DBO_Owner_Login from #TmpTableSec1 a
join #TmpTableSec2 b
on a.database_name = b.database_name
print '--- Database Owner & DBO User Mapping ---'
print '*****************************************'
select * from #TmpResult
print '--- Orphaned DBO Logins ---'
print '***************************'
select * from #TmpResult
where DBO_Owner_Login not in (select name from sys.server_principals) or DBO_Owner_Login is null
drop table #TmpTableSec1
drop table #TmpTableSec2
drop table #TmpResult
set nocount off

Thanks to the sqldude and msft forum members for providing these nearly complete scripts:

Verifying DBO User ownership and track users without a SQL login but via AD Group Logins

Admin, Scripts, Security

Easy Security Fixes

Leave a comment

Here are some security tasks I’ve been tackling lately.

1.  Find weak passwords and change them.

I keep track of application logins and passwords using a product called KeePass. It has a plugin called strengthreport that you can use to make a report or .csv showing the strength of your passwords. Use the SVN command to checkout that code to a local directory and then copy the StrengthReport folder to wherever you have KeePass installed. Restart KeePass and under the tools menu, you find a few new entries, all labeled ‘Create Report’. Pick one, they all seem to do the same thing. Then generate a report and send to .CSV.

You can filter on the ‘Basic Strength’ column or you can create a column with a single number in it to sort on.

Create a formula based on the ‘Basic Strength’ column: =FIND(“/”,D63)
Then in the next column over do something like this: =MID(D63,E63-1,1)
You could do that all in one block if you’re a fancy sort of excel wizard.

Now that the easy part is done, you can start working with developers to pump these passwords up.

2.  Set policy on logins so that they abide by Active Directory rules on complexity and failed attempts.

You can enable CHECK_POLICY on your logins by running some similar code to what is shown below.  The logins do not unlock themselves after the cooloff time defined in AD.  That’s not great.  Also, instead of clearing the ‘locked’ box on the login, just disable and re-enable CHECK_POLICY for the login (enforce password policy). This will prevent having to change the password, which will in-turn, stop developers from hating you.

 3.  Make sure logins are created properly in the future.

In KeePass, you can define an autotype template for each folder of logins.  This means you can do something like this and variables will be substituted into the template, providing you will a quick way to create users.

CREATE LOGIN [{UserName}] WITH PASSWORD=N'{Password}’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

Just create the login in KeePass, click into a SSMS window, switch back to KeePass and CTRL+V to make that code jump into SSMS.

You can also handle this is with a SQL Server Management Policy using the login facet and the PasswordPolicyEnforced property. That’s the way to go!

Roles, Scripts, Security

See all roles on your server

Leave a comment

This cursor inserts all of the roles and members from each database into your admin database.


USE [SQL_DBA_Sandbox]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Roles](
[DBName] [varchar](max) NULL,
[Role] [varchar](max) NULL,
[Member] [varchar](max) NULL
) ON [PRIMARY]
END
ELSE
DELETE FROM SQL_DBA_Sandbox.dbo.Roles;
GO

DECLARE @DB_NAME sysname;
SET @DB_NAME = NULL;

DECLARE @SQL VARCHAR(2000);

DECLARE Cursor_DB CURSOR FAST_FORWARD READ_ONLY FOR

SELECT name FROM sys.databases
WHERE database_id > 4

OPEN Cursor_DB

FETCH NEXT FROM Cursor_DB INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL =
'INSERT INTO SQL_DBA_Sandbox.dbo.Roles
( DBNAME, ROLE, MEMBER )
SELECT ''' + @DB_NAME + ''',
r.name AS ''role'',
mem.name AS ''member''
FROM [' + @DB_Name + '].sys.database_role_members rm
INNER JOIN [' + @DB_Name + '].sys.database_principals r ON r.principal_id = rm.role_principal_id
INNER JOIN [' + @DB_Name + '].sys.database_principals mem ON mem.principal_id = rm.member_principal_id';
PRINT @SQL;

EXEC (@SQL);

FETCH NEXT FROM Cursor_DB INTO @DB_NAME

END

CLOSE Cursor_DB
DEALLOCATE Cursor_DB

SELECT DISTINCT DBName,[Role] FROM SQL_DBA_Sandbox.dbo.Roles
WHERE Role = 'analyst'

SELECT * FROM SQL_DBA_Sandbox.dbo.roles
WHERE Member = 'my_user'

Schema, Scripts, Security

See all the schemas on your server

Leave a comment

Oh no! A useful cursor?


DECLARE @DB_NAME sysname;
SET @DB_NAME = NULL;

CREATE TABLE #RESULTS
(TABLE_CATALOG sysname,
TABLE_SCHEMA sysname)

DECLARE @SQL VARCHAR(2000);

DECLARE Cursor_DB CURSOR FAST_FORWARD READ_ONLY FOR

SELECT name FROM sys.databases
WHERE database_id > 4

OPEN Cursor_DB

FETCH NEXT FROM Cursor_DB INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'USE ['+ @DB_NAME + '];
INSERT INTO #RESULTS
( TABLE_CATALOG, TABLE_SCHEMA )
SELECT DISTINCT TABLE_CATALOG,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
UNION ALL
SELECT DISTINCT TABLE_CATALOG,TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
UNION ALL
SELECT DISTINCT ROUTINE_CATALOG,ROUTINE_SCHEMA FROM INFORMATION_SCHEMA.ROUTINES;'

PRINT @SQL;

EXEC (@SQL);

FETCH NEXT FROM Cursor_DB INTO @DB_NAME

END

CLOSE Cursor_DB
DEALLOCATE Cursor_DB

SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA FROM #RESULTS
WHERE TABLE_SCHEMA <> 'DBO'