Membership to db_securityadmin should be enough. Can you post the commands that you attempt and the resulting error message. Also, what version of SQL Server are you using?
Thanks
Laurentiu
Showing posts with label alter. Show all posts
Showing posts with label alter. Show all posts
Thursday, March 29, 2012
Granting permission to a database user to alter database role
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here? What should I do so that the user can create, and alter database roles in the database?
Tuesday, March 27, 2012
GRANT statement and Windows login
I am trying to grant a Windows login rights to run a Profiler trace.
I'm using the following command:
GRANT ALTER TRACE to 'DOMAIN\User'
but I keep getting the error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DOMAIN\User'.
I've tried it without the quotes and still get an error.
What am I missing?
ThanksNever mind, I figured it out:
GRANT ALTER TRACE to [DOMAIN\User]sql
I'm using the following command:
GRANT ALTER TRACE to 'DOMAIN\User'
but I keep getting the error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DOMAIN\User'.
I've tried it without the quotes and still get an error.
What am I missing?
ThanksNever mind, I figured it out:
GRANT ALTER TRACE to [DOMAIN\User]sql
GRANT statement and Windows login
I am trying to grant a Windows login rights to run a Profiler trace.
I'm using the following command:
GRANT ALTER TRACE to 'DOMAIN\User'
but I keep getting the error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DOMAIN\User'.
I've tried it without the quotes and still get an error.
What am I missing?
ThanksNever mind, I figured it out:
GRANT ALTER TRACE to [DOMAIN\User]
I'm using the following command:
GRANT ALTER TRACE to 'DOMAIN\User'
but I keep getting the error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DOMAIN\User'.
I've tried it without the quotes and still get an error.
What am I missing?
ThanksNever mind, I figured it out:
GRANT ALTER TRACE to [DOMAIN\User]
Grant rights to Truncate table to specific user 2000/2005
Hi
For certain "work" tables only, I would like to grant truncate table
ability.
In SQL2005 I can do
GRANT ALTER ON dbo.tbl#### TO myuser
Is there one syntax will work in both versions.
I don't mind if myuser is an owner of that table as long as dbo and
myuser can truncate it and other users can access it as dbo.tbl####.
I do not want to create myuser.tbl####
ThanksHi
In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
TO myuser
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>|||On 13 Nov, 14:14, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
> TO myuser
>
I tried that it returned "privilege alter table may not be granted or
revoked"
"incorrect syntax near 'on'".
Its a pain cos I want to clear these tables down quickly and don't
wanna fill log up. But do not wanna make this user dbo..|||I haven't used it yet, but I beleive you can write a proc that runs as dbo
and grant your user the rights to run the proc.
Should be easy enough to test.
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>
For certain "work" tables only, I would like to grant truncate table
ability.
In SQL2005 I can do
GRANT ALTER ON dbo.tbl#### TO myuser
Is there one syntax will work in both versions.
I don't mind if myuser is an owner of that table as long as dbo and
myuser can truncate it and other users can access it as dbo.tbl####.
I do not want to create myuser.tbl####
ThanksHi
In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
TO myuser
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>|||On 13 Nov, 14:14, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
> TO myuser
>
I tried that it returned "privilege alter table may not be granted or
revoked"
"incorrect syntax near 'on'".
Its a pain cos I want to clear these tables down quickly and don't
wanna fill log up. But do not wanna make this user dbo..|||I haven't used it yet, but I beleive you can write a proc that runs as dbo
and grant your user the rights to run the proc.
Should be easy enough to test.
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>
Friday, March 23, 2012
Grant create stored procedure in a specific schema
I want to let my developers to create/alter stored procedure only under a
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)Gal
Create a login with schema (give a mane) as a default . Those users will
connect with this login
"" <@.discussions.microsoft.com> wrote in message
news:1FD9FD0F-097C-4B3C-9E07-48D0D1AB4D87@.microsoft.com...
>I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which
> role
> let the login make ddl changes only in on schema (not in the dbo schema)
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)Gal
Create a login with schema (give a mane) as a default . Those users will
connect with this login
"" <@.discussions.microsoft.com> wrote in message
news:1FD9FD0F-097C-4B3C-9E07-48D0D1AB4D87@.microsoft.com...
>I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which
> role
> let the login make ddl changes only in on schema (not in the dbo schema)
GRANT CREATE DATABASE versus GRANT ALTER ANY LOGIN
OK, I obviously dont understand something really simple here. Could someone
help me see the light? :-)
Im logged in as a local machine admin.
C:\> SQLCMD -E
1> grant create database to [valen\nsLocalDBUsers]
2> go
1> grant alter any login to [valen\nsLocalDBUsers]
2> go
Msg 15151, Level 16, State 1, Server VALEN, Line 1
Cannot find the login 'valen\nsLocalDBUsers', because it does not exist or
you d
o not have permission.
1>
Thanks,
JoeHello Joe,
I understand that you log into SQL Server 2005 as a local admin but you
could not grant "alter any login" permission to a domain login. If I'm
off-base, please let me know.
I suspect "buitin\Administrators" group might have been removed from the
server or it has been removed "sysadmin" server role.
You may want to login as a domin user or sql login with sysadmin role,
grant yourself "sysadmin" role, and test it again
EXEC sp_helpsrvrolemember 'sysadmin'
EXEC sp_addsrvrolemember 'domain\username', 'sysadmin'
If you have any update, please feel free to let me know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Peter -
Well, sort of :-). I am allowed to do a "GRANT CREATE DATABASE TO", but
not allowed to do a "GRANT ALTER ANY LOGIN TO".
I have checked, and the builtin\administrator group still exists in SQL
server, and is a member of the sysadmin server role.
Regards,
Joe
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:6h$5$PNfHHA.4692@.TK2MSFTNGHUB02.phx.gbl...
> Hello Joe,
> I understand that you log into SQL Server 2005 as a local admin but you
> could not grant "alter any login" permission to a domain login. If I'm
> off-base, please let me know.
> I suspect "buitin\Administrators" group might have been removed from the
> server or it has been removed "sysadmin" server role.
> You may want to login as a domin user or sql login with sysadmin role,
> grant yourself "sysadmin" role, and test it again
> EXEC sp_helpsrvrolemember 'sysadmin'
> EXEC sp_addsrvrolemember 'domain\username', 'sysadmin'
> If you have any update, please feel free to let me know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Joe (jwdaigle@.nospam.nospam) writes:
> OK, I obviously dont understand something really simple here. Could
> someone help me see the light? :-)
> Im logged in as a local machine admin.
>
> C:\> SQLCMD -E
> 1> grant create database to [valen\nsLocalDBUsers]
> 2> go
> 1> grant alter any login to [valen\nsLocalDBUsers]
> 2> go
> Msg 15151, Level 16, State 1, Server VALEN, Line 1
> Cannot find the login 'valen\nsLocalDBUsers', because it does not exist or
> you d
> o not have permission.
> 1>
CREATE DATABASE is a database permission, ALTER ANY LOGIN is a server-
level permission. Database permissions are granted to database principals,
server permissions to logins.
It's certainly interesting that [valen\nsLocalDBUsers] is a database
user, but not a login, particularly if this is the master database.
But I would dig into sys.server_principals and sys.database_principals
to see what anomalies I could find.
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9911AB07D649Yazorman@.127.0.0.1...
> Joe (jwdaigle@.nospam.nospam) writes:
> CREATE DATABASE is a database permission, ALTER ANY LOGIN is a server-
> level permission. Database permissions are granted to database principals,
> server permissions to logins.
> It's certainly interesting that [valen\nsLocalDBUsers] is a database
> user, but not a login, particularly if this is the master database.
> But I would dig into sys.server_principals and sys.database_principals
> to see what anomalies I could find.
>
> --
> 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
Ah, I think I might see the problem now based on your response.
valen\nsLocalDBUsers is a group, not a user. It is a Windows group that I
added to the database in question using "sp_grantdbaccess". My guess is
that I did not first add it to the server because sp_grantdbaccess seems to
do the grantlogin at the server level "automatically".
Am I correct that I should have added the group to the server first, and
then granted access to the database? What is the recommended method for
adding a windows user/group to the server?
Thanks for any additional help, I am off to read the online help to
understand this better :-)
joe|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9911AB07D649Yazorman@.127.0.0.1...
> Joe (jwdaigle@.nospam.nospam) writes:
> CREATE DATABASE is a database permission, ALTER ANY LOGIN is a server-
> level permission. Database permissions are granted to database principals,
> server permissions to logins.
> It's certainly interesting that [valen\nsLocalDBUsers] is a database
> user, but not a login, particularly if this is the master database.
> But I would dig into sys.server_principals and sys.database_principals
> to see what anomalies I could find.
>
> --
> 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
With your help, I figured it out. What happened was this. I did a
sp_grantdbaccess [valen\nslocaldbusers] without ever doing the CREATE LO
GIN.
The sp_grantdbaccess seems to automatically add the group to db principals,
but not to server principals. Once I did the CREATE LOGIN, the GRANT ALTER
ANY LOGIN worked for that account.
Thank you for pointing me in the right direction.
Joe
help me see the light? :-)
Im logged in as a local machine admin.
C:\> SQLCMD -E
1> grant create database to [valen\nsLocalDBUsers]
2> go
1> grant alter any login to [valen\nsLocalDBUsers]
2> go
Msg 15151, Level 16, State 1, Server VALEN, Line 1
Cannot find the login 'valen\nsLocalDBUsers', because it does not exist or
you d
o not have permission.
1>
Thanks,
JoeHello Joe,
I understand that you log into SQL Server 2005 as a local admin but you
could not grant "alter any login" permission to a domain login. If I'm
off-base, please let me know.
I suspect "buitin\Administrators" group might have been removed from the
server or it has been removed "sysadmin" server role.
You may want to login as a domin user or sql login with sysadmin role,
grant yourself "sysadmin" role, and test it again
EXEC sp_helpsrvrolemember 'sysadmin'
EXEC sp_addsrvrolemember 'domain\username', 'sysadmin'
If you have any update, please feel free to let me know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Peter -
Well, sort of :-). I am allowed to do a "GRANT CREATE DATABASE TO", but
not allowed to do a "GRANT ALTER ANY LOGIN TO".
I have checked, and the builtin\administrator group still exists in SQL
server, and is a member of the sysadmin server role.
Regards,
Joe
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:6h$5$PNfHHA.4692@.TK2MSFTNGHUB02.phx.gbl...
> Hello Joe,
> I understand that you log into SQL Server 2005 as a local admin but you
> could not grant "alter any login" permission to a domain login. If I'm
> off-base, please let me know.
> I suspect "buitin\Administrators" group might have been removed from the
> server or it has been removed "sysadmin" server role.
> You may want to login as a domin user or sql login with sysadmin role,
> grant yourself "sysadmin" role, and test it again
> EXEC sp_helpsrvrolemember 'sysadmin'
> EXEC sp_addsrvrolemember 'domain\username', 'sysadmin'
> If you have any update, please feel free to let me know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Joe (jwdaigle@.nospam.nospam) writes:
> OK, I obviously dont understand something really simple here. Could
> someone help me see the light? :-)
> Im logged in as a local machine admin.
>
> C:\> SQLCMD -E
> 1> grant create database to [valen\nsLocalDBUsers]
> 2> go
> 1> grant alter any login to [valen\nsLocalDBUsers]
> 2> go
> Msg 15151, Level 16, State 1, Server VALEN, Line 1
> Cannot find the login 'valen\nsLocalDBUsers', because it does not exist or
> you d
> o not have permission.
> 1>
CREATE DATABASE is a database permission, ALTER ANY LOGIN is a server-
level permission. Database permissions are granted to database principals,
server permissions to logins.
It's certainly interesting that [valen\nsLocalDBUsers] is a database
user, but not a login, particularly if this is the master database.
But I would dig into sys.server_principals and sys.database_principals
to see what anomalies I could find.
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9911AB07D649Yazorman@.127.0.0.1...
> Joe (jwdaigle@.nospam.nospam) writes:
> CREATE DATABASE is a database permission, ALTER ANY LOGIN is a server-
> level permission. Database permissions are granted to database principals,
> server permissions to logins.
> It's certainly interesting that [valen\nsLocalDBUsers] is a database
> user, but not a login, particularly if this is the master database.
> But I would dig into sys.server_principals and sys.database_principals
> to see what anomalies I could find.
>
> --
> 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
Ah, I think I might see the problem now based on your response.
valen\nsLocalDBUsers is a group, not a user. It is a Windows group that I
added to the database in question using "sp_grantdbaccess". My guess is
that I did not first add it to the server because sp_grantdbaccess seems to
do the grantlogin at the server level "automatically".
Am I correct that I should have added the group to the server first, and
then granted access to the database? What is the recommended method for
adding a windows user/group to the server?
Thanks for any additional help, I am off to read the online help to
understand this better :-)
joe|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9911AB07D649Yazorman@.127.0.0.1...
> Joe (jwdaigle@.nospam.nospam) writes:
> CREATE DATABASE is a database permission, ALTER ANY LOGIN is a server-
> level permission. Database permissions are granted to database principals,
> server permissions to logins.
> It's certainly interesting that [valen\nsLocalDBUsers] is a database
> user, but not a login, particularly if this is the master database.
> But I would dig into sys.server_principals and sys.database_principals
> to see what anomalies I could find.
>
> --
> 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
With your help, I figured it out. What happened was this. I did a
sp_grantdbaccess [valen\nslocaldbusers] without ever doing the CREATE LO
GIN.
The sp_grantdbaccess seems to automatically add the group to db principals,
but not to server principals. Once I did the CREATE LOGIN, the GRANT ALTER
ANY LOGIN worked for that account.
Thank you for pointing me in the right direction.
Joe
grant alter trace to a domain account
surprisingly, couldn't find anything about granting alter trace permissions to a domain account.
I try to run following sql:
GRANT ALTER TRACE TO MyDomain\User1;
GO
And get the following error when executing:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
What is correct SQL to grant alter trace permissions to a domain account?
TIA.
You must enclose the name between brackets:
GRANT ALTER TRACE TO [MyDomain\User1];
Thanks
Laurentiu
grant alter trace to a domain account
surprisingly, couldn't find anything about granting alter trace permissions to a domain account.
I try to run following sql:
GRANT ALTERTRACE TO MyDomain\User1;
GO
And get the following error when executing:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
What is correct SQL to grant alter trace permissions to a domain account?
TIA.
You must enclose the name between brackets:
GRANT ALTER TRACE TO [MyDomain\User1];
Thanks
Laurentiu
GRANT ALTER TRACE in MSSQL 2000
Hi,
How do I acomplish the following in MSSQL Server 2000?
GRANT ALTER TRACE TO <user>;
GRANT VIEW ANY DEFINITION TO <user>;
Thanks
KrassHi Krass
The first is not possible, the second is not necessary.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
wrote in message news:E3282092-9BAA-4025-8A8B-1086356E3464@.microsoft.com...
> Hi,
> How do I acomplish the following in MSSQL Server 2000?
> GRANT ALTER TRACE TO <user>;
> GRANT VIEW ANY DEFINITION TO <user>;
> Thanks
> Krass|||Thanks Kalen,
So, I guess user needs to have sysadmin role. Is that correct?
Krass
"Kalen Delaney" wrote:
> Hi Krass
> The first is not possible, the second is not necessary.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
> wrote in message news:E3282092-9BAA-4025-8A8B-1086356E3464@.microsoft.com..
.
>
>|||Krass,
On SQL Server 2000, only sysadmins can run SQL Traces.
RLF
"kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
wrote in message news:6EB6CD69-8E98-4A64-96CB-DDD4316C6036@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen,
> So, I guess user needs to have sysadmin role. Is that correct?
> Krass
> "Kalen Delaney" wrote:
>
How do I acomplish the following in MSSQL Server 2000?
GRANT ALTER TRACE TO <user>;
GRANT VIEW ANY DEFINITION TO <user>;
Thanks
KrassHi Krass
The first is not possible, the second is not necessary.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
wrote in message news:E3282092-9BAA-4025-8A8B-1086356E3464@.microsoft.com...
> Hi,
> How do I acomplish the following in MSSQL Server 2000?
> GRANT ALTER TRACE TO <user>;
> GRANT VIEW ANY DEFINITION TO <user>;
> Thanks
> Krass|||Thanks Kalen,
So, I guess user needs to have sysadmin role. Is that correct?
Krass
"Kalen Delaney" wrote:
> Hi Krass
> The first is not possible, the second is not necessary.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
> wrote in message news:E3282092-9BAA-4025-8A8B-1086356E3464@.microsoft.com..
.
>
>|||Krass,
On SQL Server 2000, only sysadmins can run SQL Traces.
RLF
"kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
wrote in message news:6EB6CD69-8E98-4A64-96CB-DDD4316C6036@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen,
> So, I guess user needs to have sysadmin role. Is that correct?
> Krass
> "Kalen Delaney" wrote:
>
Grant alter procedure kind of thing
Hey guys. I need to let a developer alter procedures but not create any new
procedures. Is there a way i can do it?
I don't want to
grant create procedure to accountName
Instead I want to
grant alter procedure to accountName
Please let me know if it's Possible. Thank You.Tejas Parikh,
You can check BOL and see the permissions for both statements. The "alter
procedure" permission is not transferable and just members of sy
min,
db_owner, db_ddladmin and the sp owner have permission to alter the sp.
AMB
"Tejas Parikh" wrote:
> Hey guys. I need to let a developer alter procedures but not create any n
ew
> procedures. Is there a way i can do it?
> I don't want to
> grant create procedure to accountName
> Instead I want to
> grant alter procedure to accountName
> Please let me know if it's Possible. Thank You.
>|||>> I need to let a developer alter procedures but not create any new
procedures.<<
What the heck'!!! Let's give all the teenagers car keys and whiskey.|||LOL... are you saying the programmers are bad?
Grant
Who gives a {censored} if I am wrong.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>
More like giving them car keys, whiskey and condoms. They may be driving
drunk, but at least . . ..
Truly that's a bad idea. If your developers are qualified to write stored
procedures, then you should let them decide how to structure the code and
add procedures as necessary.
David|||Thanks Alejandro and David for your reply. It gives me the answer. All, I wa
s
trying to say is I dont want them to add any more sp's, just alter them if
needed. But well, u have a point, David.
Thank you for all your help.|||This really does show just out of touch you are with out SQL Server is used
within industry.
Do you think every shop has a DBA writing database designs and stored
procedures?
Seriously, stop what you are doing and go and get a job as a junior
programmer and get some very needed industrial experience, it looks like you
are too class room bound and have little if any (probably the latter)
exposure to business.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>|||No, but like teenagers, HIGHLY SUSPECT! I have been both in my
lifetime, so I now these things :)|||>> More like giving them car keys, whiskey and condoms. They may be driving
drunk, but at least . . ..<<
As the adoptive father of "troubled teenagers" and the legal
grandfather of two bastards, they forget the condom when they are in a
hurry. I am not going to put a :) on that one. Much like constraints
on a database, I should have given them birth control shots ...|||>> Do you think every shop has a DBA writing database designs and stored pro
cedures? <<
NO! I assume that bad programmers, like you, are writing schemas and
stored procedures. A large part of my consulting is based on cleaning
up the mess.
I would hope that a GOOD shop has code reviews and teaches the novice
programmers how to write SQL.
My publishers would not like that :)
My first full-time paid programing job was in 1965; I was a GS-1 at the
Pittman-Dunn Research Labs in Philadelphia. When did you start on your
full-time paid programing job? You never worked your way from "code
monkey" to "guru", did you?
I will not be in a classroom again until April. I have a two w
gig
for a Seimens company in South America. Do you ever leave the UK?
Leave your own company? Your own department within the company? Your
own team within that department?
procedures. Is there a way i can do it?
I don't want to
grant create procedure to accountName
Instead I want to
grant alter procedure to accountName
Please let me know if it's Possible. Thank You.Tejas Parikh,
You can check BOL and see the permissions for both statements. The "alter
procedure" permission is not transferable and just members of sy
min,db_owner, db_ddladmin and the sp owner have permission to alter the sp.
AMB
"Tejas Parikh" wrote:
> Hey guys. I need to let a developer alter procedures but not create any n
ew
> procedures. Is there a way i can do it?
> I don't want to
> grant create procedure to accountName
> Instead I want to
> grant alter procedure to accountName
> Please let me know if it's Possible. Thank You.
>|||>> I need to let a developer alter procedures but not create any new
procedures.<<
What the heck'!!! Let's give all the teenagers car keys and whiskey.|||LOL... are you saying the programmers are bad?
Grant
Who gives a {censored} if I am wrong.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>
More like giving them car keys, whiskey and condoms. They may be driving
drunk, but at least . . ..
Truly that's a bad idea. If your developers are qualified to write stored
procedures, then you should let them decide how to structure the code and
add procedures as necessary.
David|||Thanks Alejandro and David for your reply. It gives me the answer. All, I wa
s
trying to say is I dont want them to add any more sp's, just alter them if
needed. But well, u have a point, David.
Thank you for all your help.|||This really does show just out of touch you are with out SQL Server is used
within industry.
Do you think every shop has a DBA writing database designs and stored
procedures?
Seriously, stop what you are doing and go and get a job as a junior
programmer and get some very needed industrial experience, it looks like you
are too class room bound and have little if any (probably the latter)
exposure to business.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>|||No, but like teenagers, HIGHLY SUSPECT! I have been both in my
lifetime, so I now these things :)|||>> More like giving them car keys, whiskey and condoms. They may be driving
drunk, but at least . . ..<<
As the adoptive father of "troubled teenagers" and the legal
grandfather of two bastards, they forget the condom when they are in a
hurry. I am not going to put a :) on that one. Much like constraints
on a database, I should have given them birth control shots ...|||>> Do you think every shop has a DBA writing database designs and stored pro
cedures? <<
NO! I assume that bad programmers, like you, are writing schemas and
stored procedures. A large part of my consulting is based on cleaning
up the mess.
I would hope that a GOOD shop has code reviews and teaches the novice
programmers how to write SQL.
My publishers would not like that :)
My first full-time paid programing job was in 1965; I was a GS-1 at the
Pittman-Dunn Research Labs in Philadelphia. When did you start on your
full-time paid programing job? You never worked your way from "code
monkey" to "guru", did you?
I will not be in a classroom again until April. I have a two w
gigfor a Seimens company in South America. Do you ever leave the UK?
Leave your own company? Your own department within the company? Your
own team within that department?
grant (Alter table) permission on a single table in the database
Product: Microsoft SQL server Enterprise Edition (2000)
O/S: MS Windoes NT (5.2)
Version: 8.00.760
===================================================== Can we grant (Alter table) permission on a single table on a database?
( I need to create a new user in a database of about 100 tables. This User
needs modify(Change structure of table) permission on a single table. User
want to incorporate triggers into the table. Can someone suggest me the best
way to grant permission for this.
Thanks
--
ontario, canada
=====================================================db
If you make him to be owner of the table. Such as CREATE TABLE user.T(c
INT...)"db" <db@.discussions.microsoft.com> wrote in message
news:0C3AA563-11A3-42FA-989D-6330159005A3@.microsoft.com...
> Product: Microsoft SQL server Enterprise Edition (2000)
> O/S: MS Windoes NT (5.2)
> Version: 8.00.760
> =====================================================> Can we grant (Alter table) permission on a single table on a database?
> ( I need to create a new user in a database of about 100 tables. This User
> needs modify(Change structure of table) permission on a single table. User
> want to incorporate triggers into the table. Can someone suggest me the
> best
> way to grant permission for this.
> Thanks
> --
> ontario, canada
> =====================================================
O/S: MS Windoes NT (5.2)
Version: 8.00.760
===================================================== Can we grant (Alter table) permission on a single table on a database?
( I need to create a new user in a database of about 100 tables. This User
needs modify(Change structure of table) permission on a single table. User
want to incorporate triggers into the table. Can someone suggest me the best
way to grant permission for this.
Thanks
--
ontario, canada
=====================================================db
If you make him to be owner of the table. Such as CREATE TABLE user.T(c
INT...)"db" <db@.discussions.microsoft.com> wrote in message
news:0C3AA563-11A3-42FA-989D-6330159005A3@.microsoft.com...
> Product: Microsoft SQL server Enterprise Edition (2000)
> O/S: MS Windoes NT (5.2)
> Version: 8.00.760
> =====================================================> Can we grant (Alter table) permission on a single table on a database?
> ( I need to create a new user in a database of about 100 tables. This User
> needs modify(Change structure of table) permission on a single table. User
> want to incorporate triggers into the table. Can someone suggest me the
> best
> way to grant permission for this.
> Thanks
> --
> ontario, canada
> =====================================================
Monday, March 19, 2012
Got it!
Thanks Tibor...
ALTER procedure admin_ConvertUnix2Dos
as
declare @.dir varchar(256)
declare @.FileName varchar(256)
declare @.Convert varchar(512)
declare @.Exec varchar(512)
create table #tmp
(FileName varchar(256))
set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /B
'
insert into #tmp exec master..xp_cmdshell @.dir
declare MyCur cursor for
select FileName from #tmp
open MyCur
fetch next from MyCur into @.FileName
while @.@.fetch_status = 0
begin
set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B
',@.FileName+'"')
set @.Convert = replace(@.Convert,'dir','')
exec master..xp_cmdshell @.Convert
fetch next from MyCur into @.FileName
end
close MyCur
deallocate MyCur
"ChrisR" <noemail@.bla.com> wrote in message
news:e7b%23CC3oFHA.2080@.TK2MSFTNGP14.phx.gbl...
> I've been messing with the quotes and am getting nowhere quickly. Any
> ideas?
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eYhBM12oFHA.3828@.TK2MSFTNGP12.phx.gbl...
>You removed the double quotes around the path of the EXE file? I think the p
roblem is that when you
have two sets of double quotes (as in the first version), you need to enclos
e the hole shebang in
double quotes:
""c:\Unix2Dos\Unix2Dos.exe" "C:\Documents and Settings\chris.rose\My
Documents\FTP\CABHLDRLSACTNCONSTANTS.TAB;1""
Not needed now as you don't have spaced etx in path to Unix2Dos.exe, but mig
ht be worth knowing for
next time...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <noemail@.bla.com> wrote in message news:uAmBsL3oFHA.3996@.TK2MSFTNGP12.phx.gbl...[c
olor=darkred]
> Thanks Tibor...
>
> ALTER procedure admin_ConvertUnix2Dos
> as
> declare @.dir varchar(256)
> declare @.FileName varchar(256)
> declare @.Convert varchar(512)
> declare @.Exec varchar(512)
> create table #tmp
> (FileName varchar(256))
> set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /
B '
> insert into #tmp exec master..xp_cmdshell @.dir
> declare MyCur cursor for
> select FileName from #tmp
> open MyCur
> fetch next from MyCur into @.FileName
> while @.@.fetch_status = 0
> begin
> set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B ',@.FileName+
'"')
> set @.Convert = replace(@.Convert,'dir','')
> exec master..xp_cmdshell @.Convert
> fetch next from MyCur into @.FileName
> end
> close MyCur
> deallocate MyCur
>
> "ChrisR" <noemail@.bla.com> wrote in message news:e7b%23CC3oFHA.2080@.TK2MSF
TNGP14.phx.gbl...
>[/color]
ALTER procedure admin_ConvertUnix2Dos
as
declare @.dir varchar(256)
declare @.FileName varchar(256)
declare @.Convert varchar(512)
declare @.Exec varchar(512)
create table #tmp
(FileName varchar(256))
set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /B
'
insert into #tmp exec master..xp_cmdshell @.dir
declare MyCur cursor for
select FileName from #tmp
open MyCur
fetch next from MyCur into @.FileName
while @.@.fetch_status = 0
begin
set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B
',@.FileName+'"')
set @.Convert = replace(@.Convert,'dir','')
exec master..xp_cmdshell @.Convert
fetch next from MyCur into @.FileName
end
close MyCur
deallocate MyCur
"ChrisR" <noemail@.bla.com> wrote in message
news:e7b%23CC3oFHA.2080@.TK2MSFTNGP14.phx.gbl...
> I've been messing with the quotes and am getting nowhere quickly. Any
> ideas?
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eYhBM12oFHA.3828@.TK2MSFTNGP12.phx.gbl...
>You removed the double quotes around the path of the EXE file? I think the p
roblem is that when you
have two sets of double quotes (as in the first version), you need to enclos
e the hole shebang in
double quotes:
""c:\Unix2Dos\Unix2Dos.exe" "C:\Documents and Settings\chris.rose\My
Documents\FTP\CABHLDRLSACTNCONSTANTS.TAB;1""
Not needed now as you don't have spaced etx in path to Unix2Dos.exe, but mig
ht be worth knowing for
next time...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <noemail@.bla.com> wrote in message news:uAmBsL3oFHA.3996@.TK2MSFTNGP12.phx.gbl...[c
olor=darkred]
> Thanks Tibor...
>
> ALTER procedure admin_ConvertUnix2Dos
> as
> declare @.dir varchar(256)
> declare @.FileName varchar(256)
> declare @.Convert varchar(512)
> declare @.Exec varchar(512)
> create table #tmp
> (FileName varchar(256))
> set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /
B '
> insert into #tmp exec master..xp_cmdshell @.dir
> declare MyCur cursor for
> select FileName from #tmp
> open MyCur
> fetch next from MyCur into @.FileName
> while @.@.fetch_status = 0
> begin
> set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B ',@.FileName+
'"')
> set @.Convert = replace(@.Convert,'dir','')
> exec master..xp_cmdshell @.Convert
> fetch next from MyCur into @.FileName
> end
> close MyCur
> deallocate MyCur
>
> "ChrisR" <noemail@.bla.com> wrote in message news:e7b%23CC3oFHA.2080@.TK2MSF
TNGP14.phx.gbl...
>[/color]
Subscribe to:
Posts (Atom)