We need to create a view of our active directory users (we have 2500).
I found out that there is max page size of 1000, so we cannot get more
data.
Anyone found a solution to that problem?
ThanksI don't really understand what your question has to do with MSSQL - are
you talking about Reporting Services, perhaps? If so, you can try
microsoft.public.sqlserver.reportingsvcs to see if you get a better
response.
Simon
thnx.
I hope u need to get the result of two table from two database . You can create a view with three part qualified name.
Create view yourviewname
as
Select *from Databasename1.owner.tablename where ......
Union /Join
Select *from Databasename2.owner.tablename where ......
basically you need to give fully qualifed tablename . The user accessing this view should have permission in both the dbs
Madhu
|||thanks. its up and running...
In SQL Server 2005, you must have the VIEW SERVER STATE permission in order to access sys.syslockinfo (http://msdn2.microsoft.com/en-us/library/ms189497).
It seems that the VIEW SERVER STATE permission can only be granted to users.
Once you execute sp_setapprole, the connection loses the permissions of the user and assumes the permissions of the application role (http://msdn2.microsoft.com/en-us/library/ms190998).
So, how can I access the sys.syslockinfo view while using an application role?
Note: I have to maintain compatibility with SQL Server 2000
a) Create a signed stored procedure that exposes syslockinfo and grant execute on it to the application role. An example of procedure signing has been presented in the following post:
controlling security through stored procedures -- 2005 behaviour
b) Grant VIEW SERVER STATE to public.
Thanks
Laurentiu|||
Thanks for your help. I see that they just added this knowledge base article: http://support.microsoft.com/kb/906549, which has another example. Did you write the kb article?
|||Thanks for posting the KB link, this is recent indeed. No, I didn't write that article but I see it's a complete description of the solution I mentioned as (a). Let us know if you have any trouble with that solution.Thanks
Laurentiu|||Their example works great for syslogins and sysprocesses. However, it doesn't work for syslockinfo.
This is the error message:
The user does not have permission to perform this action.|||Nevermind, I think there was just something wrong with my install. I reinstalled, and now it is working. Thanks again for your help.
In SQL Server 2000 we had a view that would show the user credentials and the password hash. The reason we need this is that we use SQL Server authentication on the database. To test users, we have a login with little access, and it should be able to see the view and compare the password supplied against what is in the database, and then let the code handle a graceful exit if the password is invalid. I am trying to do this with SQL Server 2005, and I am running into trouble. I am trying to do this with a function, since there I can set the EXECUTE AS clause (in theory) and leverage the privlidges of a specific user in the database. Here is an example function:
CREATE FUNCTION check_acct.fn_allusers (@.test int)
RETURNS @.users table (username varchar(50), passwd varbinary(256))
WITH EXECUTE AS caller
AS
BEGIN
INSERT @.users
select name, password_hash from sys.sql_logins
RETURN
END
GO
GRANT SELECT ON fn_allusers TO user_acct
I cannot get to all rows in the sys.sql_logins table unless I first:
GRANT VIEW ANY DEFINITION TO CHECK_ACCT
then when I call this function from CHECK_ACCT, I am able to see the data. If I change the WITH EXECUTE AS 'CHECK_ACCT' and execute thsi function from USER_ACCT, I do not get the same results. Do functions not inherit VIEW DEFINITION proivlidges? Any suggestions on how to do this but limit access to these secure objects to this one function?
You're seeing the expected behavior, as I understand it. As you know, the "AS CALLER" reference will run the proc statements as the account that runs the proc, in which case that account needs the perms you're trying to use.
This may have to do with the scope of the call. If you'll look up the EXECUTE AS (Transact-SQL) statement in Books Online (make sure you have the February 2007 version loaded), you'll see a note about calling out of the current database, which you're doing. If you're in a database and you call sys.sql_logins, you're hitting the master database, which is outside the scope of the current database. I wonder if adding "USE master" would work?
|||The permission needed to see the password hashes is CONTROL SERVER. See BOL for detailed information (http://msdn2.microsoft.com/en-us/library/ms187113.aspx).
The execution token established by EXECUTE AS is (by default) only trusted on the database where the execution context switch took place, and all server scoped privileges for the impersonated context are stripped down to DENY ONLY. This is explained in better detail on BOL: Extending Database Impersonation by Using EXECUTE AS (http://msdn2.microsoft.com/en-us/library/ms188304.aspx).
In this case, it may be simpler to sign the SP and grant CONTROL SERVER via the signature instead of via an impersonated context.
-- Removed the EXECUTE AS clause
--
CREATE FUNCTION check_acct.fn_allusers (@.test int)
RETURNS @.users table (username varchar(50), passwd varbinary(256))
BEGIN
INSERT @.users
select name, password_hash from sys.sql_logins
RETURN
END
GO
-- In order to see the password_hash, you require quite elevated privileges: CONTROL SERVER
-- I strongly recommend storing a backup of the signing private key in a safe place and then remove the copy from the database
--
CREATE CERTIFICATE [cert_fn_allusers] WITH
SUBJECT = 'Signing certificate for check_acct.fn_allusers'
go
ADD SIGNATURE TO [check_acct.fn_allusers] BY CERTIFICATE [cert_fn_allusers]
go
-- A copy of the certificate should be in master
--
CREATE LOGIN [cert_fn_allusers] FROM CERTIFICATE [cert_fn_allusers]
go
GRANT CONTROL SERVER TO [cert_fn_allusers]
go
As an additional suggestion I would recommend using the password hashes exclusively for porting/updating SQL Server accounts between multiple SQL Server instances.
I hope this information will be useful. Let us know if you have further questions or feedback.
-Raul Garcia
SDE/T
SQL Server Engine
|||One thing that was brought to my attention is that I should have emphasized more is that potentially the usage of the passwords hash you mentioned on your problem description is dangerous.
The reason why only CONTROL SERVER permission giving access to the password hash is for protection against an attacker with lower privileges to obtain the password table and crack it offline. I strongly recommend allowing SQL Server itself verify the passwords of your users instead of doing this job on a separate code.
Additionally by allowing SQL Server to verify the password, you will be able to use the infrastructure to detect failed attempts (i.e. detect a brute force attack) and make use of the password policy functionality.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
Hi,
My Reportserver Homepage is http://d620xp/Reports
Requirement is that Users should be able to view all the reports from this URL( from their Desktops)
What are the steps I need to do, to get this ?
Thanks in Advance,
Sundar
What happens when you enter http://d620xp/Reports in other machine's web browsers?
|||Grant Privileges to the required users. After this when use opens this URL they should be able to see all the deployed reports...|||
Hi,
When Users type the ReportServer URL, they are seeing the Homepage but all the folders ( Datasources, Models and reports) are missing.
Is thr a way to enable Users to see all the Reports ?
Thanks,
Sundar
|||Hi,
Thanks for your Reply.
How do I grant priviledges to Users ?
Should I do it through ReportManager Homepage or through IIS Site settings ?
Regards,
Sundar
|||You have to grant the correct permissions to the users. Right now, it appears that it is set to the most restrictive permissions.
|||
Sundar1234 wrote:
Hi,
Thanks for your Reply.
How do I grant priviledges to Users ?
Should I do it through ReportManager Homepage or through IIS Site settings ?
Regards,
Sundar
Through reportmanager. Click properties and add the proper role assignments.
|||
Greg,
Under Properties->Security, I have the following:
> Browser, Content Manager, My Reports, Publisher, Report Builder
How do I proceed from here ?
Any help is appreciated !
Thanks,
Sundar
|||
Greg,
Under Properties->Security, I have the following:
BUILTIN\Administrators > Browser, Content Manager, My Reports, Publisher, Report Builder
D620xp\Guest Browser, Everyone How do I proceed from here ?
Any help is appreciated !
Thanks,
Sundar
Although I've never needed to do this, I would think that you would want D620xp\Guest to have access to My Reports, Report Builder, Browser, Everyone.
Possibly even Content Manager and Publisher if you trust them with that.
Right now your rights for the Guest account are too restrictive.
