Admin Account for SQL Server

[john98390]

I work on an (SQl) db that is fairly complex allowing access to data on various tables at three different levels of users. IE user "A" cannot see user "B's" date. The db is accessed via the internet and the data is returned to the client browser in standard html tables.

I need to set up an admin account that can access ALL the data on ALL the tables without writing a million and one queries to do so. Would there possibly be a "trick" that might involve a single query and or stored procedure that I could possibly paste onto each of the various pages(about 60 pages)??

Appreciate any help you might provide here.

Regards,
John

 

[tlbroadbent]

You can use views or stored procedures to achieve Row Level Security. I assume the table(s) used in the application have column(s) that identify the user's data.

Example: Each row contains a UserID column
Create View vTableName As
[tt]
Select * From TableName
Where UserID=User_ID() --Select user's rows
--DBO can view all rows
Or User_ID()=1
--User John can view all rows
Or User_Name()='John'
[/tt]
Example: Each row contains a UserName column
Create View vTableName As
[tt]
Select * From TableName
Where UserName=User_Name() --Select user's rows
--DBO can view all rows
Or User_ID()=1
--User John can view all rows
Or User_Name()='John'
[/tt]
Example: Each row contains an Organization column
Create View vTableName As
[tt]
Select * From TableName
Where (User_Name()='mary' And Orgn In (1, 5, 9))
Or (User_Name()='felix' And Orgn In (3, 7, 8))
Or (User_Name()='bob' And Orgn In (1, 6, 7))
--DBO can view all rows
Or User_name()='dbo'
--User John can view all rows
Or User_Name()='John'
[/tt]
You can also use database roles to establish this type of security. You will need to Revoke permissions on the tables for the users and Grant permissions on the views. You could use the same kind of selection criteria in stored procedures.

Another method that is frequently employed is to control Row Level Security via a table rather than hard code it as I've done in the examples. Suppose you have a table named RLSTable with two columns, UserName and Orgn.
[tt]
Create View vTableName AS

Select *
From TableName t
Where Exists
(Select * From RLSTable
Where (UserName=system_user
And Orgn=t.Orgn)
--Let user john see all rows
Or system_user='john'
Or system_user='dbo')
[/tt]
Note: user_name() and system_user return the same value. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.