Solved

useing USE within UNION

Posted on 2011-09-15
7
227 Views
Last Modified: 2012-05-12
is it possible to make the below syntax work?
USE DEC_LED
SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id) 
FROM sys.database_permissions dp JOIN sys.database_principals p 
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
union
USE DEC_HED
SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id) 
FROM sys.database_permissions dp JOIN sys.database_principals p 
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
union
USE DEC_TPE
SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id) 
FROM sys.database_permissions dp JOIN sys.database_principals p 
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'

Open in new window

0
Comment
Question by:25112
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 2

Expert Comment

by:akku101
ID: 36544767
Check this one


SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id)
FROM sys.database_permissions dp JOIN sys.database_principals p
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
union all

SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id)
FROM sys.database_permissions dp JOIN sys.database_principals p
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
union all

SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id)
FROM sys.database_permissions dp JOIN sys.database_principals p
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
0
 
LVL 2

Expert Comment

by:akku101
ID: 36544785
sorry above wont work as it referencing
 different databases. i missed database names.
0
 
LVL 25

Expert Comment

by:jogos
ID: 36545501
You can put the database name before the tablename

from FROM DEC_LED.sys.database_permissions
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:25112
ID: 36546682
then db_name() does not pick up automatically.. i would like that to be dynamic..
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 36547145
>>i would like that to be dynamic.. <<
Then you will have no choice, but to resort to dynamic SQL.
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 250 total points
ID: 36547391
db_name() indeed only returns the name of the current db.

Same you have to refere to a function in the actual db       dbname.schema.function . Making your own function can help.

You can also create a view in each database with the db-specific things like db_name() and maybe other stuff you want to.  Joining them in the db_name() is then that off the database where the view is.
0
 
LVL 5

Author Comment

by:25112
ID: 36548990
ok-
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now