alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
I saw it work in a cartoon once so I am pretty sure I can do it.
Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.
|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
--
I saw it work in a cartoon once so I am pretty sure I can do it.Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
--
I saw it work in a cartoon once so I am pretty sure I can do it.Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

Allowing users to schedule jobs in Management Studio

How do I grant a non sysdba user who has bulkadmin and dbcreator rights to schedule jobs on databases they've created?

The user is a developer and we dont want to give him sysdba rights.

try if this works

use the Execute AS command which is used to impersonate any user

havent done much of a research on that one but u can easily get stuff on BOL

|||http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=12&faqid=137 for your information to accomplish the task.

Allowing SQL Users to change password

Hi guys,

Is there anyway to allow SQL Server users to change their own passwords without giving them rights to "db_securityadmin" ?

Our M$ adviser suggested creating our own app using SQL NS and DMO to do it.

Is there any easier method, reference, download sample from the web?from qa:

sp_password 'oldpassword', 'newpassword'|||Without giving them sysadmin or securityadmin , is it possible?
I mean ..just changing their own password...
I wrote this:
EXEC sp_password '123', '456', 'test1'

I got this error:

"Only members of the sysadmin role can use the loginame option. The password was not changed."

But I read this in Books Online, can't really understand it yet...:
Permissions
Execute permissions default to the public role for a user changing the password for his or her own login. Members of the securityadmin and sysadmin fixed server roles can change the password for another user's login.|||woppps...of course...just exclude the loginname...sorry bout that...
okay ..thanks..it works!

EXEC sp_password '123', '456'

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:

> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||Hello Jen,
The person in question is probably already in, but using a
role / group such as BUILTIN\ADMINISTRATORS.
Thats the reason why they can still do the delete, insert
and update stuff as well as the select.
Look under the security, logons and see which types are
Windows Groups, then have a chat to your Server Bods to
see if the user is already included in the group.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Hi,
>I am currently having problems assigning read only
privileges to a database
>for a specific user.
>Basically, I have an application which uses Windows
Authentication to access
>a database on SQL server 2000. I only want this user to
have read only access
>to this database. I have added the user into the Logins
in SQL server (i.e.
>domain\username) and granted them the db_datareader role
to that database. I
>was assuming that this would only allow them to have read
access to the
>database, but this is not the case as they can add,
modify and delete records.
>Any help/advice would be appreciated as this is driving
me mad.
>Thanks,
>Jen
>.
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
--
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only acces
s
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete record
s.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:

> Hi,
> I am currently having problems assigning read only privileges to a databas
e
> for a specific user.
> Basically, I have an application which uses Windows Authentication to acce
ss
> a database on SQL server 2000. I only want this user to have read only acc
ess
> to this database. I have added the user into the Logins in SQL server (i.e
.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete reco
rds.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing access to Enterprise Manager without giving admin rights.

I have a user that will be doing specific updates to a specific table
in SQL. Rather than have them working at the server when these needed
to be done, I thought I would install SQL Admin Tools at their
workstation. Does anyone know if I can do this, and allow him use of
Enterprise Manager to access this table, without giving him admin
rights. He will need to import an excel file into this table
periodically.
Thanks.There's no need to give him Admin rights in order to update a table on
occaison.
Simply grant him insert/update/delete permissions to the specific table.
Then write some vb code to insert the data from Excel to SQL.
321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/?id=321686
Or
Create a DTS Package on the server. Have the user put his Excel file on a
server share, and then
periodically have the DTS package scheduled to run and process the data.
319951 HOW TO: Transfer Data to Excel by Using SQL Server Data
Transformation
http://support.microsoft.com/?id=319951
Or
You could simply give him db_datareader, db_datawriter in the database.
See Fixed Database Roles in SQL Books Online
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.