Database Permissions |
|
When doing a migration from SQL 2005 to SQL 2012 we changed the security model from individual users to Active Directory groups. I needed to find what everyone's permissions were, specifically Execute permissions. Here is what I came up with. I have included the script below for download.
;WITH Perms_query ( DBname, DB_ObjectName, Permission_name, state_desc, DB_User, Type_Desc, Table_Name, Proc_name, Login_Name )
AS ( SELECT DB_NAME(DB_ID()) AS DBName ,
OBJECT_NAME(dp.major_id) AS DB_OBJECTNAME ,
dp.permission_name ,
dp.state_desc ,
d.name AS DB_User ,
d.type_desc ,
ISNULL(t.name, 'NOT A TABLE') AS Table_name ,
ISNULL(P.name, 'NOT A PROC') AS Proc_name ,
ISNULL(l.name, 'ORPHANED USER') AS Login_name
FROM sys.database_permissions AS dp
JOIN sys.database_principals d ON dp.grantee_principal_id = d.principal_id
JOIN sys.objects o ON o.object_id = dp.major_id
LEFT JOIN sys.tables t ON t.object_id = o.object_id
LEFT JOIN SYS.procedures P ON P.object_id = O.object_id
LEFT JOIN sys.syslogins l ON l.sid = d.sid
WHERE dp.major_id >= 0
AND d.type IN ( 'S', 'G', 'U' )
AND dp.permission_name <> 'connect'
UNION
SELECT DB_NAME(DB_ID()) AS dbname ,
dp.name account_name ,
USER_NAME(drm.role_principal_id) AS DB_Role_Member ,
' ' ,
' ' ,
' ' ,
' ' ,
' ' ,
' '
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
WHERE dp.sid NOT IN ( 0x01 )
AND dp.sid IS NOT NULL
AND dp.type NOT IN ( 'c' )
AND dp.is_fixed_role <> 1
AND dp.name NOT LIKE '##%'
AND drm.role_principal_id IS NOT NULL
)
SELECT Dbname ,
DB_ObjectName AS [DB_Object or AcctName] ,
Permission_name ,
CASE WHEN state_desc = '' THEN '--'
ELSE state_desc
END AS state_desc ,
CASE WHEN DB_User = '' THEN '--'
ELSE DB_User
END AS DB_User ,
CASE WHEN Type_Desc = '' THEN '--'
ELSE Type_Desc
END AS Type_Desc ,
CASE WHEN Table_Name = '' THEN '--'
WHEN Table_Name = 'NOT A TABLE' THEN '--'
ELSE Table_Name
END AS Table_Name ,
CASE WHEN Proc_name = '' THEN '--'
WHEN Proc_name = 'NOT A PROC' THEN '--'
ELSE Proc_name
END AS Proc_Name ,
CASE WHEN Login_Name = '' THEN '--'
ELSE Login_Name
END AS Login_Name ,
CASE WHEN Permission_name = 'Execute'
THEN 'Grant execute on [' + Proc_name + '] to [' + DB_User+']'
WHEN permission_name = 'select ' AND proc_name = 'NOT A PROC'
THEN 'Grant select on [' + DBname+ '] to [' + DB_User+ ']'
ELSE '--'
END AS Exec_stmt
FROM Perms_query
WHERE state_desc <> 'deny'
AND DB_User NOT LIKE '##%'
ORDER BY state_desc
;WITH Perms_query ( DBname, DB_ObjectName, Permission_name, state_desc, DB_User, Type_Desc, Table_Name, Proc_name, Login_Name )
AS ( SELECT DB_NAME(DB_ID()) AS DBName ,
OBJECT_NAME(dp.major_id) AS DB_OBJECTNAME ,
dp.permission_name ,
dp.state_desc ,
d.name AS DB_User ,
d.type_desc ,
ISNULL(t.name, 'NOT A TABLE') AS Table_name ,
ISNULL(P.name, 'NOT A PROC') AS Proc_name ,
ISNULL(l.name, 'ORPHANED USER') AS Login_name
FROM sys.database_permissions AS dp
JOIN sys.database_principals d ON dp.grantee_principal_id = d.principal_id
JOIN sys.objects o ON o.object_id = dp.major_id
LEFT JOIN sys.tables t ON t.object_id = o.object_id
LEFT JOIN SYS.procedures P ON P.object_id = O.object_id
LEFT JOIN sys.syslogins l ON l.sid = d.sid
WHERE dp.major_id >= 0
AND d.type IN ( 'S', 'G', 'U' )
AND dp.permission_name <> 'connect'
UNION
SELECT DB_NAME(DB_ID()) AS dbname ,
dp.name account_name ,
USER_NAME(drm.role_principal_id) AS DB_Role_Member ,
' ' ,
' ' ,
' ' ,
' ' ,
' ' ,
' '
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
WHERE dp.sid NOT IN ( 0x01 )
AND dp.sid IS NOT NULL
AND dp.type NOT IN ( 'c' )
AND dp.is_fixed_role <> 1
AND dp.name NOT LIKE '##%'
AND drm.role_principal_id IS NOT NULL
)
SELECT Dbname ,
DB_ObjectName AS [DB_Object or AcctName] ,
Permission_name ,
CASE WHEN state_desc = '' THEN '--'
ELSE state_desc
END AS state_desc ,
CASE WHEN DB_User = '' THEN '--'
ELSE DB_User
END AS DB_User ,
CASE WHEN Type_Desc = '' THEN '--'
ELSE Type_Desc
END AS Type_Desc ,
CASE WHEN Table_Name = '' THEN '--'
WHEN Table_Name = 'NOT A TABLE' THEN '--'
ELSE Table_Name
END AS Table_Name ,
CASE WHEN Proc_name = '' THEN '--'
WHEN Proc_name = 'NOT A PROC' THEN '--'
ELSE Proc_name
END AS Proc_Name ,
CASE WHEN Login_Name = '' THEN '--'
ELSE Login_Name
END AS Login_Name ,
CASE WHEN Permission_name = 'Execute'
THEN 'Grant execute on [' + Proc_name + '] to [' + DB_User+']'
WHEN permission_name = 'select ' AND proc_name = 'NOT A PROC'
THEN 'Grant select on [' + DBname+ '] to [' + DB_User+ ']'
ELSE '--'
END AS Exec_stmt
FROM Perms_query
WHERE state_desc <> 'deny'
AND DB_User NOT LIKE '##%'
ORDER BY state_desc
|
|
Find who owns SQL Agent Jobs |
|
Needed to find Logins who owned any SQL Agent Jobs. We are consolidating individual logins to AD groups.
SELECT name FROM sys.syslogins
WHERE sid IN(SELECT owner_sid FROM msdb..sysjobs)
SELECT name FROM sys.syslogins
WHERE sid IN(SELECT owner_sid FROM msdb..sysjobs)
Schema Changes History Query |
|
SQL Server has a report called Schema Changes History. I wanted to figure out that query. After running a trace, talking to some folks and scouring the internet I came up with this. Really useful if you want to see who is changing stuff in your databases.
DECLARE @base_tracefilename VARCHAR(200)
SELECT @base_tracefilename = path FROM sys.traces WHERE ID =1
--SELECT @base_tracefilename
Select ObjectName
, ObjectID
, DatabaseName
, StartTime
, CASE EventClass
WHEN 46 THEN 'Created'
WHEN 47 THEN 'Deleted'
WHEN 164 THEN 'Alter'
END AS Eventclass
, EventSubClass
, CASE ObjectType
WHEN 8259 THEN 'Check Constraint'
WHEN 8260 THEN 'Default (constraint or standalone)'
WHEN 8262 THEN 'Foreign-key Constraint'
WHEN 8272 THEN 'Stored Procedure'
WHEN 8274 THEN 'Rule'
WHEN 8275 THEN 'System Table'
WHEN 8276 THEN 'Trigger on Server'
WHEN 8277 THEN '(User-defined) Table'
WHEN 8278 THEN 'View'
WHEN 8280 THEN 'Extended Stored Procedure'
WHEN 16724 THEN 'CLR Trigger'
WHEN 16964 THEN 'Database'
WHEN 16975 THEN 'Object'
WHEN 17222 THEN 'FullText Catalog'
WHEN 17232 THEN 'CLR Stored Procedure'
WHEN 17235 THEN 'Schema'
WHEN 17475 THEN 'Credential'
WHEN 17491 THEN 'DDL Event'
WHEN 17741 THEN 'Management Event'
WHEN 17747 THEN 'Security Event'
WHEN 17749 THEN 'User Event'
WHEN 17985 THEN 'CLR Aggregate Function'
WHEN 17993 THEN 'Inline Table-valued SQL Function'
WHEN 18000 THEN 'Partition Function'
WHEN 18002 THEN 'Replication Filter Procedure'
WHEN 18004 THEN 'Table-valued SQL Function'
WHEN 18259 THEN 'Server Role'
WHEN 18263 THEN 'Microsoft Windows Group'
WHEN 19265 THEN 'Asymmetric Key'
WHEN 19277 THEN 'Master Key'
WHEN 19280 THEN 'Primary Key'
WHEN 19283 THEN 'ObfusKey'
WHEN 19521 THEN 'Asymmetric Key Login'
WHEN 19523 THEN 'Certificate Login'
WHEN 19538 THEN 'Role'
WHEN 19539 THEN 'SQL Login'
WHEN 19543 THEN 'Windows Login'
WHEN 20034 THEN 'Remote Service Binding'
WHEN 20036 THEN 'Event Notification on Database'
WHEN 20037 THEN 'Event Notification'
WHEN 20038 THEN 'Scalar SQL Function'
WHEN 20047 THEN 'Event Notification on Object'
WHEN 20051 THEN 'Synonym'
WHEN 20549 THEN 'End Point'
WHEN 20801 THEN 'Adhoc Queries which may be cached'
WHEN 20816 THEN 'Prepared Queries which may be cached'
WHEN 20819 THEN 'Service Broker Service Queue'
WHEN 20821 THEN 'Unique Constraint'
WHEN 21057 THEN 'Application Role'
WHEN 21059 THEN 'Certificate'
WHEN 21075 THEN 'Server'
WHEN 21076 THEN 'Transact-SQL Trigger'
WHEN 21313 THEN 'Assembly'
WHEN 21318 THEN 'CLR Scalar Function'
WHEN 21321 THEN 'Inline scalar SQL Function'
WHEN 21328 THEN 'Partition Scheme'
WHEN 21333 THEN 'User'
WHEN 21571 THEN 'Service Broker Service Contract'
WHEN 21572 THEN 'Trigger on Database'
WHEN 21574 THEN 'CLR Table-valued Function'
WHEN 2157 THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
WHEN 21581 THEN 'Service Broker Message Type'
WHEN 21586 THEN 'Service Broker Route'
WHEN 21587 THEN 'Statistics'
WHEN 21825 THEN 'User'
WHEN 21827 THEN 'User'
WHEN 21831 THEN 'User'
WHEN 21843 THEN 'User'
WHEN 21847 THEN 'User'
WHEN 22099 THEN 'Service Broker Service'
WHEN 22601 THEN 'Index'
WHEN 22604 THEN 'Certificate Login'
WHEN 22611 THEN 'XMLSchema'
WHEN 22868 THEN 'Type'
ELSE 'Hmmm???'
END AS ObjectType
, ServerName
, LoginName
, NTUserName
, ApplicationName
from ::fn_trace_gettable( @base_tracefilename, default ) -- change default to a number to suck in additional trace files
where EventClass in (46,47,164) and EventSubclass = 0 AND DATABASEName NOT IN ('master','model','msdb','tempdb')
AND starttime > (GETDATE() -7)
DECLARE @base_tracefilename VARCHAR(200)
SELECT @base_tracefilename = path FROM sys.traces WHERE ID =1
--SELECT @base_tracefilename
Select ObjectName
, ObjectID
, DatabaseName
, StartTime
, CASE EventClass
WHEN 46 THEN 'Created'
WHEN 47 THEN 'Deleted'
WHEN 164 THEN 'Alter'
END AS Eventclass
, EventSubClass
, CASE ObjectType
WHEN 8259 THEN 'Check Constraint'
WHEN 8260 THEN 'Default (constraint or standalone)'
WHEN 8262 THEN 'Foreign-key Constraint'
WHEN 8272 THEN 'Stored Procedure'
WHEN 8274 THEN 'Rule'
WHEN 8275 THEN 'System Table'
WHEN 8276 THEN 'Trigger on Server'
WHEN 8277 THEN '(User-defined) Table'
WHEN 8278 THEN 'View'
WHEN 8280 THEN 'Extended Stored Procedure'
WHEN 16724 THEN 'CLR Trigger'
WHEN 16964 THEN 'Database'
WHEN 16975 THEN 'Object'
WHEN 17222 THEN 'FullText Catalog'
WHEN 17232 THEN 'CLR Stored Procedure'
WHEN 17235 THEN 'Schema'
WHEN 17475 THEN 'Credential'
WHEN 17491 THEN 'DDL Event'
WHEN 17741 THEN 'Management Event'
WHEN 17747 THEN 'Security Event'
WHEN 17749 THEN 'User Event'
WHEN 17985 THEN 'CLR Aggregate Function'
WHEN 17993 THEN 'Inline Table-valued SQL Function'
WHEN 18000 THEN 'Partition Function'
WHEN 18002 THEN 'Replication Filter Procedure'
WHEN 18004 THEN 'Table-valued SQL Function'
WHEN 18259 THEN 'Server Role'
WHEN 18263 THEN 'Microsoft Windows Group'
WHEN 19265 THEN 'Asymmetric Key'
WHEN 19277 THEN 'Master Key'
WHEN 19280 THEN 'Primary Key'
WHEN 19283 THEN 'ObfusKey'
WHEN 19521 THEN 'Asymmetric Key Login'
WHEN 19523 THEN 'Certificate Login'
WHEN 19538 THEN 'Role'
WHEN 19539 THEN 'SQL Login'
WHEN 19543 THEN 'Windows Login'
WHEN 20034 THEN 'Remote Service Binding'
WHEN 20036 THEN 'Event Notification on Database'
WHEN 20037 THEN 'Event Notification'
WHEN 20038 THEN 'Scalar SQL Function'
WHEN 20047 THEN 'Event Notification on Object'
WHEN 20051 THEN 'Synonym'
WHEN 20549 THEN 'End Point'
WHEN 20801 THEN 'Adhoc Queries which may be cached'
WHEN 20816 THEN 'Prepared Queries which may be cached'
WHEN 20819 THEN 'Service Broker Service Queue'
WHEN 20821 THEN 'Unique Constraint'
WHEN 21057 THEN 'Application Role'
WHEN 21059 THEN 'Certificate'
WHEN 21075 THEN 'Server'
WHEN 21076 THEN 'Transact-SQL Trigger'
WHEN 21313 THEN 'Assembly'
WHEN 21318 THEN 'CLR Scalar Function'
WHEN 21321 THEN 'Inline scalar SQL Function'
WHEN 21328 THEN 'Partition Scheme'
WHEN 21333 THEN 'User'
WHEN 21571 THEN 'Service Broker Service Contract'
WHEN 21572 THEN 'Trigger on Database'
WHEN 21574 THEN 'CLR Table-valued Function'
WHEN 2157 THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
WHEN 21581 THEN 'Service Broker Message Type'
WHEN 21586 THEN 'Service Broker Route'
WHEN 21587 THEN 'Statistics'
WHEN 21825 THEN 'User'
WHEN 21827 THEN 'User'
WHEN 21831 THEN 'User'
WHEN 21843 THEN 'User'
WHEN 21847 THEN 'User'
WHEN 22099 THEN 'Service Broker Service'
WHEN 22601 THEN 'Index'
WHEN 22604 THEN 'Certificate Login'
WHEN 22611 THEN 'XMLSchema'
WHEN 22868 THEN 'Type'
ELSE 'Hmmm???'
END AS ObjectType
, ServerName
, LoginName
, NTUserName
, ApplicationName
from ::fn_trace_gettable( @base_tracefilename, default ) -- change default to a number to suck in additional trace files
where EventClass in (46,47,164) and EventSubclass = 0 AND DATABASEName NOT IN ('master','model','msdb','tempdb')
AND starttime > (GETDATE() -7)
schema_change_history.sql | |
File Size: | 4 kb |
File Type: | sql |
Disabled User |
|
I had a disabled user. Tried to re-enable the user but no button via the GUI to do that. Scoured the internet and found this solution.
Grant CONNECT to <user name>
Grant CONNECT to <user name>
Grant Permissions to Create Stored Procedures |
|
Need to give user permissions to create stored procedures
GRANT CREATE PROCEDURE TO [domain\user]
GRANT ALTER ON SCHEMA :: schema_name TO [domain\user]
GRANT CREATE PROCEDURE TO [domain\user]
GRANT ALTER ON SCHEMA :: schema_name TO [domain\user]
Change DB Owner |
|
needed to change db_owner. used the new syntax.
alter authorization on database::DBNameto [Domain\Login Name ]
alter authorization on database::DBNameto [Domain\Login Name ]
Orphaned Users and how to find and fix!! |
|
Used to work on an app that created a new sql authenticated login for each user. Spent a lot of times when refreshing the Dev environment and trying to fix all the orphaned users. Total beat down. So this uses ms_foreachDB. Don't hate me.
IF OBJECT_ID('tempdb..#orphans') is NOT NULL
DROP TABLE #orphans
GO
--create temp table to store oprhaned users
CREATE TABLE #orphans
(dbname VARCHAR(50),
db_user VARCHAR(50))
go
--loop through all DBs searching for user in sys.database_principals whose SID has no match in sys.server_principals
EXEC sp_msforeachdb
'USE [?]; insert into #orphans(dbname, db_user)
SELECT ''[?]'' AS DBname, dp.name from sys.database_principals dp
where dp.type <>''r'' and dp.name <> ''guest'' and
dp.sid not in
(
select sid from sys.server_principals
) '
go
--generate orphaned users syntax
-- copy orphaned_user_fix column and run
SELECT * ,
'USE ' + #orphans.DBNAME + ';' + ' exec sp_change_users_login '
+ '''update_one''' + ',' + '''' + #orphans.db_user + '''' + ',' + ''''
+ #orphans.db_user + '''' AS orphaned_user_fix
FROM #orphans
--JOIN sys.syslogins l ON #orphans.db_user = l.name -- will return only accounts the are being used.
IF OBJECT_ID('tempdb..#orphans') is NOT NULL
DROP TABLE #orphans
GO
--create temp table to store oprhaned users
CREATE TABLE #orphans
(dbname VARCHAR(50),
db_user VARCHAR(50))
go
--loop through all DBs searching for user in sys.database_principals whose SID has no match in sys.server_principals
EXEC sp_msforeachdb
'USE [?]; insert into #orphans(dbname, db_user)
SELECT ''[?]'' AS DBname, dp.name from sys.database_principals dp
where dp.type <>''r'' and dp.name <> ''guest'' and
dp.sid not in
(
select sid from sys.server_principals
) '
go
--generate orphaned users syntax
-- copy orphaned_user_fix column and run
SELECT * ,
'USE ' + #orphans.DBNAME + ';' + ' exec sp_change_users_login '
+ '''update_one''' + ',' + '''' + #orphans.db_user + '''' + ',' + ''''
+ #orphans.db_user + '''' AS orphaned_user_fix
FROM #orphans
--JOIN sys.syslogins l ON #orphans.db_user = l.name -- will return only accounts the are being used.
Proxy Account for the SQL Agent |
|
I need to run some Powershell scripts using my account via the SQL Agent. I have specific modules I needed loaded from my profile that the Agent didnt have. Maybe there is another way to do this but this worked. Here is how to setup a proxy.
First set up a credential.
First set up a credential.
Enter the Identity. How this proxy will be connecting as.
Next create the Proxy and select the credential name and check which subsystem the proxy with interact with.
Set the SQL Agent job to use the proxy.
there you go on how to setup a proxy.!!
SQL Login disabled but that Login is a part of an AD Group on the Instance
So maybe this is me being stupid, but I had a Login that I disabled. This login is also in a part of an AD group set up on the instance. I know why have a login that is a part of an AD group set up as a standalone login. Basic answer is, it's my bad.
I was doing some cleanup of logins making sure all my logins are AD groups for easier managment. I disabled the login, knowing he was in an AD group and now the user can't login. So I delete the single login and now the user has access being that he is authenticating through the AD group. So a login on a sql instance will trump the AD group if you disable the login.
I was doing some cleanup of logins making sure all my logins are AD groups for easier managment. I disabled the login, knowing he was in an AD group and now the user can't login. So I delete the single login and now the user has access being that he is authenticating through the AD group. So a login on a sql instance will trump the AD group if you disable the login.
Had to remove 978 Linked Servers...What!!!! |
|
Today I had to remove 978 linked servers. Crazy right. They all had SA perms. Here is how I did it.
Enjoy!!!
/* declare variables */
DECLARE @name VARCHAR(100)
DECLARE @sql VARCHAR(1000)
DECLARE linked_server CURSOR FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.servers WHERE name LIKE '10.%'
OPEN linked_server
FETCH NEXT FROM linked_server INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'exec sp_dropserver @Server = '+''''+@name+''''+', @droplogins = '+''''+'droplogins'+''''+';'
PRINT @sql
--EXEC (@sql)
FETCH NEXT FROM linked_server INTO @name
END
CLOSE linked_server
DEALLOCATE linked_server
Enjoy!!!
/* declare variables */
DECLARE @name VARCHAR(100)
DECLARE @sql VARCHAR(1000)
DECLARE linked_server CURSOR FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.servers WHERE name LIKE '10.%'
OPEN linked_server
FETCH NEXT FROM linked_server INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'exec sp_dropserver @Server = '+''''+@name+''''+', @droplogins = '+''''+'droplogins'+''''+';'
PRINT @sql
--EXEC (@sql)
FETCH NEXT FROM linked_server INTO @name
END
CLOSE linked_server
DEALLOCATE linked_server
Using the DAC aka Dedicated Admin Connection !!!! |
|
Today, the SA account was saying it was locked out. Made no sense. There was no indication that SA was locked out. Tried several things.
Here are the steps...
- Bouncing SQL....didn't work
- DAC from SSMS....didn't work
- Made sure port 1434 was open, it wasn't so enabled it and tried to access via the DAC....didn't work
Here are the steps...
- Open a CMD terminal
- Navigate to C:\
- type SQLCMD -S <enter your servername> -U SA -P <your SA password> -A
- Press Enter
- At line one enter what ever TSQL command you need to execute
- press enter
- Type Go
- Press enter
Security Design Principles or Security Design Philosophy |
|
I have always heard of the Principle of Lease Privilege, but was unaware of these others. Good stuff. Very useful.
www.mlakartechtalk.com/infosec-design-principles-8-security-principles-to-implement/#more-2247
www.mlakartechtalk.com/infosec-design-principles-8-security-principles-to-implement/#more-2247
Create Schema Permissions and Alter Tables in a Schema |
|
Dev needed to create tables in a schema and also create new schemas
GRANT ALTER ON SCHEMA :: reporting TO [user]
GRANT CREATE SCHEMA TO [user]
GRANT ALTER ON SCHEMA :: reporting TO [user]
GRANT CREATE SCHEMA TO [user]