Get Users and Objects

Transact-SQL
1

GRANT EXECUTE ON [sys].[sp_OACreate] TO [TestUser]

Once I run the above, how can I check which users have permission to run [sys].[sp_OACreate] as well as vice versa I would like to check what procedure has been granted to the user TestUser.

Thanks

2

The following queries should get you started:

SELECT P.state_desc COLLATE DATABASE_DEFAULT + ' '
	+ P.[permission_name] COLLATE DATABASE_DEFAULT + ' ON '
	+ QUOTENAME(S.[name] COLLATE DATABASE_DEFAULT)
	+ '.' + QUOTENAME(O.[name] COLLATE DATABASE_DEFAULT)
	+ COALESCE('(' + QUOTENAME(C.[name] COLLATE DATABASE_DEFAULT) + ')', '')
	+ ' TO ' + QUOTENAME(U.name COLLATE DATABASE_DEFAULT) + ';'
FROM sys.database_permissions P
	JOIN sys.sysusers U
		ON P.grantee_principal_id = U.uid
	JOIN sys.objects O
		ON P.major_id = O.object_id
	JOIN sys.schemas S
		ON O.[schema_id] = S.[schema_id]
	LEFT JOIN sys.columns C
		ON O.object_id = C.[object_id]
			AND P.minor_id = C.column_id
ORDER BY U.[name], S.[name], O.[name], C.[name];

SELECT 'ALTER ROLE ' + R.[name] + ' ADD MEMBER ' + U.[name] + ';'
FROM sys.database_principals U
	JOIN sys.database_role_members X
		ON X.member_principal_id = U.principal_id
	JOIN sys.database_principals R
		ON R.principal_id = X.role_principal_id
WHERE U.[name] <> 'dbo';
1 Like
3

I appreciate you took the time to reply. I used this statement to grant this system SP to a number of users.

GRANT EXECUTE ON [sys].[sp_OACreate] TO Users

I am now trying which users on this server have access to this system SP.

I ran your code above and was not able to track those users down.

Thanks

4

Start by looking at sys.database_permissions in the master database. The Microsoft documentation is not difficult to look up. (Tip: Can you find sp_OACreate in sys.objects? What other view could it be in?)

ps Personally I would avoid the OLE automation procedures. If you really have to do something like this in SQL Server then look at the SQLCLR instead.

1 Like
5

I agree with @Ifor on this, stay away from OLE automation

6

I tried to look for sp_OACreate in the master database but was not able to locate it.

Anyway, I have been planning to go toward the direction of SQLCLR and I have gotten several DLLs working. Although this will be the taken route, could you share your thoughts on your reasons avoiding the OLE automation procedures? Thanks

1 Like
7

Really! The following is well documented:

use [master];
go
-- User objects
select *
from sys.objects
where [name] = 'sp_OACreate';
go
-- System objects
select *
from sys.system_objects
where [name] = 'sp_OACreate';
go
-- All objects. Depreciated but still available in SQL2019.
select *
from sys.sysobjects
where [name] = 'sp_OACreate';
go
use [model];
-- System objects
select *
from sys.system_objects
where [name] = 'sp_OACreate';
go
-- All objects. Depreciated but still available in SQL2019.
select *
from sys.sysobjects
where [name] = 'sp_OACreate';
go

I suspect 99 times out of a hundred one should try and put this sort of logic in the middle tier and leave the database to do what it is good at.

They have not really been developed since SQL2000, have a reputation for being buggy and who knows what memory/security issues are in the actual OLE objects. There is a reason they are not automatically assigned to the public role. Do you really want this on a database server?

1 Like
8

Sorry, there are a bunch of things that I did not understand and therefore, I asked on here. I was searching for sp_OACreate through SSMS.

9

Not to mention the fact that it is entirely too easy to do something badly that crashes SQL Server.

Plus - there are other (better?) methods for accomplishing any task that needs to use OLE automation and - in general - no reason to execute those processes from with SQL Server.

1 Like
10

like python or powershell and SSIS