August 17, 2012 at 10:42 am
Hello All,
I have a question related to a query. Just thinking in different way to see whether it works or not.
The following program basically restricts the user to do Insert,update,delete on management studio only for a SINGLE table. Instead of creating this kind of program on each and every single table, I am thinking to add a stored procedure by declaring a variable name.where it takes all the table names and gives the variable name to trigger where it triggers the functionality on every table.
So, my question, Is it possible to send a stored procedure variable to trigger and if so how. I am not as good with programming but still need advice from experts. Please help. Thanks
CREATE TRIGGER TestTable_Tr
ON TestTable
for INSERT, UPDATE,Delete
as
BEGIN
DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)
SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid
IF ORIGINAL_LOGIN() IN('TestUser')
AND @program_name LIKE '%Management%Studio%'
BEGIN
RAISERROR('This login is for application use only.',16,1)
ROLLBACK;
END
END;
--Now adding Stored procedure CHANGES REQUIRED FOR THIS PROGRAM
CREATE PROCEDURE [dbo].Up_TestProc
@name CHAR(9)
AS
BEGIN
SET NOCOUNT ON;
SELECT @name = name FROM sys.tables
CREATE TRIGGER TestTable_Tr
ON @name ---Is it possible
for INSERT, UPDATE,Delete
as
BEGIN
DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)
SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid
IF ORIGINAL_LOGIN() IN('TestUser')
AND @program_name LIKE '%Management%Studio%'
BEGIN
RAISERROR('This login is for application use only.',16,1)
ROLLBACK;
END
END;
END
August 17, 2012 at 11:22 am
Why would you like a Stored Procedure? If this is for administration, you should do this task once.
I'm not sure if this is the best solution, you should try to solve it using permissions.
If you insist on doing this, you need to do it by using Dynamic code
August 17, 2012 at 12:09 pm
Well I have a Scenario: We have users who requires db_datareader and db_datawriter permissions to run there front end applications. We are using windows authentication as access permissions. Now the same users have SSMS on there desktops. So, when they connect to database directly through ssms they might corrupt the data. So, to avoid this we are thinking to create a trigger as when they access DB through SSMS they permissions goes to db_datareader, but whil through application it should remain as db_datareader and db_datawriter. So, i am trying in different scenarios here.
August 17, 2012 at 12:45 pm
Did you read the links I posted? (Application Roles, EXECUTE and sp_executesql)
My suggestion is to use the Application Roles and remove permissions to the logins.
However, If you want to create triggers for every table in the database, you need to construct your query to generate dynamic code. You'll have to be sure to change the trigger name together with the table name and be sure not to override existing triggers. You could also try an undocumented SP named sp_msforeachtable (search on the web for examples and all documentation possible and TEST BEFORE USING IT).
Remember to test before doing this major changes to your production environments.
August 17, 2012 at 12:46 pm
DBA_SQL (8/17/2012)
Well I have a Scenario: We have users who requires db_datareader and db_datawriter permissions to run there front end applications. We are using windows authentication as access permissions. Now the same users have SSMS on there desktops. So, when they connect to database directly through ssms they might corrupt the data. So, to avoid this we are thinking to create a trigger as when they access DB through SSMS they permissions goes to db_datareader, but whil through application it should remain as db_datareader and db_datawriter. So, i am trying in different scenarios here.
There are so many things here that make me want to crawl under my desk. Why do you have users that are not competent with sql that have SSMS installed on their desktops? If they can't be trusted with SSMS get it off their desktop.
If for some reason this doesn't work then you should look at a logon trigger instead of trying to dynamically create triggers on a bunch of tables.
You can't create a trigger inside of a proc (unless you use dynamic sql). Your code is trying to create a trigger on a variable. You can't do that. If the contents of the variable contains the table name, you still need to use dynamic sql. Nowhere in your code do you check to see if the trigger already exists, the name of the trigger is not unique (concurrency would be a problem here) and you don't drop the trigger.
The trigger approach just sounds like a disaster waiting to happen.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 17, 2012 at 1:02 pm
Sean Lange (8/17/2012)
Why do you have users that are not competent with sql that have SSMS installed on their desktops? If they can't be trusted with SSMS get it off their desktop.
At least at the company I'm working for, users from the finance department have SSMS installed to make "simple queries". They only have read access to limited tables. However, one thing is analysis and another thing is capturing the information, it shouldn't be both.
August 17, 2012 at 1:14 pm
Luis Cazares (8/17/2012)
Sean Lange (8/17/2012)
Why do you have users that are not competent with sql that have SSMS installed on their desktops? If they can't be trusted with SSMS get it off their desktop.At least at the company I'm working for, users from the finance department have SSMS installed to make "simple queries". They only have read access to limited tables. However, one thing is analysis and another thing is capturing the information, it shouldn't be both.
The last thing I would want is a bunch of untrained people writing "simple" queries on a product database. An accidental cross join on a couple of tables with a few million rows and your system is toast until somebody kills the process. We have a copy of production data that we keep around for this type of thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 17, 2012 at 2:02 pm
No one said they were working on a production database.
I believe that most problems like this should be solved with other administration options and not triggers. I wouldn't say you shouldn't write more than five triggers in your life (as someone else says) but they should not be the first option for every solution.
August 17, 2012 at 2:16 pm
Luis Cazares (8/17/2012)
No one said they were working on a production database.I believe that most problems like this should be solved with other administration options and not triggers. I wouldn't say you shouldn't write more than five triggers in your life (as someone else says) but they should not be the first option for every solution.
True enough, I made the assumption about production because if it was a sandbox it wouldn't really matter if the data got corrupted. 😉
We are absolutely in agreement that the dynamic approach to this is probably not a good one.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply