November 6, 2015 at 7:12 am
Dear All,
I'm trying to get some information from the master database about more than 1 user database and I wanted to know how I would be able to achieve this please?
Thank you in advance!
November 6, 2015 at 7:13 am
What information do you need?
November 10, 2015 at 5:07 am
Thank you for your reply!
I'm trying to get some information on permissions on each databases on the server but I'm trying to get a way of retrieving the information in one script but I'm unable to.
Thank you!
November 10, 2015 at 5:21 am
tt-615680 (11/10/2015)
Thank you for your reply!I'm trying to get some information on permissions on each databases on the server but I'm trying to get a way of retrieving the information in one script but I'm unable to.
Thank you!
I've not had the need to do such a thing but rumor has it that if you register the servers all in one qroup and start a query window on that group, the query will run on all servers. You can also use sp_MSForEachDB on a single server. If using an undocumented stored procedure bugs you, write a loop to step through each database avoiding those that you might not want to include.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2015 at 5:34 am
OK, here's my "whole server" permissions script
IF OBJECT_ID('[tempdb].[dbo].[#TMP]') IS NOT NULL
DROP TABLE [dbo].[#TMP]
CREATE TABLE [dbo].[#TMP] (
[DBName] SYSNAME,
[Type] VARCHAR(20),
[Object/Role] SYSNAME,
[Login] SYSNAME,
[Access] VARCHAR(20),
[Permission_Name] VARCHAR(20)
)
USE MASTER
declare
@isql nvarchar(max)
select @isql = (SELECT 'USE ' + QUOTENAME(name) + ';' + CHAR(13)+CHAR(10)+
'insert into #tmp ' + CHAR(13)+CHAR(10)+
'select db_name() AS DBName, ''object'' as [Type],isnull(object_name(major_id),class_desc) AS [Object/Role],pr.name AS [Login], state_desc as [Access], permission_name ' + CHAR(13)+CHAR(10)+
'from sys.database_permissions pe ' + CHAR(13)+CHAR(10)+
'inner join sys.database_principals pr ' + CHAR(13)+CHAR(10)+
'on pe.grantee_principal_id = pr.principal_id ' + CHAR(13)+CHAR(10)+
'union ' + CHAR(13)+CHAR(10)+
'select ' + CHAR(13)+CHAR(10)+
'db_name(), ''role'',' + CHAR(13)+CHAR(10)+
'pr1.name as RoleName, ' + CHAR(13)+CHAR(10)+
'pr2.name as MemberName, ' + CHAR(13)+CHAR(10)+
'''GRANT'',''''' + CHAR(13)+CHAR(10)+
'from sys.database_role_members drm ' + CHAR(13)+CHAR(10)+
'inner join sys.database_principals pr1 ' + CHAR(13)+CHAR(10)+
'on drm.role_principal_id = pr1.principal_id ' + CHAR(13)+CHAR(10)+
'inner join sys.database_principals pr2 ' + CHAR(13)+CHAR(10)+
'on drm.member_principal_id = pr2.principal_id ' + CHAR(13)+CHAR(10)
FROM sys.databases WHERE state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
print @isql
execute sp_executesql @isql
SELECT * FROM #tmp
drop table #TMP
November 11, 2015 at 2:23 am
Sorry to be a pain but I have the following script but I'm unable to get the result, would you please let me know where I'm doing wrong:
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; '
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''))
BEGIN
sql script
END
November 11, 2015 at 3:02 am
I believe the correct syntax is
--EXECUTE master.sys.sp_MSforeachdb 'USE [?]; <SQL CODE>'
--EG
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'USE [?];IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''''))
BEGIN
--sql script
END'
EXECUTE master.sys.sp_MSforeachdb '+@SQL
It depends on how long your TSQL script it, as I dont know if the sp_MSForEachdb proc takes a VARCHAR(max) or if its limited to an 8000 byte varchar.
The other option is to do something like Anthony posted and bypass the use of this undocumented feature, as MS could remove or disable it.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 11, 2015 at 10:27 am
I have tried "foreachdb but I'm unable to get any results at all, all I get is "Command completed successfully"
running the following script:
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
'DECLARE @SQL VARCHAR(8000)
SET @SQL = 'USE [?];
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''''))
BEGIN
select
*
from
sys.server_principals
END
EXECUTE master.sys.sp_MSforeachdb '+@SQL
November 11, 2015 at 11:45 am
tt-615680 (11/11/2015)
I have tried "foreachdb but I'm unable to get any results at all, all I get is "Command completed successfully"running the following script:
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
'DECLARE @SQL VARCHAR(8000)
SET @SQL = 'USE [?];
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''''))
BEGIN
select
*
from
sys.server_principals
END
EXECUTE master.sys.sp_MSforeachdb '+@SQL
Try the following:
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'USE [?];
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''''))
BEGIN
select
*
from
sys.server_principals
END'
EXECUTE master.sys.sp_MSforeachdb @SQL
I moved the closing quote before the call to the procedure.
It looked like you were trying to call sp_MSforeachdb within a call to sp_MSforeachdb. You really don't want to do that, because it will run the inner query n2 times, where n is the number of databases that you have on that server.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 11, 2015 at 11:49 am
I just realized that you're only running the query if a table with an empty name exists. You CANNOT create a table with an empty name, so your query will never produce results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply