Showing posts with label manager. Show all posts
Showing posts with label manager. Show all posts

Friday, March 23, 2012

How to get the folder wise security info of all the users?

Hi,

The Report Manager portal has many folders. For each folder there are specific users with different roles.

I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.

Use ReportServer

SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r

WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID

The above query fetches all the users and their roles.

The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query.

TIA

The security settings appear to be in XML. See the GetPolicy stored procedure.

select xmldescription from secdata

You can see the security on each folder by running rsscripter utility and looking at the resulting code in the fldr.rss files.

'Set Folder security
Dim pol(4) As [Policy]
Dim sersec As Serialization.XmlSerializer = New Serialization.XmlSerializer(GetType(Policy))
Dim tsec0 As New StringReader("<?xml version=""1.0"" encoding=""utf-16""?><Policy xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""> <GroupUserName xmlns=""http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"">BUILTIN\Administrators</GroupUserName> <Roles xmlns=""http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices""> <Role> <Name>Content Manager</Name> </Role> </Roles></Policy>")

If I was trying to reverse-engineer how to get the security roles, I would look into SQL Profiler to see what calls are being made when setting/getting security on folders.

You may have more luck using SOAP & Web Service to get the information you want.

regards,

Andrew

Monday, March 19, 2012

How to get SQL Server Management Studio to automatically connect upon startup?

Hi all,
2000's Enterprise Manager was smart enough to automatically connect to my
last database when it was launched.
Somehow I can't find out how to turn on this extremely useful feature in
2005's Management Studio. Can anyone tell me the secret?
Thanks,
C17
> 2000's Enterprise Manager was smart enough to automatically connect to my
> last database when it was launched.
> Somehow I can't find out how to turn on this extremely useful feature in
> 2005's Management Studio. Can anyone tell me the secret?
None of these things are "secrets." You can add command line parameters and
create shortcuts for different servers. Here are the possible startup
parameters for Management Studio (sqlwb.exe):
Usage:
sqlwb.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P
password] [-E] [file_name[, file_name]] [/?]
[-S The name of the SQL Server instance to which to connect]
[-d The name of the SQL Server database to which to connect]
[-E] Use Windows Authentication to login to SQL Server
[-U The name of the SQL Server login with which to connect]
[-P The password associated with the login]
[file_name[, file_name]] names of files to load
[-nosplash] Supress splash screen
[/?] Displays this usage information
However, this won't automatically open Object Explorer. (Same was true in
Query Analyzer, IIRC.)
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||Thank you, that works great!!
It's still annoying that one can't set a default via the UI, though. From
reading the Feedback pages, and the MSFT responses, it seems as though
everyone at MSFT thinks that we're all using Management Studio from inside
of VS, and so the response to a lot of these usability requests is
basically,
"Well, you can do that from inside of VS." I think they forget that a lot
of us use it for management, not development!
Thanks again,
C17
|||You maybe are misunderstanding the posts from Microsoft. You
don't use Management Studio from inside Visual Studio. You
use Management Studio OR Visual Studio.
What posts are you referring too? You are always free to
offer product suggestions.
-Sue
On Thu, 14 Jun 2007 14:41:07 -0400, "C17"
<c17@.community.nospam> wrote:

>Thank you, that works great!!
>It's still annoying that one can't set a default via the UI, though. From
>reading the Feedback pages, and the MSFT responses, it seems as though
>everyone at MSFT thinks that we're all using Management Studio from inside
>of VS, and so the response to a lot of these usability requests is
>basically,
>"Well, you can do that from inside of VS." I think they forget that a lot
>of us use it for management, not development!
>Thanks again,
>C17
>

How to get size of a table

Hello,

I want to get size of a particular table. I have used the wizard provided by SQL Server Enterprise Manager. But it is giving wrong size. If the data of some cells has been deleted or some rows are deleted. It shows the same size which it shows for the first time. I have also used sp_spaceused but is showing the same behaviour as mentioned above. I deleted some rows from the table but is showing the same size.I want to know the size exactly, so that i can come to know that some rows has been deleted or data is corrupted. Please tell me the solution as early as possible.

Haider Ali Khan

sp_spaceused()

Wednesday, March 7, 2012

how to get online/offline status

Hi,

i try get information about the online or offline status of a database
within ms-sql srv 2000.

I can look it up within server manager, but I'm looking for a way to get
this information from the command line or a select statement.

Is there a command or a sql statement that tells me the status of a
database ? There's some information in master.dbo.sysdatabases, but it
looks very cryptic.

Any help appreciated, thanks,
stefan"Stefan Behrens" <stefan.news@.gmx.net> wrote in message
news:Xns94DB71E9836ADstefannewsgmxnet@.195.20.224.1 16...
> Hi,
> i try get information about the online or offline status of a database
> within ms-sql srv 2000.
> I can look it up within server manager, but I'm looking for a way to get
> this information from the command line or a select statement.
> Is there a command or a sql statement that tells me the status of a
> database ? There's some information in master.dbo.sysdatabases, but it
> looks very cryptic.
> Any help appreciated, thanks,
> stefan

select databasepropertyex('master', 'status')

Simon

Friday, February 24, 2012

How to get list (text) of all tables and columns?

Is there a way using MS SQL Server and Enterprise Manager to get a text
document (or perhaps even a Word document) listing all table names,
column names, etc of a database?

--
Sugapablo
-----------
http://www.sugapablo.com <--music
http://www.sugapablo.net <--personal"Sugapablo" <russREMOVE@.sugapablo.com> wrote in message
news:vmu7b03j5uos68@.corp.supernews.com...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?
> --
> Sugapablo
> -----------
> http://www.sugapablo.com <--music
> http://www.sugapablo.net <--personal

It would probably be easier to use Query Analyzer instead, and get the
details you need from the INFORMATION_SCHEMA views (assuming you have SQL7
or 2000), eg.:

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME, ORDINAL_POSITION

If it's a one-off task, then you can just cut and paste the results, but if
you need to do it regularly, then you could consider using SQLDMO from a
client application. That way you can create the whole Word document using VB
or something similar.

Simon|||Sugapablo <russREMOVE@.sugapablo.com> wrote in message news:<vmu7b03j5uos68@.corp.supernews.com>...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?

Hello,

To get a list of user tables use this query:

select name from ssysobjects where type = 'u'

And for a list of columns related for all objects (tables,views,sps)

select name, object_name(id) from sysindexes

The results could be pushed to a text document or any other ODBC
compliant datasource using DTS.

Regards,
-Manoj