?
Solved

useing USE within UNION

Posted on 2011-09-15
7
Medium Priority
?
236 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
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 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