Permit to connectting SQL Server 2005 Express only at specific client ip.

Hi, how can I set SQL Server 2005 Express to permit only specific client ip? Is this possible using T-SQL?

This is not possible using SQL Server or T-SQL.

You may be able to limit the connecting clients by using IPSEC or SSL.

|||Hi,

there is a property which is called Applicationname which can be retrievedt hrough the APP_NAME() function. But be aware, one can change that by just manipulating the connectin string, so if you read this anywhere on the web, that this "security feature" can be used to check the application name, just ignore it.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||And many 'custom' applications do not provide an AppName.

permissions granted to an application intead of a user

I have an application that talks to an access db located on a 2000 server. I would like grant permissions to the application instead of a specific user. Any help would be great...

Such capability is not available - permissions are grantable to accounts, not to applications.

Thanks
Laurentiu

|||

I am including a link to a related entry in this forum where we have a detailed discussion on this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=786513&SiteID=1

I hope this information will be useful.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

let me restate:

the db is on a mapped drive on the 2000 server.

i want the prgm to have access to it but not to the general public.

i need a way to limit access to myself and the prgm only....

i have admin rights and would like to give the prgm the same...

|||

Answer is the same - Windows security works by assigning permissions to users - permissions cannot be assigned to applications.

You should read the post that Raul pointed out. You can attempt to use application roles or have the application connect with specific credentials to the database, but a smart user can reverse-engineer the application and figure out how to connect directly to the database, hence there is no security guarantee of the type you seek.

Thanks
Laurentiu

Permissions

How can I tell what permisisions a user or role has for a specific database?
I've queried the syspermissions table but it's not very helpful.
I've granted a user "create view" permission and would like to verify that it's in effect.
Thanks.
MaryHave you looked up "Permissions" in BOL?|||Yes, I have.

Permission to execute specific jobs only

Is there a way to grant a group of users the ability to execute some jobs on a server, but restrict their access to others.

We'd like to grant our developers the permission to execute their jobs (many developers and we'd like to avoid a shared account) without giving them the ability to execute our backup, reindex, etc jobs.

Is there a way to grant execute on some jobs, but not all to a Windows group?

We're using the new msdb SQLAgent role to allow them to see all jobs and view output, but there doesn't seem to be a way to grant execute on specific jobs only.

Thanks

Under normal circumstances, a user will be able to start a Job that he/she 'owns'.

Of course, success depends upon have permissions to execute each step of the Job.

|||You could consider having a table populated that indicates what the groups can do. Then make the first step of the job a query which raises an error if the permission shouldn't apply. Only continue the job if the query succeeds.

Alternatively, you could fake this by having a tiny table for each job. So you have a "sec_backup" table (with a single column and row), which is only queryable by those who also have permission to run the job.

It's not particularly elegant, but you could quite easily produce the security model you're after this way.

Rob

Permission for a newly created user

I have created a login that belongs to both Public and
db_datareader database role to a specific database.
However, when I open the property of that newly created
user and select "List only objects with permissions for
this user". It is blank.
On the other hand, when I connect to QA with that user, I
am able to run SQL Script.
Is there anything wrong ? OR It is correct that we are
not able to see the access right in EM ?Peter
db_datareader is intended for SELECT all data from any users tables
If you want the user to not be able to modify any data in users tables make
him a member of db_denydatawriter
fixed database role
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:7c4801c5282d$0b0b3390$a601280a@.phx.gbl...
> I have created a login that belongs to both Public and
> db_datareader database role to a specific database.
> However, when I open the property of that newly created
> user and select "List only objects with permissions for
> this user". It is blank.
> On the other hand, when I connect to QA with that user, I
> am able to run SQL Script.
> Is there anything wrong ? OR It is correct that we are
> not able to see the access right in EM ?|||No...nothing is wrong. You will see the permissions
explicitly granted to the user when viewing the permissions
from Enterprise Manager, users. You won't see the
permissions the user has based on membership to the role.
-Sue
On Sun, 13 Mar 2005 16:30:37 -0800, "Peter"
<anonymous@.discussions.microsoft.com> wrote:

>I have created a login that belongs to both Public and
>db_datareader database role to a specific database.
>However, when I open the property of that newly created
>user and select "List only objects with permissions for
>this user". It is blank.
>On the other hand, when I connect to QA with that user, I
>am able to run SQL Script.
>Is there anything wrong ? OR It is correct that we are
>not able to see the access right in EM ?

Permission for a newly created user

I have created a login that belongs to both Public and
db_datareader database role to a specific database.
However, when I open the property of that newly created
user and select "List only objects with permissions for
this user". It is blank.
On the other hand, when I connect to QA with that user, I
am able to run SQL Script.
Is there anything wrong ? OR It is correct that we are
not able to see the access right in EM ?
Peter
db_datareader is intended for SELECT all data from any users tables
If you want the user to not be able to modify any data in users tables make
him a member of db_denydatawriter
fixed database role
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:7c4801c5282d$0b0b3390$a601280a@.phx.gbl...
> I have created a login that belongs to both Public and
> db_datareader database role to a specific database.
> However, when I open the property of that newly created
> user and select "List only objects with permissions for
> this user". It is blank.
> On the other hand, when I connect to QA with that user, I
> am able to run SQL Script.
> Is there anything wrong ? OR It is correct that we are
> not able to see the access right in EM ?
|||No...nothing is wrong. You will see the permissions
explicitly granted to the user when viewing the permissions
from Enterprise Manager, users. You won't see the
permissions the user has based on membership to the role.
-Sue
On Sun, 13 Mar 2005 16:30:37 -0800, "Peter"
<anonymous@.discussions.microsoft.com> wrote:

>I have created a login that belongs to both Public and
>db_datareader database role to a specific database.
>However, when I open the property of that newly created
>user and select "List only objects with permissions for
>this user". It is blank.
>On the other hand, when I connect to QA with that user, I
>am able to run SQL Script.
>Is there anything wrong ? OR It is correct that we are
>not able to see the access right in EM ?

Permission for a newly created user

I have created a login that belongs to both Public and
db_datareader database role to a specific database.
However, when I open the property of that newly created
user and select "List only objects with permissions for
this user". It is blank.
On the other hand, when I connect to QA with that user, I
am able to run SQL Script.
Is there anything wrong ? OR It is correct that we are
not able to see the access right in EM ?Peter
db_datareader is intended for SELECT all data from any users tables
If you want the user to not be able to modify any data in users tables make
him a member of db_denydatawriter
fixed database role
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:7c4801c5282d$0b0b3390$a601280a@.phx.gbl...
> I have created a login that belongs to both Public and
> db_datareader database role to a specific database.
> However, when I open the property of that newly created
> user and select "List only objects with permissions for
> this user". It is blank.
> On the other hand, when I connect to QA with that user, I
> am able to run SQL Script.
> Is there anything wrong ? OR It is correct that we are
> not able to see the access right in EM ?|||No...nothing is wrong. You will see the permissions
explicitly granted to the user when viewing the permissions
from Enterprise Manager, users. You won't see the
permissions the user has based on membership to the role.
-Sue
On Sun, 13 Mar 2005 16:30:37 -0800, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>I have created a login that belongs to both Public and
>db_datareader database role to a specific database.
>However, when I open the property of that newly created
>user and select "List only objects with permissions for
>this user". It is blank.
>On the other hand, when I connect to QA with that user, I
>am able to run SQL Script.
>Is there anything wrong ? OR It is correct that we are
>not able to see the access right in EM ?

Permission

Hi,
is there a way to know every permission that a specific user has in the SQl server using a Sql line ?
I know individually ! But I would like a process that will look in all databases all roles, and so on !
Any help I'll apreciate !
Thank's
Hi,
Have a look into the procedure sp_helprotect in books online.
Thanks
Hari
MCDBA
"Carrasco" <Carrasco@.discussions.microsoft.com> wrote in message
news:81C7D2F3-624D-4331-A257-5792D75CD82F@.microsoft.com...
> Hi,
> is there a way to know every permission that a specific user has in the
SQl server using a Sql line ?
> I know individually ! But I would like a process that will look in all
databases all roles, and so on !
> Any help I'll apreciate !
> Thank's
>

Permission

Hi,
is there a way to know every permission that a specific user has in the SQl
server using a Sql line '
I know individually ! But I would like a process that will look in all datab
ases all roles, and so on !
Any help I'll apreciate !
Thank'sHi,
Have a look into the procedure sp_helprotect in books online.
Thanks
Hari
MCDBA
"Carrasco" <Carrasco@.discussions.microsoft.com> wrote in message
news:81C7D2F3-624D-4331-A257-5792D75CD82F@.microsoft.com...
> Hi,
> is there a way to know every permission that a specific user has in the
SQl server using a Sql line '
> I know individually ! But I would like a process that will look in all
databases all roles, and so on !
> Any help I'll apreciate !
> Thank's
>