All,
Is that possible to have a role that has admin permission on views, but only
read/write permission on tables?
Thanks!
TinaSure, you can create your own role e.g.
exec sp_addrole 'ViewCreators'
grant create view to ViewCreators
exec sp_addrolemember 'db_datareader','ViewCreators'
exec sp_addrolemember 'db_datawriter','ViewCreators'
You can then just add users to that role.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tina Ma" <tina.ma@.parsons.com> wrote in message
news:etFp$YyfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> All,
> Is that possible to have a role that has admin permission on views, but
> only
> read/write permission on tables?
> Thanks!
> Tina
>
Showing posts with label admin. Show all posts
Showing posts with label admin. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
permissions for SQL admin
Hi,
I got a challenging question from my DBA: he set up some auditing
procedure to check all the user computers to see the versions of small
applications. This is done through a Storeprocedure under one DB called
auditing. Basically, he said when this storeprocedure runs, the script runs
and get all the version information from user boxes. And, as he said, this
needs domain admin rights to run because this script actaully grabs
information from all user computers.
Please advice what is the best, suitable rights rather than domain admin
rights in order for this undertaking to be carried out.
Thanks.If you want to run that procedure on a schedule you will have to grant
the SQL Agent account the appropiate permissions. If you want to run
that ad-hoc, you will have to make sure that the current user, if the
authenticated with WIndows authentication, is priviledged to the
operations on the client, if the user is authenticated with SQL Server
authentication that the SQL Server service account is priviledged to
run the operations on the client.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
I got a challenging question from my DBA: he set up some auditing
procedure to check all the user computers to see the versions of small
applications. This is done through a Storeprocedure under one DB called
auditing. Basically, he said when this storeprocedure runs, the script runs
and get all the version information from user boxes. And, as he said, this
needs domain admin rights to run because this script actaully grabs
information from all user computers.
Please advice what is the best, suitable rights rather than domain admin
rights in order for this undertaking to be carried out.
Thanks.If you want to run that procedure on a schedule you will have to grant
the SQL Agent account the appropiate permissions. If you want to run
that ad-hoc, you will have to make sure that the current user, if the
authenticated with WIndows authentication, is priviledged to the
operations on the client, if the user is authenticated with SQL Server
authentication that the SQL Server service account is priviledged to
run the operations on the client.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
Permissions for Domain user account
Hi,
I want to use a domain user account not belonging to local admin or domain admin groups in SQL 2000/2005 Enterprise edition. This is what I've done so far..
On the machine that is the Domain Controller:
- installed SQL 2005 as a domain admin
- created a domain user account using Active Directory Users and Computers. This user is only
"Member of" domain users; not any Administrators group.
- added this user to SQL Server Management Studio->Logins and in Server Roles assigned
sysadmin role.
Question 1: Do I need to give any additional permissions to this user to work with SQL?
Question 2: How can I test this user for basic SQL operations like database creation? Can I use Osql?
Question 3: Can I use this user account to login to my domain controller using remote desktop? I tried adding this user to remote users, but in vain.
Hi there,
Just a few of my thoughts....
Question 1: Do I need to give any additional permissions to this user to work with SQL?
Well, going off what you have said (making the domain user part of the sysadmin server role) this will allow the domain user to do any action on your database server.
Whether this is a good thing or not is debatable as it's pretty good practice to only give a user the lowest level of permissions they need in order to do whatever work they need to do. Not knowing your exact situation, giving the domain user the level of privilege you have might be overkill - but like I said it depends on what you're intending etc.
I would recommend you review security topics in SQL Server Books Online and use the info there in conjunction with your knowledge of the circumstances to select the appropriate strategy for giving permission to this user and any other users whose accounts you need to add.
Question 2: How can I test this user for basic SQL operations like database creation? Can I use Osql?
There are a few options here, I'll go into a few....
A) Yes you can use the OSQL or iSQL command line utilities to execute T-SQL if you're working with SQL Server 2000. Once again, SQL Server Books Online is a good resource for this.
If you're working with SQL Server 2005, it's better to use sqlcmd if you're looking to do things via a command line (http://msdn2.microsoft.com/en-us/library/ms170207(SQL.90).aspx)
B) It's easier to use graphical tools, I think. If you can use something like Query Analyzer (SQL Server 2000) or Management Studio (SQL Server 2005) to connect to your DB as the domain user (e.g. logging onto a machine with those tools installed as the domain user and then using those tools to connect to your database server) then that would probably be easier
Question 3: Can I use this user account to login to my domain controller using remote desktop? I tried adding this user to remote users, but in vain.
From what you've written it seems like you're doing the right thing. Here's a tutorial on the complete process which may help: http://www.windowsnetworking.com/articles_tutorials/Windows_2003_Terminal_Services_Part1.html
(Note that it's split into two parts...The link I've included is to the first part but the first part connects to the second part via another link)
TechNet isn't bad either: http://technet2.microsoft.com/windowsserver/en/technologies/featured/termserv/default.mspx
Like I said, from what you've written it's a little hard to gauge the exact problem but it seems like you're doing the right things. However, if you want to run through the steps in the articles & stuff I've placed links to above then things should work for your Terminal Services setup (they did for me)
========
Hope this helps a bit
|||
Thanks alot for your help. Sorry for a delayed reply as I was reading the material you suggested and the good news is that I am able to implement the same and have got things working.
ref - Question 1: As for the permissions, as you suggested, I no longer give the user sysadmin role. The user has dbowner role and it suffices.
ref-Question 2: I used Query Analyser itself by logging in to the machine as the domain user.
ref- Question 3: I could not get Remote Desktop to work for the user. I guess some licensing issue. As a workaround, I used VNC to login as the domain user.
Thanks again.
Permissions Changed
Hello!
At some point over the weekend my user account (which is an admin
account) lost the ability to perform administrative tasks in SQL
Server 2000. I can no longer create a database, create users, modify
existing tables, etc. Three days ago I was able to create a database
with no problems. The network guy and I are trying to figure out what
caused this problem. It seems that all admin accounts have lost these
permissions. Does anyone have any suggestions what I can check for?
Thanks,
--
JerryCheck out your Logins in SQL Server, whether or not for instance the local Administrators group is
still there, and whether it is a member of the sysadmin server role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> Hello!
> At some point over the weekend my user account (which is an admin
> account) lost the ability to perform administrative tasks in SQL
> Server 2000. I can no longer create a database, create users, modify
> existing tables, etc. Three days ago I was able to create a database
> with no problems. The network guy and I are trying to figure out what
> caused this problem. It seems that all admin accounts have lost these
> permissions. Does anyone have any suggestions what I can check for?
> Thanks,
> --
> Jerry
>|||Your account could be removed from Domain Admins or Local Admins group.
It seems that you still are able to login to your SQL Server. In a false
scenario, you might be a member of Domain Users and Domain Admins groups.
And these groups may have logins in SQL Server. Let's assume that Domain
Users login has only public role and Domain Admins login has sysadmin role.
If you are removed from Domain Admins Windows Group, then you lost your
sysadmin role right and now you login to your SQL Server only as using
public role. (I setup a role like this cause you mentioned about a network
and I thought there could be a domain in your environment)
Your situation must be something like this. Check out your logins as Tiber
also mentioned.
--
Ekrem Önsoy
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> Hello!
> At some point over the weekend my user account (which is an admin
> account) lost the ability to perform administrative tasks in SQL
> Server 2000. I can no longer create a database, create users, modify
> existing tables, etc. Three days ago I was able to create a database
> with no problems. The network guy and I are trying to figure out what
> caused this problem. It seems that all admin accounts have lost these
> permissions. Does anyone have any suggestions what I can check for?
> Thanks,
> --
> Jerry
>|||Thanks for the reply. I checked and the admin group that was set up
for me is still in the SQL Users list. Unfortunately my account no
longer has the permissions it needs to check if it's in the sysadmins
group.
--
Jerry
On Oct 2, 3:46 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Check out your Logins in SQL Server, whether or not for instance the local Administrators group is
> still there, and whether it is a member of the sysadmin server role.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Jerry" <jerrya...@.gmail.com> wrote in message
> news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> > Hello!
> > At some point over the weekend my user account (which is an admin
> > account) lost the ability to perform administrative tasks in SQL
> > Server 2000. I can no longer create a database, create users, modify
> > existing tables, etc. Three days ago I was able to create a database
> > with no problems. The network guy and I are trying to figure out what
> > caused this problem. It seems that all admin accounts have lost these
> > permissions. Does anyone have any suggestions what I can check for?
>|||We have an admin group set up that is a part of the Windows
Administrators group. We're going to try to see if this group is a
part of the sysadmins role in SQL Server.
--
Jerry
On Oct 3, 3:29 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Your account could be removed from Domain Admins or Local Admins group.
> It seems that you still are able to login to your SQL Server. In a false
> scenario, you might be a member of Domain Users and Domain Admins groups.
> And these groups may have logins in SQL Server. Let's assume that Domain
> Users login has only public role and Domain Admins login has sysadmin rol=e=2E
> If you are removed from Domain Admins Windows Group, then you lost your
> sysadmin role right and now you login to your SQL Server only as using
> public role. (I setup a role like this cause you mentioned about a network
> and I thought there could be a domain in your environment)
> Your situation must be something like this. Check out your logins as Tiber
> also mentioned.
> --
> Ekrem =D6nsoy
> "Jerry" <jerrya...@.gmail.com> wrote in message
> news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> > Hello!
> > At some point over the weekend my user account (which is an admin
> > account) lost the ability to perform administrative tasks in SQL
> > Server 2000. I can no longer create a database, create users, modify
> > existing tables, etc. Three days ago I was able to create a database
> > with no problems. The network guy and I are trying to figure out what
> > caused this problem. It seems that all admin accounts have lost these
> > permissions. Does anyone have any suggestions what I can check for?
>|||> Unfortunately my account no
> longer has the permissions it needs to check if it's in the sysadmins
> group.
It seems it isn't in the sysadmin group, or you would have been able to check that (assuming you are
logged in though this Windows group). So you need to go and find somebody who has privileges to
check this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1191500677.281166.191150@.w3g2000hsg.googlegroups.com...
> Thanks for the reply. I checked and the admin group that was set up
> for me is still in the SQL Users list. Unfortunately my account no
> longer has the permissions it needs to check if it's in the sysadmins
> group.
> --
> Jerry
> On Oct 2, 3:46 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Check out your Logins in SQL Server, whether or not for instance the local Administrators group
>> is
>> still there, and whether it is a member of the sysadmin server role.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Jerry" <jerrya...@.gmail.com> wrote in message
>> news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
>> > Hello!
>> > At some point over the weekend my user account (which is an admin
>> > account) lost the ability to perform administrative tasks in SQL
>> > Server 2000. I can no longer create a database, create users, modify
>> > existing tables, etc. Three days ago I was able to create a database
>> > with no problems. The network guy and I are trying to figure out what
>> > caused this problem. It seems that all admin accounts have lost these
>> > permissions. Does anyone have any suggestions what I can check for?
>|||Tibor,
I found an account to get in and did find that the group was not in
the sysadmin role. This was in the sysadmin role on Friday but on
Monday it wasn't there. Do you have any ideas what might have caused
this?
Thanks for your advice.
--
Jerry
On Oct 4, 7:50 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Unfortunately my account no
> > longer has the permissions it needs to check if it's in the sysadmins
> > group.
> It seems it isn't in the sysadmin group, or you would have been able to check that (assuming you are
> logged in though this Windows group). So you need to go and find somebody who has privileges to
> check this...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Jerry" <jerrya...@.gmail.com> wrote in message
> news:1191500677.281166.191150@.w3g2000hsg.googlegroups.com...
> > Thanks for the reply. I checked and the admin group that was set up
> > for me is still in the SQL Users list. Unfortunately my account no
> > longer has the permissions it needs to check if it's in the sysadmins
> > group.
> > --
> > Jerry
> > On Oct 2, 3:46 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> Check out your Logins in SQL Server, whether or not for instance the local Administrators group
> >> is
> >> still there, and whether it is a member of the sysadmin server role.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "Jerry" <jerrya...@.gmail.com> wrote in message
> >>news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> >> > Hello!
> >> > At some point over the weekend my user account (which is an admin
> >> > account) lost the ability to perform administrative tasks in SQL
> >> > Server 2000. I can no longer create a database, create users, modify
> >> > existing tables, etc. Three days ago I was able to create a database
> >> > with no problems. The network guy and I are trying to figure out what
> >> > caused this problem. It seems that all admin accounts have lost these
> >> > permissions. Does anyone have any suggestions what I can check for?|||> Do you have any ideas what might have caused
> this?
Most probably somebody removed it and:
* Don't remember it
* Didn't understand what he/she was doing
* Don't dare to come forward
I don't recall whether these events are logged to the default profiler trace (see the trc files in
same folder as where you find the errorlog file), but it is worth a shot to see if you find this
event there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1191505654.310090.315540@.k79g2000hse.googlegroups.com...
> Tibor,
> I found an account to get in and did find that the group was not in
> the sysadmin role. This was in the sysadmin role on Friday but on
> Monday it wasn't there. Do you have any ideas what might have caused
> this?
> Thanks for your advice.
> --
> Jerry
>
> On Oct 4, 7:50 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> > Unfortunately my account no
>> > longer has the permissions it needs to check if it's in the sysadmins
>> > group.
>> It seems it isn't in the sysadmin group, or you would have been able to check that (assuming you
>> are
>> logged in though this Windows group). So you need to go and find somebody who has privileges to
>> check this...
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Jerry" <jerrya...@.gmail.com> wrote in message
>> news:1191500677.281166.191150@.w3g2000hsg.googlegroups.com...
>> > Thanks for the reply. I checked and the admin group that was set up
>> > for me is still in the SQL Users list. Unfortunately my account no
>> > longer has the permissions it needs to check if it's in the sysadmins
>> > group.
>> > --
>> > Jerry
>> > On Oct 2, 3:46 pm, "Tibor Karaszi"
>> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> >> Check out your Logins in SQL Server, whether or not for instance the local Administrators
>> >> group
>> >> is
>> >> still there, and whether it is a member of the sysadmin server role.
>> >> --
>> >> Tibor Karaszi, SQL Server
>> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
>> >> "Jerry" <jerrya...@.gmail.com> wrote in message
>> >>news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
>> >> > Hello!
>> >> > At some point over the weekend my user account (which is an admin
>> >> > account) lost the ability to perform administrative tasks in SQL
>> >> > Server 2000. I can no longer create a database, create users, modify
>> >> > existing tables, etc. Three days ago I was able to create a database
>> >> > with no problems. The network guy and I are trying to figure out what
>> >> > caused this problem. It seems that all admin accounts have lost these
>> >> > permissions. Does anyone have any suggestions what I can check for?
>|||I'll take a look. I'm suspecting it was someone who didn't know what
they did.
On Oct 4, 9:56 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Do you have any ideas what might have caused
> > this?
> Most probably somebody removed it and:
> * Don't remember it
> * Didn't understand what he/she was doing
> * Don't dare to come forward
> I don't recall whether these events are logged to the default profiler trace (see the trc files in
> same folder as where you find the errorlog file), but it is worth a shot to see if you find this
> event there.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Jerry" <jerrya...@.gmail.com> wrote in message
> news:1191505654.310090.315540@.k79g2000hse.googlegroups.com...
> > Tibor,
> > I found an account to get in and did find that the group was not in
> > the sysadmin role. This was in the sysadmin role on Friday but on
> > Monday it wasn't there. Do you have any ideas what might have caused
> > this?
> > Thanks for your advice.
> > --
> > Jerry
> > On Oct 4, 7:50 am, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> > Unfortunately my account no
> >> > longer has the permissions it needs to check if it's in the sysadmins
> >> > group.
> >> It seems it isn't in the sysadmin group, or you would have been able to check that (assuming you
> >> are
> >> logged in though this Windows group). So you need to go and find somebody who has privileges to
> >> check this...
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "Jerry" <jerrya...@.gmail.com> wrote in message
> >>news:1191500677.281166.191150@.w3g2000hsg.googlegroups.com...
> >> > Thanks for the reply. I checked and the admin group that was set up
> >> > for me is still in the SQL Users list. Unfortunately my account no
> >> > longer has the permissions it needs to check if it's in the sysadmins
> >> > group.
> >> > --
> >> > Jerry
> >> > On Oct 2, 3:46 pm, "Tibor Karaszi"
> >> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> >> Check out your Logins in SQL Server, whether or not for instance the local Administrators
> >> >> group
> >> >> is
> >> >> still there, and whether it is a member of the sysadmin server role.
> >> >> --
> >> >> Tibor Karaszi, SQL Server
> >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> >> "Jerry" <jerrya...@.gmail.com> wrote in message
> >> >>news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> >> >> > Hello!
> >> >> > At some point over the weekend my user account (which is an admin
> >> >> > account) lost the ability to perform administrative tasks in SQL
> >> >> > Server 2000. I can no longer create a database, create users, modify
> >> >> > existing tables, etc. Three days ago I was able to create a database
> >> >> > with no problems. The network guy and I are trying to figure out what
> >> >> > caused this problem. It seems that all admin accounts have lost these
> >> >> > permissions. Does anyone have any suggestions what I can check for?
At some point over the weekend my user account (which is an admin
account) lost the ability to perform administrative tasks in SQL
Server 2000. I can no longer create a database, create users, modify
existing tables, etc. Three days ago I was able to create a database
with no problems. The network guy and I are trying to figure out what
caused this problem. It seems that all admin accounts have lost these
permissions. Does anyone have any suggestions what I can check for?
Thanks,
--
JerryCheck out your Logins in SQL Server, whether or not for instance the local Administrators group is
still there, and whether it is a member of the sysadmin server role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> Hello!
> At some point over the weekend my user account (which is an admin
> account) lost the ability to perform administrative tasks in SQL
> Server 2000. I can no longer create a database, create users, modify
> existing tables, etc. Three days ago I was able to create a database
> with no problems. The network guy and I are trying to figure out what
> caused this problem. It seems that all admin accounts have lost these
> permissions. Does anyone have any suggestions what I can check for?
> Thanks,
> --
> Jerry
>|||Your account could be removed from Domain Admins or Local Admins group.
It seems that you still are able to login to your SQL Server. In a false
scenario, you might be a member of Domain Users and Domain Admins groups.
And these groups may have logins in SQL Server. Let's assume that Domain
Users login has only public role and Domain Admins login has sysadmin role.
If you are removed from Domain Admins Windows Group, then you lost your
sysadmin role right and now you login to your SQL Server only as using
public role. (I setup a role like this cause you mentioned about a network
and I thought there could be a domain in your environment)
Your situation must be something like this. Check out your logins as Tiber
also mentioned.
--
Ekrem Önsoy
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> Hello!
> At some point over the weekend my user account (which is an admin
> account) lost the ability to perform administrative tasks in SQL
> Server 2000. I can no longer create a database, create users, modify
> existing tables, etc. Three days ago I was able to create a database
> with no problems. The network guy and I are trying to figure out what
> caused this problem. It seems that all admin accounts have lost these
> permissions. Does anyone have any suggestions what I can check for?
> Thanks,
> --
> Jerry
>|||Thanks for the reply. I checked and the admin group that was set up
for me is still in the SQL Users list. Unfortunately my account no
longer has the permissions it needs to check if it's in the sysadmins
group.
--
Jerry
On Oct 2, 3:46 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Check out your Logins in SQL Server, whether or not for instance the local Administrators group is
> still there, and whether it is a member of the sysadmin server role.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Jerry" <jerrya...@.gmail.com> wrote in message
> news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> > Hello!
> > At some point over the weekend my user account (which is an admin
> > account) lost the ability to perform administrative tasks in SQL
> > Server 2000. I can no longer create a database, create users, modify
> > existing tables, etc. Three days ago I was able to create a database
> > with no problems. The network guy and I are trying to figure out what
> > caused this problem. It seems that all admin accounts have lost these
> > permissions. Does anyone have any suggestions what I can check for?
>|||We have an admin group set up that is a part of the Windows
Administrators group. We're going to try to see if this group is a
part of the sysadmins role in SQL Server.
--
Jerry
On Oct 3, 3:29 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Your account could be removed from Domain Admins or Local Admins group.
> It seems that you still are able to login to your SQL Server. In a false
> scenario, you might be a member of Domain Users and Domain Admins groups.
> And these groups may have logins in SQL Server. Let's assume that Domain
> Users login has only public role and Domain Admins login has sysadmin rol=e=2E
> If you are removed from Domain Admins Windows Group, then you lost your
> sysadmin role right and now you login to your SQL Server only as using
> public role. (I setup a role like this cause you mentioned about a network
> and I thought there could be a domain in your environment)
> Your situation must be something like this. Check out your logins as Tiber
> also mentioned.
> --
> Ekrem =D6nsoy
> "Jerry" <jerrya...@.gmail.com> wrote in message
> news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> > Hello!
> > At some point over the weekend my user account (which is an admin
> > account) lost the ability to perform administrative tasks in SQL
> > Server 2000. I can no longer create a database, create users, modify
> > existing tables, etc. Three days ago I was able to create a database
> > with no problems. The network guy and I are trying to figure out what
> > caused this problem. It seems that all admin accounts have lost these
> > permissions. Does anyone have any suggestions what I can check for?
>|||> Unfortunately my account no
> longer has the permissions it needs to check if it's in the sysadmins
> group.
It seems it isn't in the sysadmin group, or you would have been able to check that (assuming you are
logged in though this Windows group). So you need to go and find somebody who has privileges to
check this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1191500677.281166.191150@.w3g2000hsg.googlegroups.com...
> Thanks for the reply. I checked and the admin group that was set up
> for me is still in the SQL Users list. Unfortunately my account no
> longer has the permissions it needs to check if it's in the sysadmins
> group.
> --
> Jerry
> On Oct 2, 3:46 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Check out your Logins in SQL Server, whether or not for instance the local Administrators group
>> is
>> still there, and whether it is a member of the sysadmin server role.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Jerry" <jerrya...@.gmail.com> wrote in message
>> news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
>> > Hello!
>> > At some point over the weekend my user account (which is an admin
>> > account) lost the ability to perform administrative tasks in SQL
>> > Server 2000. I can no longer create a database, create users, modify
>> > existing tables, etc. Three days ago I was able to create a database
>> > with no problems. The network guy and I are trying to figure out what
>> > caused this problem. It seems that all admin accounts have lost these
>> > permissions. Does anyone have any suggestions what I can check for?
>|||Tibor,
I found an account to get in and did find that the group was not in
the sysadmin role. This was in the sysadmin role on Friday but on
Monday it wasn't there. Do you have any ideas what might have caused
this?
Thanks for your advice.
--
Jerry
On Oct 4, 7:50 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Unfortunately my account no
> > longer has the permissions it needs to check if it's in the sysadmins
> > group.
> It seems it isn't in the sysadmin group, or you would have been able to check that (assuming you are
> logged in though this Windows group). So you need to go and find somebody who has privileges to
> check this...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Jerry" <jerrya...@.gmail.com> wrote in message
> news:1191500677.281166.191150@.w3g2000hsg.googlegroups.com...
> > Thanks for the reply. I checked and the admin group that was set up
> > for me is still in the SQL Users list. Unfortunately my account no
> > longer has the permissions it needs to check if it's in the sysadmins
> > group.
> > --
> > Jerry
> > On Oct 2, 3:46 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> Check out your Logins in SQL Server, whether or not for instance the local Administrators group
> >> is
> >> still there, and whether it is a member of the sysadmin server role.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "Jerry" <jerrya...@.gmail.com> wrote in message
> >>news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> >> > Hello!
> >> > At some point over the weekend my user account (which is an admin
> >> > account) lost the ability to perform administrative tasks in SQL
> >> > Server 2000. I can no longer create a database, create users, modify
> >> > existing tables, etc. Three days ago I was able to create a database
> >> > with no problems. The network guy and I are trying to figure out what
> >> > caused this problem. It seems that all admin accounts have lost these
> >> > permissions. Does anyone have any suggestions what I can check for?|||> Do you have any ideas what might have caused
> this?
Most probably somebody removed it and:
* Don't remember it
* Didn't understand what he/she was doing
* Don't dare to come forward
I don't recall whether these events are logged to the default profiler trace (see the trc files in
same folder as where you find the errorlog file), but it is worth a shot to see if you find this
event there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1191505654.310090.315540@.k79g2000hse.googlegroups.com...
> Tibor,
> I found an account to get in and did find that the group was not in
> the sysadmin role. This was in the sysadmin role on Friday but on
> Monday it wasn't there. Do you have any ideas what might have caused
> this?
> Thanks for your advice.
> --
> Jerry
>
> On Oct 4, 7:50 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> > Unfortunately my account no
>> > longer has the permissions it needs to check if it's in the sysadmins
>> > group.
>> It seems it isn't in the sysadmin group, or you would have been able to check that (assuming you
>> are
>> logged in though this Windows group). So you need to go and find somebody who has privileges to
>> check this...
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Jerry" <jerrya...@.gmail.com> wrote in message
>> news:1191500677.281166.191150@.w3g2000hsg.googlegroups.com...
>> > Thanks for the reply. I checked and the admin group that was set up
>> > for me is still in the SQL Users list. Unfortunately my account no
>> > longer has the permissions it needs to check if it's in the sysadmins
>> > group.
>> > --
>> > Jerry
>> > On Oct 2, 3:46 pm, "Tibor Karaszi"
>> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> >> Check out your Logins in SQL Server, whether or not for instance the local Administrators
>> >> group
>> >> is
>> >> still there, and whether it is a member of the sysadmin server role.
>> >> --
>> >> Tibor Karaszi, SQL Server
>> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
>> >> "Jerry" <jerrya...@.gmail.com> wrote in message
>> >>news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
>> >> > Hello!
>> >> > At some point over the weekend my user account (which is an admin
>> >> > account) lost the ability to perform administrative tasks in SQL
>> >> > Server 2000. I can no longer create a database, create users, modify
>> >> > existing tables, etc. Three days ago I was able to create a database
>> >> > with no problems. The network guy and I are trying to figure out what
>> >> > caused this problem. It seems that all admin accounts have lost these
>> >> > permissions. Does anyone have any suggestions what I can check for?
>|||I'll take a look. I'm suspecting it was someone who didn't know what
they did.
On Oct 4, 9:56 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Do you have any ideas what might have caused
> > this?
> Most probably somebody removed it and:
> * Don't remember it
> * Didn't understand what he/she was doing
> * Don't dare to come forward
> I don't recall whether these events are logged to the default profiler trace (see the trc files in
> same folder as where you find the errorlog file), but it is worth a shot to see if you find this
> event there.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Jerry" <jerrya...@.gmail.com> wrote in message
> news:1191505654.310090.315540@.k79g2000hse.googlegroups.com...
> > Tibor,
> > I found an account to get in and did find that the group was not in
> > the sysadmin role. This was in the sysadmin role on Friday but on
> > Monday it wasn't there. Do you have any ideas what might have caused
> > this?
> > Thanks for your advice.
> > --
> > Jerry
> > On Oct 4, 7:50 am, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> > Unfortunately my account no
> >> > longer has the permissions it needs to check if it's in the sysadmins
> >> > group.
> >> It seems it isn't in the sysadmin group, or you would have been able to check that (assuming you
> >> are
> >> logged in though this Windows group). So you need to go and find somebody who has privileges to
> >> check this...
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "Jerry" <jerrya...@.gmail.com> wrote in message
> >>news:1191500677.281166.191150@.w3g2000hsg.googlegroups.com...
> >> > Thanks for the reply. I checked and the admin group that was set up
> >> > for me is still in the SQL Users list. Unfortunately my account no
> >> > longer has the permissions it needs to check if it's in the sysadmins
> >> > group.
> >> > --
> >> > Jerry
> >> > On Oct 2, 3:46 pm, "Tibor Karaszi"
> >> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> >> Check out your Logins in SQL Server, whether or not for instance the local Administrators
> >> >> group
> >> >> is
> >> >> still there, and whether it is a member of the sysadmin server role.
> >> >> --
> >> >> Tibor Karaszi, SQL Server
> >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> >> "Jerry" <jerrya...@.gmail.com> wrote in message
> >> >>news:1191357676.328164.54550@.50g2000hsm.googlegroups.com...
> >> >> > Hello!
> >> >> > At some point over the weekend my user account (which is an admin
> >> >> > account) lost the ability to perform administrative tasks in SQL
> >> >> > Server 2000. I can no longer create a database, create users, modify
> >> >> > existing tables, etc. Three days ago I was able to create a database
> >> >> > with no problems. The network guy and I are trying to figure out what
> >> >> > caused this problem. It seems that all admin accounts have lost these
> >> >> > permissions. Does anyone have any suggestions what I can check for?
Friday, March 9, 2012
Permissions
I'm using sql 2000 and access 2002. I've created two
groups, users and admin. I've created two forms based on
two queries, FullView and LimitedView. Each query is
based on 10 different tables and each form is meant to be
read only. The only difference between the two queries is
a few fields that contain sensitive data.
I'm trying to use Enterprise Manager to assign
permissions. I've given the user and admin groups
db_datareader and db_datawriter permissions (both groups
will need to have write permission on other tables). I've
given both groups Select, Insert and Update on each of
the Tables the queries are based on.
I've given Select permissions for the admin group on the
FullView query and denied permissions to the users group.
And I've given Select permissions to users on the
LimitedView query.
After doing this the users can access both the FullView
and LimitedView queries.
What I'm doing wrong? Also, can anyone point me to some
information on assigning permissions?
Thanks for the help,
PaulIn future, you should start a new thread for a question instead of
piggybacking on an existing thread. Many people smiply blow by
questions that have replies. Here's a good resource to help you get
started with SQL Server security:
[url]http://www.microsoft.com/sql/techinfo/administration/2000/security/default.asp[/ur
l]
The reason the users can see both views is that you've granted them
permissions to select from the base tables. Bear in mind that if you
want users to update data through the view using Access you have to
use the VIEW_METADATA syntax when creating the view.
--Mary
On Mon, 16 Aug 2004 05:31:00 -0700, "Paul Scott"
<anonymous@.discussions.microsoft.com> wrote:
>I'm using sql 2000 and access 2002. I've created two
>groups, users and admin. I've created two forms based on
>two queries, FullView and LimitedView. Each query is
>based on 10 different tables and each form is meant to be
>read only. The only difference between the two queries is
>a few fields that contain sensitive data.
>I'm trying to use Enterprise Manager to assign
>permissions. I've given the user and admin groups
>db_datareader and db_datawriter permissions (both groups
>will need to have write permission on other tables). I've
>given both groups Select, Insert and Update on each of
>the Tables the queries are based on.
>I've given Select permissions for the admin group on the
>FullView query and denied permissions to the users group.
>And I've given Select permissions to users on the
>LimitedView query.
>After doing this the users can access both the FullView
>and LimitedView queries.
>What I'm doing wrong? Also, can anyone point me to some
>information on assigning permissions?
>Thanks for the help,
>Paul
groups, users and admin. I've created two forms based on
two queries, FullView and LimitedView. Each query is
based on 10 different tables and each form is meant to be
read only. The only difference between the two queries is
a few fields that contain sensitive data.
I'm trying to use Enterprise Manager to assign
permissions. I've given the user and admin groups
db_datareader and db_datawriter permissions (both groups
will need to have write permission on other tables). I've
given both groups Select, Insert and Update on each of
the Tables the queries are based on.
I've given Select permissions for the admin group on the
FullView query and denied permissions to the users group.
And I've given Select permissions to users on the
LimitedView query.
After doing this the users can access both the FullView
and LimitedView queries.
What I'm doing wrong? Also, can anyone point me to some
information on assigning permissions?
Thanks for the help,
PaulIn future, you should start a new thread for a question instead of
piggybacking on an existing thread. Many people smiply blow by
questions that have replies. Here's a good resource to help you get
started with SQL Server security:
[url]http://www.microsoft.com/sql/techinfo/administration/2000/security/default.asp[/ur
l]
The reason the users can see both views is that you've granted them
permissions to select from the base tables. Bear in mind that if you
want users to update data through the view using Access you have to
use the VIEW_METADATA syntax when creating the view.
--Mary
On Mon, 16 Aug 2004 05:31:00 -0700, "Paul Scott"
<anonymous@.discussions.microsoft.com> wrote:
>I'm using sql 2000 and access 2002. I've created two
>groups, users and admin. I've created two forms based on
>two queries, FullView and LimitedView. Each query is
>based on 10 different tables and each form is meant to be
>read only. The only difference between the two queries is
>a few fields that contain sensitive data.
>I'm trying to use Enterprise Manager to assign
>permissions. I've given the user and admin groups
>db_datareader and db_datawriter permissions (both groups
>will need to have write permission on other tables). I've
>given both groups Select, Insert and Update on each of
>the Tables the queries are based on.
>I've given Select permissions for the admin group on the
>FullView query and denied permissions to the users group.
>And I've given Select permissions to users on the
>LimitedView query.
>After doing this the users can access both the FullView
>and LimitedView queries.
>What I'm doing wrong? Also, can anyone point me to some
>information on assigning permissions?
>Thanks for the help,
>Paul
Wednesday, March 7, 2012
permission problems running SQLExpress under terminal server
Hi all,
I've installed SQLExpress under terminal server, and everything worked
great.
Except, when the terminal server users don't have admin access, my program
crashes.
I noticed when I used Microsoft SQL Server Management Studio express on the
non admin accounts, I couldn't view the tables.
I went to the standard folder C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data
and windows denied me acess to that folder.
My question is, How can I let the users with non admin access run my program
without having them access to the data folder.
Also, is there a quick fix for my problem above to get it working? giving
admin access to everyone isn't an option.
p.s I've installed sqlexpress in Authentication Method = "Windows
Authentication"
p.s.s I've research on user instances, but felt that it didn't solve my
problem. Every user must access the same centralised data.
cheers
AstroHi
The programm connects to SQL Server via a login that you defined on the
server. So non admin users are running the programm in context of the login
the application connects to. You mentioned that program is crashed, what is
the error?
Okay, I see you set up SQL Server with Windows Authentication mode only.
Create a group on the machine where SQL Server run and add those users tio
the group. It is up to you wether or not not the users are member of syadmin
server role.
<astroboyfusion@.yahoo.com.au> wrote in message
news:46806b20$0$46400$c30e37c6@.pit-reader.telstra.net...
> Hi all,
> I've installed SQLExpress under terminal server, and everything worked
> great.
> Except, when the terminal server users don't have admin access, my program
> crashes.
> I noticed when I used Microsoft SQL Server Management Studio express on
> the
> non admin accounts, I couldn't view the tables.
> I went to the standard folder C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data
> and windows denied me acess to that folder.
> My question is, How can I let the users with non admin access run my
> program
> without having them access to the data folder.
> Also, is there a quick fix for my problem above to get it working? giving
> admin access to everyone isn't an option.
>
> p.s I've installed sqlexpress in Authentication Method = "Windows
> Authentication"
> p.s.s I've research on user instances, but felt that it didn't solve my
> problem. Every user must access the same centralised data.
> cheers
> Astro
>
>|||(astroboyfusion@.yahoo.com.au) writes:
> I've installed SQLExpress under terminal server, and everything worked
> great.
> Except, when the terminal server users don't have admin access, my program
> crashes.
And the error message is?
> I noticed when I used Microsoft SQL Server Management Studio express on
> the non admin accounts, I couldn't view the tables.
> I went to the standard folder C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data
> and windows denied me acess to that folder.
> My question is, How can I let the users with non admin access run my
> program without having them access to the data folder.
It appears that you implemented your program without thinking or knowing
about security. Well, here are the bad news: you need to learn that now.
Exactly what you need to do, depends on what your application is up to.
If all you do is to access tables, it's only a matter of granting
table permissions. Of course, had you used stored procedure, all you
would need would be to grant rights to run the procedure. But if your
application also bulk-load files etc, you may need heftier stuff.
--
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|||Thankyou Uri and Erland.
The way I solved the error 229 for now was changing the permission under
Security\Login\BuiltIn\Users
under "Server Roles" page and checked sysadmin.
-- Original Message --
From: "Erland Sommarskog" <esquel@.sommarskog.se>
Newsgroups: microsoft.public.sqlserver.security
Sent: Wednesday, June 27, 2007 8:09 AM
Subject: Re: permission problems running SQLExpress under terminal server
> (astroboyfusion@.yahoo.com.au) writes:
program[vbcol=seagreen]
> And the error message is?
I'm sorry Erland, I should have given the details of the error.
---
"failed to retrieve data for this request."
microsoft.sqlserver.express.smoenum
(Microsoft SQL Server, Error: 229)
---
> It appears that you implemented your program without thinking or knowing
> about security. Well, here are the bad news: you need to learn that now.
Your comment about security is spot on, I had to quickly migrate from Access
to Sqlserver. This is an incomplete project which I picked up to complete.
> Exactly what you need to do, depends on what your application is up to.
> If all you do is to access tables, it's only a matter of granting
> table permissions. Of course, had you used stored procedure, all you
> would need would be to grant rights to run the procedure. But if your
> application also bulk-load files etc, you may need heftier stuff.
> --
> 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
I've installed SQLExpress under terminal server, and everything worked
great.
Except, when the terminal server users don't have admin access, my program
crashes.
I noticed when I used Microsoft SQL Server Management Studio express on the
non admin accounts, I couldn't view the tables.
I went to the standard folder C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data
and windows denied me acess to that folder.
My question is, How can I let the users with non admin access run my program
without having them access to the data folder.
Also, is there a quick fix for my problem above to get it working? giving
admin access to everyone isn't an option.
p.s I've installed sqlexpress in Authentication Method = "Windows
Authentication"
p.s.s I've research on user instances, but felt that it didn't solve my
problem. Every user must access the same centralised data.
cheers
AstroHi
The programm connects to SQL Server via a login that you defined on the
server. So non admin users are running the programm in context of the login
the application connects to. You mentioned that program is crashed, what is
the error?
Okay, I see you set up SQL Server with Windows Authentication mode only.
Create a group on the machine where SQL Server run and add those users tio
the group. It is up to you wether or not not the users are member of syadmin
server role.
<astroboyfusion@.yahoo.com.au> wrote in message
news:46806b20$0$46400$c30e37c6@.pit-reader.telstra.net...
> Hi all,
> I've installed SQLExpress under terminal server, and everything worked
> great.
> Except, when the terminal server users don't have admin access, my program
> crashes.
> I noticed when I used Microsoft SQL Server Management Studio express on
> the
> non admin accounts, I couldn't view the tables.
> I went to the standard folder C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data
> and windows denied me acess to that folder.
> My question is, How can I let the users with non admin access run my
> program
> without having them access to the data folder.
> Also, is there a quick fix for my problem above to get it working? giving
> admin access to everyone isn't an option.
>
> p.s I've installed sqlexpress in Authentication Method = "Windows
> Authentication"
> p.s.s I've research on user instances, but felt that it didn't solve my
> problem. Every user must access the same centralised data.
> cheers
> Astro
>
>|||(astroboyfusion@.yahoo.com.au) writes:
> I've installed SQLExpress under terminal server, and everything worked
> great.
> Except, when the terminal server users don't have admin access, my program
> crashes.
And the error message is?
> I noticed when I used Microsoft SQL Server Management Studio express on
> the non admin accounts, I couldn't view the tables.
> I went to the standard folder C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data
> and windows denied me acess to that folder.
> My question is, How can I let the users with non admin access run my
> program without having them access to the data folder.
It appears that you implemented your program without thinking or knowing
about security. Well, here are the bad news: you need to learn that now.
Exactly what you need to do, depends on what your application is up to.
If all you do is to access tables, it's only a matter of granting
table permissions. Of course, had you used stored procedure, all you
would need would be to grant rights to run the procedure. But if your
application also bulk-load files etc, you may need heftier stuff.
--
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|||Thankyou Uri and Erland.
The way I solved the error 229 for now was changing the permission under
Security\Login\BuiltIn\Users
under "Server Roles" page and checked sysadmin.
-- Original Message --
From: "Erland Sommarskog" <esquel@.sommarskog.se>
Newsgroups: microsoft.public.sqlserver.security
Sent: Wednesday, June 27, 2007 8:09 AM
Subject: Re: permission problems running SQLExpress under terminal server
> (astroboyfusion@.yahoo.com.au) writes:
program[vbcol=seagreen]
> And the error message is?
I'm sorry Erland, I should have given the details of the error.
---
"failed to retrieve data for this request."
microsoft.sqlserver.express.smoenum
(Microsoft SQL Server, Error: 229)
---
> It appears that you implemented your program without thinking or knowing
> about security. Well, here are the bad news: you need to learn that now.
Your comment about security is spot on, I had to quickly migrate from Access
to Sqlserver. This is an incomplete project which I picked up to complete.
> Exactly what you need to do, depends on what your application is up to.
> If all you do is to access tables, it's only a matter of granting
> table permissions. Of course, had you used stored procedure, all you
> would need would be to grant rights to run the procedure. But if your
> application also bulk-load files etc, you may need heftier stuff.
> --
> 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
Saturday, February 25, 2012
permission needed to run CmdExec job steps
Hi, Can someone tell me what permissions are needed for logins to be able
to run CmdExec Jobs'
I recently removed everyone from System Admin and have been working through
all kind of issues. I now have some jobs that fail and here is the message
that they fail with>> "Non-SysAdmins have been denied permission to run
CmdExec job steps. The step failed." There are 3 Analysts who create DTS
packages and schedule them to run at various times, so they need to own
their jobs, but what it the minimum permissions they can be provided to be
able to run these type of jobs'
Thank you..How you would go about this depends on the version of SQL
Server - it's different for all versions. Since you mention
DTS I am guessing you are on SQL Server 2000.
On 2000, if a non-sysadmin is going to be running a CmdExec
job, first you need to enable SQL Agent to allow
non-sysadmins to execute CmdExec steps. Right click on SQL
Agent, select properties and then go to the Job System tab.
From here, remove the check which restricts CmdExec and
ActiveX job steps to Sysadmins only.
You will then need to configure the proxy account. The proxy
account is the security context that will be used for the
jobs. You can find information on the proxy account as well
as some of the security issues you are running into in books
online under: xp_sqlagent_proxy_account
-Sue
On Tue, 5 Sep 2006 16:08:48 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>Hi, Can someone tell me what permissions are needed for logins to be able
>to run CmdExec Jobs'
>I recently removed everyone from System Admin and have been working through
>all kind of issues. I now have some jobs that fail and here is the message
>that they fail with>> "Non-SysAdmins have been denied permission to run
>CmdExec job steps. The step failed." There are 3 Analysts who create DTS
>packages and schedule them to run at various times, so they need to own
>their jobs, but what it the minimum permissions they can be provided to be
>able to run these type of jobs'
>Thank you..
>|||Thanks Sue, You are correct SQL2000 is what I speak of. I believe you have
provided me with all the right stuff. I will read what I can find under
xp_sqlagent_proxy_account.. Thanks again.
=======================================
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nfvrf2dp55icp6a9r02i0hfdndjp77dr4k@.
4ax.com...
> How you would go about this depends on the version of SQL
> Server - it's different for all versions. Since you mention
> DTS I am guessing you are on SQL Server 2000.
> On 2000, if a non-sysadmin is going to be running a CmdExec
> job, first you need to enable SQL Agent to allow
> non-sysadmins to execute CmdExec steps. Right click on SQL
> Agent, select properties and then go to the Job System tab.
> From here, remove the check which restricts CmdExec and
> ActiveX job steps to Sysadmins only.
> You will then need to configure the proxy account. The proxy
> account is the security context that will be used for the
> jobs. You can find information on the proxy account as well
> as some of the security issues you are running into in books
> online under: xp_sqlagent_proxy_account
> -Sue
> On Tue, 5 Sep 2006 16:08:48 -0500, "WANNABE" <breichenbach
> AT istate DOT com> wrote:
>
>
to run CmdExec Jobs'
I recently removed everyone from System Admin and have been working through
all kind of issues. I now have some jobs that fail and here is the message
that they fail with>> "Non-SysAdmins have been denied permission to run
CmdExec job steps. The step failed." There are 3 Analysts who create DTS
packages and schedule them to run at various times, so they need to own
their jobs, but what it the minimum permissions they can be provided to be
able to run these type of jobs'
Thank you..How you would go about this depends on the version of SQL
Server - it's different for all versions. Since you mention
DTS I am guessing you are on SQL Server 2000.
On 2000, if a non-sysadmin is going to be running a CmdExec
job, first you need to enable SQL Agent to allow
non-sysadmins to execute CmdExec steps. Right click on SQL
Agent, select properties and then go to the Job System tab.
From here, remove the check which restricts CmdExec and
ActiveX job steps to Sysadmins only.
You will then need to configure the proxy account. The proxy
account is the security context that will be used for the
jobs. You can find information on the proxy account as well
as some of the security issues you are running into in books
online under: xp_sqlagent_proxy_account
-Sue
On Tue, 5 Sep 2006 16:08:48 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>Hi, Can someone tell me what permissions are needed for logins to be able
>to run CmdExec Jobs'
>I recently removed everyone from System Admin and have been working through
>all kind of issues. I now have some jobs that fail and here is the message
>that they fail with>> "Non-SysAdmins have been denied permission to run
>CmdExec job steps. The step failed." There are 3 Analysts who create DTS
>packages and schedule them to run at various times, so they need to own
>their jobs, but what it the minimum permissions they can be provided to be
>able to run these type of jobs'
>Thank you..
>|||Thanks Sue, You are correct SQL2000 is what I speak of. I believe you have
provided me with all the right stuff. I will read what I can find under
xp_sqlagent_proxy_account.. Thanks again.
=======================================
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nfvrf2dp55icp6a9r02i0hfdndjp77dr4k@.
4ax.com...
> How you would go about this depends on the version of SQL
> Server - it's different for all versions. Since you mention
> DTS I am guessing you are on SQL Server 2000.
> On 2000, if a non-sysadmin is going to be running a CmdExec
> job, first you need to enable SQL Agent to allow
> non-sysadmins to execute CmdExec steps. Right click on SQL
> Agent, select properties and then go to the Job System tab.
> From here, remove the check which restricts CmdExec and
> ActiveX job steps to Sysadmins only.
> You will then need to configure the proxy account. The proxy
> account is the security context that will be used for the
> jobs. You can find information on the proxy account as well
> as some of the security issues you are running into in books
> online under: xp_sqlagent_proxy_account
> -Sue
> On Tue, 5 Sep 2006 16:08:48 -0500, "WANNABE" <breichenbach
> AT istate DOT com> wrote:
>
>
Subscribe to:
Posts (Atom)