January 7, 2010 at 11:37 am
I have a table named LSA_COMMAND_PERMISSION that an application uses that has 3 columns ROLE_NAME, COMMAND_ID, PERMISSION based on what the user's ROLE_NAME is determines what access the user has. If the user has the SUPERUSER role that has access to everything they will not have any rows in this table, if a users has read_only or none access to certain accounting functions the rows will appear in this table. Code used to create attached resultsset superuser.png
USE DBNAME
SELECT ROLE_NAME, COMMAND_ID as 'Command ID', PERMISSION_TYPE as 'Permission'
FROM LSA_COMMAND_PERMISSION
where role_name = 'SUPERUSER'
Used to creat the 541_CFO.PNG
USE DBNAME
SELECT ROLE_NAME, COMMAND_ID as 'Command ID', PERMISSION_TYPE as 'Permission'
FROM LSA_COMMAND_PERMISSION
where role_name = 541_CFO'
I am really stuggling with joins and subqueries.
Ed Mitchell
January 7, 2010 at 1:00 pm
It appears that you are attempting to create a facility that already exists in SQL Server 2008 .... might I suggest that you use Books On Line (SQL help file) to famaliarize yourself with what already exists.
Starting here:
http://msdn.microsoft.com/en-us/library/ms189121.aspx
And for more details
http://msdn.microsoft.com/en-us/library/ms187936.aspx
Then read and understand the use of SCHEMAS.
Using what is already available will in the future make additions/deletions/changes much easier.
Now if the above is not of help to you, please post the table(s) definitions, along with some sample data for each table, and your desired output .... to help us help you
January 7, 2010 at 2:06 pm
I am using SQL 2005
Here is the table definitions:
CREATE TABLE [dbo].[LSA_COMMAND_PERMISSION](
[RECORD_IDENTITY] [varchar](12) NOT NULL,
[RECORD_CREATED] [datetime] NULL,
[RECORD_MODIFIED] [datetime] NULL,
[RECORD_USER] [varchar](20) NULL,
[RECORD_MODIFY_USER] [varchar](20) NULL,
[RECORD_VERSION] [datetime] NULL,
[ROLE_NAME] [varchar](20) NOT NULL,
[INSTANCE_NAME] [varchar](30) NOT NULL,
[COMMAND_ID] [varchar](128) NOT NULL,
[PERMISSION_TYPE] [varchar](32) NULL,
Your response was more about sql roles not this particular applications roles these are created and defined from within the application, I failed to attach the correct 541_CFO.png file which I have attached this time. I have also attached the columns and the data from the table that I am interested.
January 7, 2010 at 4:54 pm
ed.mitchell
I am using SQL 2005
1. You have posted to a SQL Server 2008 Forum.
2. You have posted the definition of a single table - good
3. The sample data presented is contained within an Excel spreadsheet, and I do not have the inclination to go thorough all 6,890 rows of the spread sheet to extract sample data so selected just a few rows which I thought were sufficient.
The article in my signature block clearly shows how to present a sample of data in the easily consumable format for those who wish to help you i.e., like shown in the next 2 lines:
SELECT xxx, aaaa, bbb UNION ALL
SELECT yyy,bbb,ccc UNION ALL
4. You did not include the expected result from a particular input.
That said I composed the following test (Reduced the table definition to accomodate the data provided):
CREATE TABLE [dbo].[#LSA_COMMAND_PERMISSION](
[ROLE_NAME] [varchar](20) NOT NULL,
[COMMAND_ID] [varchar](128) NOT NULL,
[PERMISSION_TYPE] [varchar](32) NULL)
INSERT INTO #LSA_COMMAND_PERMISSION
SELECT '541_CFO','Lsa.Vfin.Ledger.Forms.AccountCategoryForm','READONLY' UNION ALL
SELECT '541_CFO','Lsa.Vfin.Ledger.Forms.ApportionmentForm','NONE' UNION ALL
SELECT 'ICX_SUPERUSER','Lsa.Vfin.CashMgmt.Forms.BankAdjustmentForm','NONE'
SELECT ROLE_NAME, COMMAND_ID as 'Command ID', PERMISSION_TYPE as 'Permission'
FROM #LSA_COMMAND_PERMISSION
where role_name LIKE '%SUPERUSER%'
Result from above T-SQL
ROLE_NAMECommand ID Permission
ICX_SUPERUSERLsa.Vfin.CashMgmt.Forms.BankAdjustmentFormNONE
SELECT ROLE_NAME, COMMAND_ID as 'Command ID', PERMISSION_TYPE as 'Permission'
FROM #LSA_COMMAND_PERMISSION
where role_name = '541_CFO'
Result from above T-SQL
ROLE_NAMECommand ID Permission
541_CFOLsa.Vfin.Ledger.Forms.AccountCategoryForm READONLY
541_CFOLsa.Vfin.Ledger.Forms.ApportionmentForm NONE
SELECT ROLE_NAME, COMMAND_ID as 'Command ID', PERMISSION_TYPE as 'Permission'
FROM #LSA_COMMAND_PERMISSION where role_name = 'SUPERUSER'
No results returned from the above T_SQL
A variation on the theme
SELECT ROLE_NAME, COMMAND_ID as 'Command ID', PERMISSION_TYPE as 'Permission'
FROM #LSA_COMMAND_PERMISSION
where role_name = '541_CFO' AND PERMISSION_Type = 'READONLY'
Results returned:
ROLE_NAMECommand ID Permission
541_CFOLsa.Vfin.Ledger.Forms.AccountCategoryFormREADONLY
Did I extract the correct sample data, and is this what you were expecting as your results?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply