change user1 access changes user2 access

Hello,
I have set up some accounts on Sql Server 2k. I am just
starting out with Security stuff. A problem that I am
having is that when I assign access to one account, user1,
the next account, user2, appears to pick up the same
access to things. Ideally, I want user1 to be able to
connect to tables and user2 to be able to execute stored
procedures. But boths users seem to acquire the same
access when I set one. Then when I reset the other, the
first user now has the same access as the second user. Is
there a way to separate this?
Thanks,
RonApril 7, 2005
When you are giving permissions to User1, are you giving permissions to a
group that User1 belongs to? If you are assigning permissions to User1
through a group membership, then it sounds to me like User2 is in the same
group. If this is the case then make sure User2 is not in any groups that
you are using to assign permission to User1. Hope this helps! :-)
Joseph MCAD
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:020001c53b8a$54890a40$a501280a@.phx.gbl...
> Hello,
> I have set up some accounts on Sql Server 2k. I am just
> starting out with Security stuff. A problem that I am
> having is that when I assign access to one account, user1,
> the next account, user2, appears to pick up the same
> access to things. Ideally, I want user1 to be able to
> connect to tables and user2 to be able to execute stored
> procedures. But boths users seem to acquire the same
> access when I set one. Then when I reset the other, the
> first user now has the same access as the second user. Is
> there a way to separate this?
> Thanks,
> Ron|||The way I am creating new logins is by going to the
Security Icon in EM. I click on New Login. I assign a
name, a password, I select a database to login to, then go
the the Database Access and select the same database again
then in the Permit In Database Role window, I select
public, I click on properties, then I click on Permissions
in the Database Role Type window. Then I click on what
the user should have access to. So all the users have the
same database role on the same database. Do I need to
change the role in order to set the respective permissions
for the respective users? What is the recommended role
for a user who should only look at certain tables? For a
user that should only be able to execute certain SP's?
Thanks
Ron

>--Original Message--
>April 7, 2005
> When you are giving permissions to User1, are you
giving permissions to a
>group that User1 belongs to? If you are assigning
permissions to User1
>through a group membership, then it sounds to me like
User2 is in the same
>group. If this is the case then make sure User2 is not in
any groups that
>you are using to assign permission to User1. Hope this
helps! :-)
>
Joseph MCAD
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:020001c53b8a$54890a40$a501280a@.phx.gbl...
user1,[vbcol=seagreen]
Is[vbcol=seagreen]
>
>.
>|||April 7, 2005
Using the public role is your problem. All users, whether it be User1,
User2, UserX, are members of this role. By granting the public role
permission you are granting All users the permissions. You should create a
new role specifically for this certain login and then grant that role
permissions. Then just add users to the new role. This is why it seems that
the users are tied together. It is recommended by security experts to never
grant permission to the public role, but to instead create custom roles! I
am glad I could be of help! :-)
Joseph MCAD
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:122201c53ba3$b17bdd40$a601280a@.phx.gbl...[vbcol=seagreen]
> The way I am creating new logins is by going to the
> Security Icon in EM. I click on New Login. I assign a
> name, a password, I select a database to login to, then go
> the the Database Access and select the same database again
> then in the Permit In Database Role window, I select
> public, I click on properties, then I click on Permissions
> in the Database Role Type window. Then I click on what
> the user should have access to. So all the users have the
> same database role on the same database. Do I need to
> change the role in order to set the respective permissions
> for the respective users? What is the recommended role
> for a user who should only look at certain tables? For a
> user that should only be able to execute certain SP's?
> Thanks
> Ron
>
> giving permissions to a
> permissions to User1
> User2 is in the same
> any groups that
> helps! :-)
> Joseph MCAD
> message
> user1,
> Is|||Yes, I thought so. Thanks for your reply. Anyway, I did
click on the Role option, but I did not see anywhere for
creating a new or custom role. May I ask how this is
done? This is definitely what I need to do.
Thanks again,
Ron

>--Original Message--
>April 7, 2005
> Using the public role is your problem. All users,
whether it be User1,
>User2, UserX, are members of this role. By granting the
public role
>permission you are granting All users the permissions.
You should create a
>new role specifically for this certain login and then
grant that role
>permissions. Then just add users to the new role. This is
why it seems that
>the users are tied together. It is recommended by
security experts to never
>grant permission to the public role, but to instead
create custom roles! I
>am glad I could be of help! :-)
>
Joseph MCAD
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:122201c53ba3$b17bdd40$a601280a@.phx.gbl...
go[vbcol=seagreen]
again[vbcol=seagreen]
Permissions[vbcol=seagreen]
the[vbcol=seagreen]
permissions[vbcol=seagreen]
a[vbcol=seagreen]
in[vbcol=seagreen]
just[vbcol=seagreen]
stored[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||OK. I found role in Books on line. I think I am starting
to get the idea. So just to make sure, if I want a
different user to have different permissions, that user
would be in a different role? I assume yes. And, if I
only want a user to be able to access only specific
tables, then I can uncheck everything in the permissions
except for the specific table? I also assume that would
be yes. well, here goes.

>--Original Message--
>April 7, 2005
> Using the public role is your problem. All users,
whether it be User1,
>User2, UserX, are members of this role. By granting the
public role
>permission you are granting All users the permissions.
You should create a
>new role specifically for this certain login and then
grant that role
>permissions. Then just add users to the new role. This is
why it seems that
>the users are tied together. It is recommended by
security experts to never
>grant permission to the public role, but to instead
create custom roles! I
>am glad I could be of help! :-)
>
Joseph MCAD
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:122201c53ba3$b17bdd40$a601280a@.phx.gbl...
go[vbcol=seagreen]
again[vbcol=seagreen]
Permissions[vbcol=seagreen]
the[vbcol=seagreen]
permissions[vbcol=seagreen]
a[vbcol=seagreen]
in[vbcol=seagreen]
just[vbcol=seagreen]
stored[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||April 7, 2005
You have the idea. You don't really have to create a role, but it makes
management easier. If you only need One user to have specific permissions,
you can always just assign that user the permissions directly. I always
think that roles are much better though. Tell me how you turn out! :-)
Joseph MCAD
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:039e01c53bae$bbed3340$a501280a@.phx.gbl...[vbcol=seagreen]
> OK. I found role in Books on line. I think I am starting
> to get the idea. So just to make sure, if I want a
> different user to have different permissions, that user
> would be in a different role? I assume yes. And, if I
> only want a user to be able to access only specific
> tables, then I can uncheck everything in the permissions
> except for the specific table? I also assume that would
> be yes. well, here goes.
>
> whether it be User1,
> public role
> You should create a
> grant that role
> why it seems that
> security experts to never
> create custom roles! I
> Joseph MCAD
> message
> go
> again
> Permissions
> the
> permissions
> a
> in
> just
> stored
> the

Change Password Role

Is there a role that I can assign to a user that will give them the ability
to change password for specific group of database users?
ThanksWith the ability to change password for just one database; I don't think
there is one.
But you can do the following:
1) Create a new login on the server with securityadmin server role access.
2) Create a stored procedure in the database where you want to give the
ability to change passwords as yourself, and in the SP impersonate the login
created in step 1.
3) Encrypt the SP (Your choice don't have to).
4) Give the database user execute permissions to the SP.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||Agho (Agho@.discussions.microsoft.com) writes:
> Is there a role that I can assign to a user that will give them the
> ability to change password for specific group of database users?
Which version of SQL Server are you using?
In SQL 2005, you should be able create a role that you can grant
ALTER LOGIN on the logins, you want the role to be able to change
the passwords for. I don't think you can ALTER LOGIN on a role, but
you will have to grant rights on each login.
If you want users in the role to have the right to change any password,
you can grant the role ALTER ANY LOGIN.
I'm 100% sure that ALTER LOGIN is sufficient; you may need CONTROL LOGIN.
But testing this should be simple.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx