SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to build a stored procedure with insert into...


Trying to build a stored procedure with insert into...

Author
Message
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 11749
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
kramaswamy
kramaswamy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 1803
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


jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 11749
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;


kramaswamy
kramaswamy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 1803
I'm still not sure what the problem is. If I create a new stored procedure which executes that one, everything works fine...
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 11749
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
kramaswamy
kramaswamy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 1803
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.
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 11749
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.
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 11749
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search