April 9, 2014 at 8:25 am
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
April 9, 2014 at 11:21 am
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
April 9, 2014 at 7:19 pm
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;
April 10, 2014 at 7:35 am
I'm still not sure what the problem is. If I create a new stored procedure which executes that one, everything works fine...
April 10, 2014 at 9:28 am
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
April 10, 2014 at 9:54 am
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.
April 10, 2014 at 11:06 am
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.
April 10, 2014 at 11:15 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply