Showing posts with label obtain. Show all posts
Showing posts with label obtain. Show all posts

Tuesday, March 20, 2012

Query to obtain users and privileges from databases

Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.

I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.

thank you

There are a couple of views that can help you here.

sys.server_permissions and sys.database_permissions will show what permissions are granted to logins and users respectively.

Do you need to only check for individual users or do you need to check for roles as well?

thx,

-steven

|||

The following solution provides a way to query all the permissions of all users in the current database. So it is not a complete answer to your question. In order to navigate in every database, it is easier to write managed code program to do that.

Select sys.database_principals.name, permission_name,state, state_desc

from sys.database_permissions, sys.database_principals

where grantee_principal_id = principal_id

|||Thank you very much for your answers

Query to obtain users and privileges from databases

Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.

I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.

thank you

There are a couple of views that can help you here.

sys.server_permissions and sys.database_permissions will show what permissions are granted to logins and users respectively.

Do you need to only check for individual users or do you need to check for roles as well?

thx,

-steven

|||

The following solution provides a way to query all the permissions of all users in the current database. So it is not a complete answer to your question. In order to navigate in every database, it is easier to write managed code program to do that.

Select sys.database_principals.name, permission_name,state, state_desc

from sys.database_permissions, sys.database_principals

where grantee_principal_id = principal_id

|||Thank you very much for your answers

Query to obtain missing number

I've been trying to figure out how to create a query that would list the missing numbers between a high and low number for a field. For example, If I have the recordset below:

1
3
4
6
7
9

I'd like the resulting recordset to be:

2
5
8

Is there a way to achieve this? Thanks, Jason.Yes, there are several ways.

What have you covered so far in class?

-PatP|||In Class? I'm not taking a class. I know the programming language fairly well, I just cannot figure this one out. Can you give me a quick example? Thanks, Jason.|||There are multiple ways to do this. Probably the simplest is to create a "numbers" table with one row for every interesting (possible) value that a number might have. For a two byte integer, this range could be -32768 through 32767. Once you've got the numbers table, you can do a simple exists test, something like:SELECT n.val
FROM numbers AS n
WHERE NOT EXISTS (SELECT *
FROM myRecordset AS r
WHERE r.val = n.val)Of course you'd also need to limit the result to just the values of interest in this case (between the Min and Max values already in your recordset).

-PatP|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||Does Foxpro support recursive queries? If so, you could recursively increment an integer up to some limit and exclude the non-qualifying rows.

query to obtain linked server properties

Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers already
Hi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj

query to obtain linked server properties

Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers alreadyHi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj

query to obtain linked server properties

Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers alreadyHi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj