Newbie Q: System DSN from DMZ?

Hello,
I have a webserver running in a DMZ off my network. It needs access to a
SQL2000 server running inside my network. How can I create a system DSN
that points to my SQL2000 server when there is no direct path?
When I create the DSN, it asks, "which SQL Server to you want to connect
to?" But, the drop down box is empty. What should I be typing in here, and
what holes do I need to create in my firewall to make it work?
-Trevori'm not a firewall expert here, but as far as i'm aware you need to open up the port for SQL server on the firewall - you ca fid this port number from the SQL Cliet manager tool|||simply type in the name of your db server when creating the dsn.
assuming your sqlserver is listening on tcp port 1433, you'll need to open up
port 1433 in the firewall, but only open the hole for your web server. check
your sql server log to see what port it's listening on.
Trevor Clark wrote:
> Hello,
> I have a webserver running in a DMZ off my network. It needs access to a
> SQL2000 server running inside my network. How can I create a system DSN
> that points to my SQL2000 server when there is no direct path?
> When I create the DSN, it asks, "which SQL Server to you want to connect
> to?" But, the drop down box is empty. What should I be typing in here, and
> what holes do I need to create in my firewall to make it work?
> -Trevor

Newbie Q: System DSN from DMZ?

Hello,
I have a webserver running in a DMZ off my network. It needs access to a
SQL2000 server running inside my network. How can I create a system DSN
that points to my SQL2000 server when there is no direct path?
When I create the DSN, it asks, "which SQL Server to you want to connect
to?" But, the drop down box is empty. What should I be typing in here, and
what holes do I need to create in my firewall to make it work?
-Trevori'm not a firewall expert here, but as far as i'm aware you need to open up
the port for SQL server on the firewall - you ca fid this port number from t
he SQL Cliet manager tool|||simply type in the name of your db server when creating the dsn.
assuming your sqlserver is listening on tcp port 1433, you'll need to open u
p
port 1433 in the firewall, but only open the hole for your web server. chec
k
your sql server log to see what port it's listening on.
Trevor Clark wrote:

quote:

---

> Hello,
> I have a webserver running in a DMZ off my network. It needs access to a
> SQL2000 server running inside my network. How can I create a system DSN
> that points to my SQL2000 server when there is no direct path?
> When I create the DSN, it asks, "which SQL Server to you want to connect
> to?" But, the drop down box is empty. What should I be typing in here, a
nd
> what holes do I need to create in my firewall to make it work?
> -Trevor

---

newbie - user permissions

I have added a new user to a database without any explicit permissions, but when I view their effective permissions inside the Microsoft SQL Server Management Studio, they have a whole host of permissions. How can this be? Is it a bug in SQL Server? Or could it be that the public role has all these permissions?

If new users are inheriting these permissions from the public role, how do I view the public role permissions?

Thanks.

To view the public role permissions use the following query

select class_desc,major_id,minor_id,permission_name,state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('public')

For more information see the documentation on the catalog view sys.database_permissions at http://msdn2.microsoft.com/en-us/library/ms188367.aspx

|||

Hi,

did you create a Windows User account or a SQL Server user account ? If you created a SQl Server user Account, then the rights probably belong to the public role. You can view the permissions by naviogating to the Database --> Security --> Database Roles --> public . If you added a Windows user this might also apply as well as the user can be in a Windows group which was added and has the appropiate permissions. All permissions are then inherited from the group the user is in. grant permissions are additive while deny′s are restrictive which means that once denied you cannot do the command this eas applied on.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

This is what I did (all using the Microsoft SQL Server Management Studio):

- Firstly I created a new login (Security -> Logins -> New Login) selecting Windows authentication. Whilst still in the New Login dialog, I selected the User Mappings page and mapped the user to the desired database (leaving membership to the public database role only).

- I then opened the properties for the database that I mapped the new login to, selected the new user and clicked 'Effective Permissions'. The list displayed many more permissions than just "connect".

I have added this user to another SQL Server on the network in exactly the same way and everything worked fine (i.e. the permissions listed were just "Connect").

Any ideas? Thanks again.

|||

As Jens mentioned, the user is probably a member of a group that is granted permissions in this database. You can look at the permissions directly granted to the user in the database by executing:

select class_desc, major_id, minor_id, permission_name, state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('your_user_name')

If this only shows connect, then the other permissions are inherited from a group. You can look more closely at the database_permissions catalog to see which group is granted the extra set of permissions.

Thanks
Laurentiu

|||

Using the query you provided I have determined that the user is only directly granted connect.

Do you know of a query I can run that will tell me the source of the other permissions the user has?

|||

You can check to see what groups/roles you have in the database by querying sys.database_principals. You can then query sys.database_permissions for each group/role to which your user belongs to, to see what permissions these are granted. Or you could work the other way around and check to see who is the grantee of the permissions that you see in Management Studio. To see what Windows groups your user belongs to, you can check the sys.login_token catalog. To see what roles he belongs to, you can query sys.database_role_members.

For example, here are some queries that you may find useful:

-- Find groups to which current user belongs to in current database
--
select lt.name from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid and dbp.type = 'G'

-- Find roles to which current user belongs to in current database
--
select rls.name from sys.database_role_members dbrm, sys.database_principals rls
where dbrm.role_principal_id = rls.principal_id and dbrm.member_principal_id = user_id()

-- Find permissions granted to current user directly or that are inherited
-- from groups or roles
--
select permission_name from sys.database_permissions
where grantee_principal_id in
(
select user_id()
union
select dbp.principal_id from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid
union
select dbrm.role_principal_id from sys.database_role_members dbrm
where dbrm.member_principal_id = user_id()
)

If you need more help, please include the output of these queries with your next reply.

Thanks
Laurentiu

|||

Hi Laurentiu:

Could you please show us on how to get the login name, the username and the role to which the login belongs (db_ddladmin, db_datareader etc) in all the databases?. This query when run from master should show the output as:

Loginname usename databasename Rolename

This should show the output listed by each db.

Also is there a way to script the roles to which the user belongs. I did not see that option in SSMS. In 2000 the option was the checkboxes script users and script database roles.

Please let us know.

Thank you

AK

|||

This query will get the login, it's user name, and database role. Run this in each database, and you have your answer. Although it's not a complete picture if you use Windows Authentication. If you have a windows group as a login, members of that group won't get mapped to a database user until needed and will not show up in this query. But they still have access.

select sl.name [loginname], dbm.name [username], db_name() [databasename], dbr.name [rolename]
from sys.server_principals sl inner join sys.database_principals dbm
on (sl.sid = dbm.sid)
inner join sys.database_role_members dbrm
on (dbm.principal_id = dbrm.member_principal_id)
inner join sys.database_principals dbr
on (dbrm.role_principal_id = dbr.principal_id)

-Jack

|||Thanks a lot Jack. I can modify it on my end according to my needs. Thanks again.

newbie - user permissions

I have added a new user to a database without any explicit permissions, but when I view their effective permissions inside the Microsoft SQL Server Management Studio, they have a whole host of permissions. How can this be? Is it a bug in SQL Server? Or could it be that the public role has all these permissions?

If new users are inheriting these permissions from the public role, how do I view the public role permissions?

Thanks.

To view the public role permissions use the following query

select class_desc,major_id,minor_id,permission_name,state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('public')

For more information see the documentation on the catalog view sys.database_permissions at http://msdn2.microsoft.com/en-us/library/ms188367.aspx

|||

Hi,

did you create a Windows User account or a SQL Server user account ? If you created a SQl Server user Account, then the rights probably belong to the public role. You can view the permissions by naviogating to the Database --> Security --> Database Roles --> public . If you added a Windows user this might also apply as well as the user can be in a Windows group which was added and has the appropiate permissions. All permissions are then inherited from the group the user is in. grant permissions are additive while deny′s are restrictive which means that once denied you cannot do the command this eas applied on.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

This is what I did (all using the Microsoft SQL Server Management Studio):

- Firstly I created a new login (Security -> Logins -> New Login) selecting Windows authentication. Whilst still in the New Login dialog, I selected the User Mappings page and mapped the user to the desired database (leaving membership to the public database role only).

- I then opened the properties for the database that I mapped the new login to, selected the new user and clicked 'Effective Permissions'. The list displayed many more permissions than just "connect".

I have added this user to another SQL Server on the network in exactly the same way and everything worked fine (i.e. the permissions listed were just "Connect").

Any ideas? Thanks again.

|||

As Jens mentioned, the user is probably a member of a group that is granted permissions in this database. You can look at the permissions directly granted to the user in the database by executing:

select class_desc, major_id, minor_id, permission_name, state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('your_user_name')

If this only shows connect, then the other permissions are inherited from a group. You can look more closely at the database_permissions catalog to see which group is granted the extra set of permissions.

Thanks
Laurentiu

|||

Using the query you provided I have determined that the user is only directly granted connect.

Do you know of a query I can run that will tell me the source of the other permissions the user has?

|||

You can check to see what groups/roles you have in the database by querying sys.database_principals. You can then query sys.database_permissions for each group/role to which your user belongs to, to see what permissions these are granted. Or you could work the other way around and check to see who is the grantee of the permissions that you see in Management Studio. To see what Windows groups your user belongs to, you can check the sys.login_token catalog. To see what roles he belongs to, you can query sys.database_role_members.

