Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trying to build a stored procedure with insert into... Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 8:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:12 PM
Points: 987, Visits: 5,162
OK, to simplify some annual auditing of DB users (not the SQL logins,) I'm trying to craft a stored procedure that the customer on the server (they're the only customer on this particular server) can run to get a listing of all DB users and what roles they have.

I've got a query that returns this for the currently selected DB, so that part's done.

I can use SP_MSFOREACHDB to run it against each DB, with the results going into a temp table to make it easier to copy/paste into an Excel file.

What I want to do, and can't seem to see how, is wrap the whole thing in yet another SP of my own, with an EXECUTE AS so that the customer doesn't need sysadmin or any special privileges on the server. When I do this, it runs, but only against master.

Now, from digging it looks like you can't have an "insert #temptable exec sp_whatever" inside another SP. I'd like to avoid dynamic SQL, and while I know there are problems with MSFOREACHDB, it'll work for what we need.

I have, at least, found how to turn a user created SP, into a system SP so it can be run regardless of the DB you've selected, so at least there's that.

Thanks,
Jason
Post #1559980
Posted Wednesday, April 9, 2014 11:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
As an example (clearly not your exact query), would something like this not work?

CREATE PROCEDURE Test1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
CREATE TABLE #Names
(
DBName VARCHAR(MAX),
TableName VARCHAR(MAX)
)

INSERT INTO #Names (DBName, TableName) EXEC sp_MSforeachdb 'USE ?; SELECT ''?'', [name] FROM sys.tables'

SELECT * FROM #Names
END
GO

ALTER PROCEDURE Test2
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

EXEC Test1
END
GO

Post #1560110
Posted Wednesday, April 9, 2014 7:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:12 PM
Points: 987, Visits: 5,162
I meant to post this much earlier, but the network at work gets flaky sometimes...

Here's the query I was using to create the SP (scrubbed the user name, and skipping the step to make it a system SP)

use [master];
go

create procedure dbo.sp_DBRoleAudit
with execute as 'domain\UserWithSysAdmin'
as

create TABLE #DB_USers (
DBName sysname
, UserName sysname
, LoginType sysname
, AssociatedRole varchar(max)
,create_date datetime
,modify_date datetime
)

INSERT #DB_USers
EXEC sp_MSforeachdb
'use [?]
SELECT ''?'' AS DB_Name
,case prin.name when ''dbo''
then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')''
else prin.name
end AS UserName
,prin.type_desc AS LoginType
,isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole
,create_date,modify_date
FROM sys.database_principals as prin
LEFT OUTER JOIN sys.database_role_members as mem
ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL
and prin.sid NOT IN (0x00)
and prin.is_fixed_role <> 1
AND prin.name NOT LIKE ''##%'''

SELECT dbname as [DBName]
,username as [UserName]
,logintype as [LoginType]
,create_date as [CreateDate]
,modify_date as [ModifyDate]
, STUFF( ( SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
FROM #DB_USers as user2
WHERE user1.DBName=user2.DBName
AND user1.UserName=user2.UserName FOR XML PATH('') ) ,1,1,'') AS [Permissions_user]
FROM #DB_USers as user1
GROUP BY dbname
,username
,logintype
,create_date
,modify_date
ORDER BY DBName
,username

drop table #DB_USers;

Post #1560221
Posted Thursday, April 10, 2014 7:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
I'm still not sure what the problem is. If I create a new stored procedure which executes that one, everything works fine...
Post #1560430
Posted Thursday, April 10, 2014 9:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:12 PM
Points: 987, Visits: 5,162
Hmmm.

The issue I'm running into is, it's only executing the sp_msforeachdb once.

Maybe different versions of SQL?

Me: SQL 2008 R2 SP2
Post #1560502
Posted Thursday, April 10, 2014 9:54 AM This worked for the OP Answer marked as solution
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
Okay, my bad, now I see where the problem is. It seems like it's centered around the "WITH EXECUTE AS ___" part.

The problem is easy to identify - just change the procedure to become "SELECT * FROM sys.databases", if you remove the WITH EXECUTE AS, you'll see all the databases, but if you keep it, you will only see a subset.

Take a look at this article on MSDN:

http://technet.microsoft.com/en-us/library/ms178534.aspx

Specifically, this part:

"If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases."

Take a look at this thread:

https://groups.google.com/forum/#!topic/microsoft.public.sqlserver.security/GO5CBzHZ4vY

See the responses given by Erland Sommarskog. Hopefully they should answer your questions.
Post #1560517
Posted Thursday, April 10, 2014 11:06 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:12 PM
Points: 987, Visits: 5,162
OK, I think that might be the problem. Although I tried with my domain account as the "execute as" user, and it did the same thing. My account is a member of sysadmin, so I would've expected it to work.

Hmmm.
Post #1560547
Posted Thursday, April 10, 2014 11:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:12 PM
Points: 987, Visits: 5,162
OK, just started in on the Google thread, and think I see why this isn't working. Not sure I'm going to be able to get it to work, not in the security environment I work in...

Sounds like whatever user I choose to use for the "EXECUTE AS" needs to be a DB login as well, for each DB. My sysadmin account doesn't exist as a DB login, because hey, it's SA!

Ditto for the other account I've been trying to use.
Post #1560552
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse