[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

useing USE within UNION

Posted on 2011-09-15
7
Medium Priority
?
235 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
Technology Partners: 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!

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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