For example, here are some queries that you may find useful:

-- Find groups to which current user belongs to in current database
--
select lt.name from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid and dbp.type = 'G'

-- Find roles to which current user belongs to in current database
--
select rls.name from sys.database_role_members dbrm, sys.database_principals rls
where dbrm.role_principal_id = rls.principal_id and dbrm.member_principal_id = user_id()

-- Find permissions granted to current user directly or that are inherited
-- from groups or roles
--
select permission_name from sys.database_permissions
where grantee_principal_id in
(
select user_id()
union
select dbp.principal_id from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid
union
select dbrm.role_principal_id from sys.database_role_members dbrm
where dbrm.member_principal_id = user_id()
)

If you need more help, please include the output of these queries with your next reply.

Thanks
Laurentiu

|||

Hi Laurentiu:

Could you please show us on how to get the login name, the username and the role to which the login belongs (db_ddladmin, db_datareader etc) in all the databases?. This query when run from master should show the output as:

Loginname usename databasename Rolename

This should show the output listed by each db.

Also is there a way to script the roles to which the user belongs. I did not see that option in SSMS. In 2000 the option was the checkboxes script users and script database roles.

Please let us know.

Thank you

AK

|||

This query will get the login, it's user name, and database role. Run this in each database, and you have your answer. Although it's not a complete picture if you use Windows Authentication. If you have a windows group as a login, members of that group won't get mapped to a database user until needed and will not show up in this query. But they still have access.

select sl.name [loginname], dbm.name [username], db_name() [databasename], dbr.name [rolename]
from sys.server_principals sl inner join sys.database_principals dbm
on (sl.sid = dbm.sid)
inner join sys.database_role_members dbrm
on (dbm.principal_id = dbrm.member_principal_id)
inner join sys.database_principals dbr
on (dbrm.role_principal_id = dbr.principal_id)

-Jack

|||Thanks a lot Jack. I can modify it on my end according to my needs. Thanks again.

newbie - user permissions

I have added a new user to a database without any explicit permissions, but when I view their effective permissions inside the Microsoft SQL Server Management Studio, they have a whole host of permissions. How can this be? Is it a bug in SQL Server? Or could it be that the public role has all these permissions?

If new users are inheriting these permissions from the public role, how do I view the public role permissions?

Thanks.

To view the public role permissions use the following query

select class_desc,major_id,minor_id,permission_name,state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('public')

For more information see the documentation on the catalog view sys.database_permissions at http://msdn2.microsoft.com/en-us/library/ms188367.aspx

|||

Hi,

did you create a Windows User account or a SQL Server user account ? If you created a SQl Server user Account, then the rights probably belong to the public role. You can view the permissions by naviogating to the Database --> Security --> Database Roles --> public . If you added a Windows user this might also apply as well as the user can be in a Windows group which was added and has the appropiate permissions. All permissions are then inherited from the group the user is in. grant permissions are additive while deny′s are restrictive which means that once denied you cannot do the command this eas applied on.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

This is what I did (all using the Microsoft SQL Server Management Studio):

- Firstly I created a new login (Security -> Logins -> New Login) selecting Windows authentication. Whilst still in the New Login dialog, I selected the User Mappings page and mapped the user to the desired database (leaving membership to the public database role only).

- I then opened the properties for the database that I mapped the new login to, selected the new user and clicked 'Effective Permissions'. The list displayed many more permissions than just "connect".

I have added this user to another SQL Server on the network in exactly the same way and everything worked fine (i.e. the permissions listed were just "Connect").

Any ideas? Thanks again.

|||

As Jens mentioned, the user is probably a member of a group that is granted permissions in this database. You can look at the permissions directly granted to the user in the database by executing:

select class_desc, major_id, minor_id, permission_name, state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('your_user_name')

If this only shows connect, then the other permissions are inherited from a group. You can look more closely at the database_permissions catalog to see which group is granted the extra set of permissions.

Thanks
Laurentiu

|||

Using the query you provided I have determined that the user is only directly granted connect.

Do you know of a query I can run that will tell me the source of the other permissions the user has?

|||

You can check to see what groups/roles you have in the database by querying sys.database_principals. You can then query sys.database_permissions for each group/role to which your user belongs to, to see what permissions these are granted. Or you could work the other way around and check to see who is the grantee of the permissions that you see in Management Studio. To see what Windows groups your user belongs to, you can check the sys.login_token catalog. To see what roles he belongs to, you can query sys.database_role_members.

For example, here are some queries that you may find useful:

