?
Solved

useing USE within UNION

Posted on 2011-09-15
7
Medium Priority
?
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 1000 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 1000 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

Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

800 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