-- Find groups to which current user belongs to in current database
--
select lt.name from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid and dbp.type = 'G'

-- Find roles to which current user belongs to in current database
--
select rls.name from sys.database_role_members dbrm, sys.database_principals rls
where dbrm.role_principal_id = rls.principal_id and dbrm.member_principal_id = user_id()

-- Find permissions granted to current user directly or that are inherited
-- from groups or roles
--
select permission_name from sys.database_permissions
where grantee_principal_id in
(
select user_id()
union
select dbp.principal_id from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid
union
select dbrm.role_principal_id from sys.database_role_members dbrm
where dbrm.member_principal_id = user_id()
)

If you need more help, please include the output of these queries with your next reply.

Thanks
Laurentiu

|||

Hi Laurentiu:

Could you please show us on how to get the login name, the username and the role to which the login belongs (db_ddladmin, db_datareader etc) in all the databases?. This query when run from master should show the output as:

Loginname usename databasename Rolename

This should show the output listed by each db.

Also is there a way to script the roles to which the user belongs. I did not see that option in SSMS. In 2000 the option was the checkboxes script users and script database roles.

Please let us know.

Thank you

AK

|||

This query will get the login, it's user name, and database role. Run this in each database, and you have your answer. Although it's not a complete picture if you use Windows Authentication. If you have a windows group as a login, members of that group won't get mapped to a database user until needed and will not show up in this query. But they still have access.

select sl.name [loginname], dbm.name [username], db_name() [databasename], dbr.name [rolename]
from sys.server_principals sl inner join sys.database_principals dbm
on (sl.sid = dbm.sid)
inner join sys.database_role_members dbrm
on (dbm.principal_id = dbrm.member_principal_id)
inner join sys.database_principals dbr
on (dbrm.role_principal_id = dbr.principal_id)

-Jack

|||Thanks a lot Jack. I can modify it on my end according to my needs. Thanks again.

newbe question: calling function inside select

Hi!

I have a scalar function that returns integer:
xview (int)

Now, I'm trying to build a procedure that has the following select
inside:

select atr1, xview(atr2)
from tablename

But, I get the 'Invalid name' error when I try to execute that
procedure.

If I got it right, I must use user.fn_name() syntax, but I cannot use
dbo.xview() inside my procedure since it means xview will always be
executed as dbo, which is unaccaptable.

I'm a bit confused, so any hint is very welcomed.

Thanks!

Mario.Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> I have a scalar function that returns integer:
> xview (int)
> Now, I'm trying to build a procedure that has the following select
> inside:
> select atr1, xview(atr2)
> from tablename
> But, I get the 'Invalid name' error when I try to execute that
> procedure.
> If I got it right, I must use user.fn_name() syntax, but I cannot use
> dbo.xview() inside my procedure since it means xview will always be
> executed as dbo, which is unaccaptable.

But those are the rules. You must refer to a scalar function with a
two-part name.

I don't really see why this is unacceptable. Do you plan to have other
xview functions owned by other users?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 13 Oct 2003 22:13:03 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>But those are the rules. You must refer to a scalar function with a
>two-part name.
>I don't really see why this is unacceptable. Do you plan to have other
>xview functions owned by other users?

Ok, let's put is this way.
I have 'xview' function.

I'm connected to sql server as userX.
Now, when I (as userX) call dbo.xview(), do I execute it as userX or
dbo?

It is vital, because xview() contains code that uses msqql USER sistem
variable, and it should be noted that user userX executed that
function.

Mario.|||Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> Ok, let's put is this way.
> I have 'xview' function.
> I'm connected to sql server as userX.
> Now, when I (as userX) call dbo.xview(), do I execute it as userX or
> dbo?
> It is vital, because xview() contains code that uses msqql USER sistem
> variable, and it should be noted that user userX executed that
> function.

USER will return userX.

The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
does not change when you call a user-defined function or stored procedure.

The point with calling a stored procedure owned by another user, is
that you can get controlled access to objects that you don't have direct
access to. For instance, in many databases, users does not have direct
access to any tables. Instead they only have access to stored procedures
and user-defined functions that make sure that the users can only access
data they have a right to see, and their updates conforms to the rule
of the database.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 14 Oct 2003 22:00:21 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>USER will return userX.
>The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
>return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
>does not change when you call a user-defined function or stored procedure.
>The point with calling a stored procedure owned by another user, is
>that you can get controlled access to objects that you don't have direct
>access to. For instance, in many databases, users does not have direct
>access to any tables. Instead they only have access to stored procedures
>and user-defined functions that make sure that the users can only access
>data they have a right to see, and their updates conforms to the rule
>of the database.

Aha. That is very good. Thank you for the information!

Mario.