Granting right to all the user sp's

Hello,
I would like to ease granting the right to execute all user sp's by normal u
sers.
By default, AFIK you have to click the EXECUTE option of each and every sp.
I head about a procedure, which could automate this.
Anyone knows this kind of procedure or how to to this?
Thank You
JoachimHi,
use pubs
go
select 'grant execute on ' +name +' to user_name' from sysobjects where
type='p'
Replace the database name and user_name based on your requirement.
After excuting the script you will get a bunch of grant statement in your
result pane, just cut and paste the entire contents and execute it again.
Thanks
Hari
MCDBA
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim|||You can use a procedure like this
use master
go
create procedure sp_grantexec(@.user sysname,@.debug int = 0)
as
set nocount on
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME(@.user)
-- check user exists
if not exists(select * from sysusers where name = @.user)
begin
raiserror('User %s is not a valid user in this database',16,1,@.user)
return -1
end
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') =
0'
if @.debug = 1 print @.sql
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
If @.ret <> 0
begin
raiserror('Error executing command %s',16,1,@.sql)
return -2
end
go
Then you can run it like below to grant user foo exec permissions on all
user stored procedures in the pubs database
use pubs
go
exec sp_grantexec 'foo'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim

granting public permissions to another role

We need to revoke all insert/update/delete access from public. So this won't
affect users I wanted to create a new role and grant it all of these excess
permissions from public, then revoke the permissions from public. It looks
like there are thousands of grants that need to be revoked - can anyone thin
k
of a way to script this?
Thank you in advance.One method is to generate the script using Transact-SQL. You can tweak the
example below to generate the desired script. This example script ignores
system objects and doesn't handle column permissions.
SET NOCOUNT ON
SELECT
CASE [p].[protecttype]
WHEN 204 THEN 'GRANT '
WHEN 205 THEN 'GRANT '
WHEN 206 THEN 'DENY '
END +
CASE [p].[action]
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 224 THEN 'EXECUTE'
WHEN 26 THEN 'REFERENCES'
END + ' ON ' +
QUOTENAME(USER_NAME([o].[uid])) + '.' +
QUOTENAME([o].[name]) + ' TO ' +
QUOTENAME([u].[name]) +
CASE WHEN [p].[protecttype] = 204 THEN ' WITH GRANT OPTION' ELSE '' END
FROM
[sysobjects] AS [o]
JOIN
[sysprotects] AS [p] ON
[p].[id] = [o].[id]
JOIN
[sysusers] AS [u] ON
[p].[uid] = [u].[uid]
WHERE
OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
[u].[name] = 'public'
Hope this helps.
Dan Guzman
SQL Server MVP
"Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
news:745289F7-F2CF-4079-8A35-66974CFA73F3@.microsoft.com...
> We need to revoke all insert/update/delete access from public. So this
> won't
> affect users I wanted to create a new role and grant it all of these
> excess
> permissions from public, then revoke the permissions from public. It looks
> like there are thousands of grants that need to be revoked - can anyone
> think
> of a way to script this?
> Thank you in advance.|||Thanks for the help - and when it comes to revoking the permissions from
"public" I could use a similar script using "revoke" instead of "grant"?
"Dan Guzman" wrote:

> One method is to generate the script using Transact-SQL. You can tweak th
e
> example below to generate the desired script. This example script ignores
> system objects and doesn't handle column permissions.
> SET NOCOUNT ON
> SELECT
> CASE [p].[protecttype]
> WHEN 204 THEN 'GRANT '
> WHEN 205 THEN 'GRANT '
> WHEN 206 THEN 'DENY '
> END +
> CASE [p].[action]
> WHEN 193 THEN 'SELECT'
> WHEN 195 THEN 'INSERT'
> WHEN 196 THEN 'DELETE'
> WHEN 197 THEN 'UPDATE'
> WHEN 224 THEN 'EXECUTE'
> WHEN 26 THEN 'REFERENCES'
> END + ' ON ' +
> QUOTENAME(USER_NAME([o].[uid])) + '.' +
> QUOTENAME([o].[name]) + ' TO ' +
> QUOTENAME([u].[name]) +
> CASE WHEN [p].[protecttype] = 204 THEN ' WITH GRANT OPTION' ELSE '' END
> FROM
> [sysobjects] AS [o]
> JOIN
> [sysprotects] AS [p] ON
> [p].[id] = [o].[id]
> JOIN
> [sysusers] AS [u] ON
> [p].[uid] = [u].[uid]
> WHERE
> OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
> [u].[name] = 'public'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
> news:745289F7-F2CF-4079-8A35-66974CFA73F3@.microsoft.com...
>
>|||Yes, Bobsie, the script I posted was developed to script existing
permissions. You'll need to modify it so that modified permission scripts
are generated instead.
Run the script once to extract the public permissions with your new role
hard-coded as the grantee instead of public'. The generated script will
looks something like:
GRANT SELECT ON MyTable TO MyNewRole
GRANT EXECUTE ON MyProc TO MyNewRole
Then run the script again with a hard-coded REVOKE instead of the GRANT/DENY
CASE statement so the second script generated will be like:
REVOKE SELECT ON MyTable TO public
REVOKE EXECUTE ON MyProc TO public
Be sure to review the generated scripts before running in your environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
news:3949EB00-7D35-4EC1-9920-249ABF69BBEC@.microsoft.com...
> Thanks for the help - and when it comes to revoking the permissions from
> "public" I could use a similar script using "revoke" instead of "grant"?
>
> "Dan Guzman" wrote:
>sql

Granting Permissions using SQL 2005 Schema...

All,

I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.

I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].

I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.

Anyone have any ideas?

Thanks,

Justin

They would need alter schema to drop tables in the schema.

GRANT ALTER ON SCHEMA::[Sandbox] TO [Sandbox Users]

-Sue

|||

CONTROL should cover ALTER and DELETE. My guess is that the users from that group have been denied some permission that is affecting their DELET statements.

You can make use of fn_my_permissions and has_perm_by_name to find out the actual permissions on the object, for example:

-- Connected as/impersonating a member of Sandbox users

--

SELECT * FROM fn_my_permissions( 'Sandbox', 'schema' )

go

SELECT has_perms_by_name( 'Sandbox.SampleTable', 'object', 'DELETE' )

go

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for catching that Raul...not even sure what I was thinking last night. Or not thinking at that moment.

-Sue

|||

Raul,

Thanks... that will help. I will be working with the user this morning to see if I can figure out why he is having this problem.

It looks like the permissions are fine. I was helping him out this morning and I think he has an issue with the package he was trying to run, bu the fn_my_permissions helped out tremendously.

Thanks!

Justin

Granting Permissions using SQL 2005 Schema...

All,

I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.

I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].

I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.

Anyone have any ideas?

Thanks,

Justin

They would need alter schema to drop tables in the schema.

GRANT ALTER ON SCHEMA::[Sandbox] TO [Sandbox Users]

-Sue

|||

CONTROL should cover ALTER and DELETE. My guess is that the users from that group have been denied some permission that is affecting their DELET statements.

You can make use of fn_my_permissions and has_perm_by_name to find out the actual permissions on the object, for example:

-- Connected as/impersonating a member of Sandbox users

--

SELECT * FROM fn_my_permissions( 'Sandbox', 'schema' )

go

SELECT has_perms_by_name( 'Sandbox.SampleTable', 'object', 'DELETE' )

go

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for catching that Raul...not even sure what I was thinking last night. Or not thinking at that moment.

-Sue

|||

Raul,

Thanks... that will help. I will be working with the user this morning to see if I can figure out why he is having this problem.

It looks like the permissions are fine. I was helping him out this morning and I think he has an issue with the package he was trying to run, bu the fn_my_permissions helped out tremendously.

Thanks!

Justin

Granting GRANT permissions

I have the need to allow users GRANT permissions for their created stored
procedures. However I do not wish to give these users db_securityadmin right
s
in the database they will be creating said stored procedures in.
Is there a way to only give them GRANT EXEC rights and nothing else? I
really don't like they idea they can modify groups and the users in those
groups with db_securityadmin rights as well as modify access rights to table
s.
Thanks
JoshCreators of stored procedures (standard users with CREATE PROCEDURE rights)
can grant permissions on their own procedures to other users. Is this what
you mean? i.e. if user A has CREATE PROCEDURE rights they can create a
procedure (A.P1) and then grant permissions on it to another user B (grant
exec on A.P1 to B). This is without them being in any other role than public
in the database.
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
"Josh N." <Josh N.@.discussions.microsoft.com> wrote in message
news:6BA4EBA0-9BC5-4A84-BBA8-A18502BFB93E@.microsoft.com...
>I have the need to allow users GRANT permissions for their created stored
> procedures. However I do not wish to give these users db_securityadmin
> rights
> in the database they will be creating said stored procedures in.
> Is there a way to only give them GRANT EXEC rights and nothing else? I
> really don't like they idea they can modify groups and the users in those
> groups with db_securityadmin rights as well as modify access rights to
> tables.
> Thanks
> Josh
>|||The owner of a stored procedure automatically has the ability to grant
others the right to execute it. They do not need to be in any special role.
HTH
Kalen Delaney
www.solidqualitylearning.com
"Josh N." <Josh N.@.discussions.microsoft.com> wrote in message
news:6BA4EBA0-9BC5-4A84-BBA8-A18502BFB93E@.microsoft.com...
>I have the need to allow users GRANT permissions for their created stored
> procedures. However I do not wish to give these users db_securityadmin
> rights
> in the database they will be creating said stored procedures in.
> Is there a way to only give them GRANT EXEC rights and nothing else? I
> really don't like they idea they can modify groups and the users in those
> groups with db_securityadmin rights as well as modify access rights to
> tables.
> Thanks
> Josh
>|||Yes this is what I was refering to. Thank you for your answer but I now
realize I have a much larger problem.
How do I allow a user to create a stored procedure for 'dbo' without giving
them owner rights? I tried to "grant create procedure to xxx as dbo" but
that errors out saying you can't use AS when granting those rights.
If anyone knows of a way to allow a user to create procedures and edit them
for dbo without being dbo I would appreciate your response.
Thanks
Josh
"Jasper Smith" wrote:

> Creators of stored procedures (standard users with CREATE PROCEDURE rights
)
> can grant permissions on their own procedures to other users. Is this what
> you mean? i.e. if user A has CREATE PROCEDURE rights they can create a
> procedure (A.P1) and then grant permissions on it to another user B (grant
> exec on A.P1 to B). This is without them being in any other role than publ
ic
> in the database.
> --
> 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
> "Josh N." <Josh N.@.discussions.microsoft.com> wrote in message
> news:6BA4EBA0-9BC5-4A84-BBA8-A18502BFB93E@.microsoft.com...
>
>|||Can you elaborate on exactly what you want to do? You can create a table
owned by dbo if you are in the db_owner role. In that case your user name is
not DBO, but you can act as the owner of the object.
There is no way to create a proc owned by dbo without being dbo or being in
the db_owner role.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Josh N." <JoshN@.discussions.microsoft.com> wrote in message
news:FCB0EC5B-B880-4B97-A82C-7EA14DF54096@.microsoft.com...
> Yes this is what I was refering to. Thank you for your answer but I now
> realize I have a much larger problem.
> How do I allow a user to create a stored procedure for 'dbo' without
> giving
> them owner rights? I tried to "grant create procedure to xxx as dbo" but
> that errors out saying you can't use AS when granting those rights.
> If anyone knows of a way to allow a user to create procedures and edit
> them
> for dbo without being dbo I would appreciate your response.
> Thanks
> Josh
>
>
> "Jasper Smith" wrote:
>
>|||I'm not really following what you mean either. I'm guessing
that you want a user to be able to create a stored procedure
that is owned by dbo without the user being a member of
db_owners. You can add the user to the db_ddladmin role and
they can create stored procedures owned by dbo. When they
create the stored procedures, they need to qualify them as
being owned by dbo...for example
create procedure dbo.SomeStoredProcedure <etc>
Members of db_ddladmin can also edit the stored procedures.
However, they inherit a lot of other permissions in the
process as they can add, modify, drop database objects, not
just stored procedures.
More info on exactly what you want to do would be good as it
is not necessarily a good thing to give these rights to
users.
-Sue
On Mon, 12 Sep 2005 13:45:02 -0700, "Josh N."
<JoshN@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yes this is what I was refering to. Thank you for your answer but I now
>realize I have a much larger problem.
>How do I allow a user to create a stored procedure for 'dbo' without giving
>them owner rights? I tried to "grant create procedure to xxx as dbo" but
>that errors out saying you can't use AS when granting those rights.
>If anyone knows of a way to allow a user to create procedures and edit them
>for dbo without being dbo I would appreciate your response.
>Thanks
>Josh
>
>
>"Jasper Smith" wrote:
>|||Yes I want users who are not in db_owner or db_ddladmin to be able to create
procedures for dbo. But it appears my initial assumption about this is true,
which is unfortunate.
I appereciate everyone's responses and thank you. Unless anyone knows of a
way to allow users to do this without giving them db_ddladmin or db_owner, I
apparently am forced to leave a database wide open to people I don't trust
(this wasn't my decision...)
Josh
"Sue Hoegemeier" wrote:

> I'm not really following what you mean either. I'm guessing
> that you want a user to be able to create a stored procedure
> that is owned by dbo without the user being a member of
> db_owners. You can add the user to the db_ddladmin role and
> they can create stored procedures owned by dbo. When they
> create the stored procedures, they need to qualify them as
> being owned by dbo...for example
> create procedure dbo.SomeStoredProcedure <etc>
> Members of db_ddladmin can also edit the stored procedures.
> However, they inherit a lot of other permissions in the
> process as they can add, modify, drop database objects, not
> just stored procedures.
> More info on exactly what you want to do would be good as it
> is not necessarily a good thing to give these rights to
> users.
> -Sue
> On Mon, 12 Sep 2005 13:45:02 -0700, "Josh N."
> <JoshN@.discussions.microsoft.com> wrote:
>
>

Granting a user permissions to create and Drop a table

How do I allow a user (or group of users) permission to create/drop a table?

I have found the 'GRANT CREATE TABLE TO username' command, which will (I assume) allow a user to create a table, but how to I allow a user to 'DROP' the created table as well?
'GRANT DROP TABLE TO username' doesn't work?
and I want the users to be able to DROP/DELETE this table (temporary table created just for printing purposes) as well.

thanksWhat I want to know as well is, if the table is DROPPED, do I have to re-set all permissions on it again after it is re-created?
If so, what is the best way to achieve all this/

Thanks|||

See permission section of the DROP TABLE article in BOL. Permissions would have to be reassigned - they are dropped when the table is dropped. You should script all your database operations and manage script files with a source code control product.

Thanks
Laurentiu

grant the privilege of create user to other users

Hi, in sql server 2000 for create a login I use the command sp_addlogin
but if i connect to sql server with a user that not is 'sa' or trusted
connection and execute addlogin the server shows that i don't have
permisions.
how can i do for a user diferent to sa have the privilege to create
users?
thanks.Hi,
make your login a member of the fixed server role "securityadmin"
In Enterprise Manager double click a login (in the Security folder of a
server) and click the "Server Roles" tab to assign fixed server roles.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1141835445.230660.269690@.i40g2000cwc.googlegroups.com...
> Hi, in sql server 2000 for create a login I use the command sp_addlogin
> but if i connect to sql server with a user that not is 'sa' or trusted
> connection and execute addlogin the server shows that i don't have
> permisions.
> how can i do for a user diferent to sa have the privilege to create
> users?
> thanks.
>

grant the privilege of create user to other users

Hi, in sql server 2000 for create a login I use the command sp_addlogin
but if i connect to sql server with a user that not is 'sa' or trusted
connection and execute addlogin the server shows that i don't have
permisions.
how can i do for a user diferent to sa have the privilege to create
users?
thanks.
Hi,
make your login a member of the fixed server role "securityadmin"
In Enterprise Manager double click a login (in the Security folder of a
server) and click the "Server Roles" tab to assign fixed server roles.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1141835445.230660.269690@.i40g2000cwc.googlegr oups.com...
> Hi, in sql server 2000 for create a login I use the command sp_addlogin
> but if i connect to sql server with a user that not is 'sa' or trusted
> connection and execute addlogin the server shows that i don't have
> permisions.
> how can i do for a user diferent to sa have the privilege to create
> users?
> thanks.
>

grant the privilege of create user to other users

Hi, in sql server 2000 for create a login I use the command sp_addlogin
but if i connect to sql server with a user that not is 'sa' or trusted
connection and execute addlogin the server shows that i don't have
permisions.
how can i do for a user diferent to sa have the privilege to create
users?
thanks.Hi,
make your login a member of the fixed server role "securityadmin"
In Enterprise Manager double click a login (in the Security folder of a
server) and click the "Server Roles" tab to assign fixed server roles.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1141835445.230660.269690@.i40g2000cwc.googlegroups.com...
> Hi, in sql server 2000 for create a login I use the command sp_addlogin
> but if i connect to sql server with a user that not is 'sa' or trusted
> connection and execute addlogin the server shows that i don't have
> permisions.
> how can i do for a user diferent to sa have the privilege to create
> users?
> thanks.
>

Grant permissions

Hi all,
Can I grant select only permission on all objects in the database? I have users that I need to give view access only on stored procedures, triggers, and functions. Thanks.db_datareader (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_6ndx.asp).

-PatPsql

GRANT Permission to Users

I need to grant EXEC permission to several users on my Procedures. I would
like to to do this in One GRANT statement. this doesn't run. What do I need
to change.
GRANT EXEC ON
--Proc_Clear_data_CAS_ODS
--Proc_Clear_data_CMS_ODS
--Proc_Clear_data_GAS_ODS
--Proc_load_data_ShipperDim
--Proc_load_data_capacityGrpDim
--Proc_load_data_CAS_To_ODS
--Proc_load_data_CMS_To_ODS
--Proc_load_data_ContractDim
--Proc_load_data_CMS_To_ODS
--Proc_load_data_GAS_To_ODS
--Proc_load_data_PayerDim
--Proc_load_data_pointGrpDim
--Proc_load_data_ShipperDim
TO USER 1
USER 2
USER 3
WITH GRANT OPTION
Hi,
You can not give multiple procedure names in a Grant statement. But the
otherway is possible,
Exexute previlage on a single procedure to multiple users.
Grant exec on proc1 to usr1,ur2,usr3
Thanks
Hari
MCDBA
"baaul" wrote:

> I need to grant EXEC permission to several users on my Procedures. I would
> like to to do this in One GRANT statement. this doesn't run. What do I need
> to change.
> GRANT EXEC ON
> --Proc_Clear_data_CAS_ODS
> --Proc_Clear_data_CMS_ODS
> --Proc_Clear_data_GAS_ODS
> --Proc_load_data_ShipperDim
> --Proc_load_data_capacityGrpDim
> --Proc_load_data_CAS_To_ODS
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_ContractDim
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_GAS_To_ODS
> --Proc_load_data_PayerDim
> --Proc_load_data_pointGrpDim
> --Proc_load_data_ShipperDim
> TO USER 1
> USER 2
> USER 3
> WITH GRANT OPTION

GRANT Permission to Users

I need to grant EXEC permission to several users on my Procedures. I would
like to to do this in One GRANT statement. this doesn't run. What do I need
to change.
GRANT EXEC ON
--Proc_Clear_data_CAS_ODS
--Proc_Clear_data_CMS_ODS
--Proc_Clear_data_GAS_ODS
--Proc_load_data_ShipperDim
--Proc_load_data_capacityGrpDim
--Proc_load_data_CAS_To_ODS
--Proc_load_data_CMS_To_ODS
--Proc_load_data_ContractDim
--Proc_load_data_CMS_To_ODS
--Proc_load_data_GAS_To_ODS
--Proc_load_data_PayerDim
--Proc_load_data_pointGrpDim
--Proc_load_data_ShipperDim
TO USER 1
USER 2
USER 3
WITH GRANT OPTIONHi,
You can not give multiple procedure names in a Grant statement. But the
otherway is possible,
Exexute previlage on a single procedure to multiple users.
Grant exec on proc1 to usr1,ur2,usr3
Thanks
Hari
MCDBA
"baaul" wrote:
> I need to grant EXEC permission to several users on my Procedures. I would
> like to to do this in One GRANT statement. this doesn't run. What do I need
> to change.
> GRANT EXEC ON
> --Proc_Clear_data_CAS_ODS
> --Proc_Clear_data_CMS_ODS
> --Proc_Clear_data_GAS_ODS
> --Proc_load_data_ShipperDim
> --Proc_load_data_capacityGrpDim
> --Proc_load_data_CAS_To_ODS
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_ContractDim
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_GAS_To_ODS
> --Proc_load_data_PayerDim
> --Proc_load_data_pointGrpDim
> --Proc_load_data_ShipperDim
> TO USER 1
> USER 2
> USER 3
> WITH GRANT OPTION

Grant permission (Sch-M lock)

When I tried to grant a Select permission on a table to a
user, it was blocked by any users who were currently
reading that table.
The grant command in QA issued lock Sch-M (Schema
modification) lock. Why is it issued Sch-M lock ?
Is it mean I cannot grant permission only any table while
users access it ?Hi Johnny,
Changes to schema information can not occur while the object is in use. I
suggest you continue to try of schedule it over night.
As an alternative to using SQL users ID you could use Windows groups to
secure your tables or use Database roles and add your users to the role
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:058701c3b569$b81a9810$a301280a@.phx.gbl...
> When I tried to grant a Select permission on a table to a
> user, it was blocked by any users who were currently
> reading that table.
> The grant command in QA issued lock Sch-M (Schema
> modification) lock. Why is it issued Sch-M lock ?
> Is it mean I cannot grant permission only any table while
> users access it ?
>|||1) Why does grant permission on table require Sch-M lock ?
I am sure it is regular task to grant table permission to
users. I can't just wait for scheduling overnight.
2) I also tried to create a database role. But when I
grant Select permission to that role, it was blocked.
Running SQL 7 & SP3 on Windows 2000.|||Johnny,
1) You have a good point but the fact remain it does and your can do it
until you can lock the table. Think of it this way, if you were changing
the permissions to remove users (which may be accessing the Table) with
permissions. Then you can understand you would need to lock the schema.
The process doesn't know you are adding until after.
2) Yes the time you grant permissions to the role again you need a lock but
after what you do is add users to roles this doesn't require a lock at all.
So you can do this while the table is in use.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:049101c3b56e$5e304e80$a401280a@.phx.gbl...
> 1) Why does grant permission on table require Sch-M lock ?
> I am sure it is regular task to grant table permission to
> users. I can't just wait for scheduling overnight.
> 2) I also tried to create a database role. But when I
> grant Select permission to that role, it was blocked.
> Running SQL 7 & SP3 on Windows 2000.

grant or deny access to db

I want to avoid users to connect to my dB via odbc
if the user do not use my front application.
If the user try to connect to the db via Acces, Excel or
any other, I want the db to refuse the acces.
How can I do so?
Using application roles would be one option. You can find more info on
application roles in books online.
-Sue
On Fri, 1 Oct 2004 09:32:46 -0700, "fredy"
<anonymous@.discussions.microsoft.com> wrote:

>I want to avoid users to connect to my dB via odbc
>if the user do not use my front application.
>If the user try to connect to the db via Acces, Excel or
>any other, I want the db to refuse the acces.
>How can I do so?

Grant insert, but only allow entering vals in some fields

I have a table with 3 fields:
val1
val2
val3
Is there a way using a role to allow users to create a new record in
the table, but only allowing them to populate the val1 field during the
insert? They should not be allowed to put data in fields val2 and val3
during the insert and they should only be allowed to modify the val1
field.
Thanks!
Chris(cbtechlists@.gmail.com) writes:
> I have a table with 3 fields:
> val1
> val2
> val3
> Is there a way using a role to allow users to create a new record in
> the table, but only allowing them to populate the val1 field during the
> insert? They should not be allowed to put data in fields val2 and val3
> during the insert and they should only be allowed to modify the val1
> field.
You could a create view that exposes the permitted column and let the
users insert into that view rather than directly to the table. Or you
could expose all columns in the table, and have an INSTEAD OF trigger
ignores the non-permitted columns.
... or you could use stored procedures.
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

Grant execute on XP_CMDSHELL indirectly

I need to grant execute permissions to some users on xp_cmdshell. I am
trying to avoid having to grant execute permission directly on
xp_cmdhell, but alternatively grant access indirectly through another
procedure. The idea will be to write another proc "Custom_cmdshell"
which will call xp_cmdshell.
I am familiar with ownership chains but haven't figured out a way to
work with it in this situation.
Regards,
KenOn Feb 26, 5:37=A0am, Ken <raid...@.yahoo.com> wrote:
> I need to grant execute permissions to some users on xp_cmdshell. I am
> trying to avoid having to grant execute permission directly on
> xp_cmdhell, but alternatively grant access indirectly through =A0another
> procedure. The idea will be to write another proc "Custom_cmdshell"
> which will call xp_cmdshell.
> I am familiar with ownership chains but haven't figured out a way to
> work with it in this situation.
> Regards,
> Ken
Please refere to below article .
http://support.microsoft.com/kb/890775/en-us
Thanks
Ajay Rengunthwar
MCTS|||I think that this article might contain valuable information and possibly options for you:
http://www.sommarskog.se/grantperm.html
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ken" <raidken@.yahoo.com> wrote in message
news:afad664c-55a0-4ffd-b1f1-0037373d258e@.q33g2000hsh.googlegroups.com...
>I need to grant execute permissions to some users on xp_cmdshell. I am
> trying to avoid having to grant execute permission directly on
> xp_cmdhell, but alternatively grant access indirectly through another
> procedure. The idea will be to write another proc "Custom_cmdshell"
> which will call xp_cmdshell.
> I am familiar with ownership chains but haven't figured out a way to
> work with it in this situation.
> Regards,
> Ken|||I should have mentioned that we are using SQL 2000. The article
mentioned applies to SQL 2005 only.
Ken
On Feb 26, 3:47=A0am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that this article might contain valuable information and possibly =options for you:http://www.sommarskog.se/grantperm.html
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
>|||Check out the section "Cross-Database Access", which is applicable to 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ken" <raidken@.yahoo.com> wrote in message
news:0d244ac3-57ee-430e-a612-1d7dcc4d9d47@.e60g2000hsh.googlegroups.com...
I should have mentioned that we are using SQL 2000. The article
mentioned applies to SQL 2005 only.
Ken
On Feb 26, 3:47 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that this article might contain valuable information and possibly options for
> you:http://www.sommarskog.se/grantperm.html
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>sql

Grant execute on XP_CMDSHELL indirectly

I need to grant execute permissions to some users on xp_cmdshell. I am
trying to avoid having to grant execute permission directly on
xp_cmdhell, but alternatively grant access indirectly through another
procedure. The idea will be to write another proc "Custom_cmdshell"
which will call xp_cmdshell.
I am familiar with ownership chains but haven't figured out a way to
work with it in this situation.
Regards,
Ken
On Feb 26, 5:37Xam, Ken <raid...@.yahoo.com> wrote:
> I need to grant execute permissions to some users on xp_cmdshell. I am
> trying to avoid having to grant execute permission directly on
> xp_cmdhell, but alternatively grant access indirectly through Xanother
> procedure. The idea will be to write another proc "Custom_cmdshell"
> which will call xp_cmdshell.
> I am familiar with ownership chains but haven't figured out a way to
> work with it in this situation.
> Regards,
> Ken
Please refere to below article .
http://support.microsoft.com/kb/890775/en-us
Thanks
Ajay Rengunthwar
MCTS
|||I should have mentioned that we are using SQL 2000. The article
mentioned applies to SQL 2005 only.
Ken
On Feb 26, 3:47Xam, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that this article might contain valuable information and possibly options for you:http://www.sommarskog.se/grantperm.html
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>

grant DELETE Global Temp Table permission to users

I suppose I have to do it in the tempdb database. I create a new user in thi
s database and add him to the db_owner role. Everything seems to work fine.
After restart SQL Server the users are dropded in the tempdb database.
Which is the correct way to grant these rights to a user?
Thanks."Edi Fellmann" <anonymous@.discussions.microsoft.com> wrote in message
news:E0499352-2B5D-46C4-A38E-34FC85B5BEC9@.microsoft.com...
> I suppose I have to do it in the tempdb database. I create a new user in
this database and add him to the db_owner role. Everything seems to work
fine.
> After restart SQL Server the users are dropded in the tempdb database.
> Which is the correct way to grant these rights to a user?
Tempdb is designed to be used by SQL Server as temporary storage and work
space. Any contents of tempdb are reset when SQL Server is restarted.
Consider creating a "temp" database for this purpose.
Steve|||I use the global temp table in a sp with dynamic SQL. It is not posible to u
se temp tables in this sp.
Is there any other solution?
Thankssql

Grand Total of an iif expression

I have a report grouped on the user. The report calculates various
utilization totals based on the username. Some users have a Utilization = 100% for only 12 or 17 hours and other have a Utilization = 100% for 40
hours. At the group level, I can calculate the utilization with the
following formula...
=iif ( Fields!zUserID.Value ="USER1 ",
(Sum( Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
Fields!zQtyBilled.Value , "GroupUserName")) / 12 ,
iif ( Fields!zUserID.Value ="USER2 ", (Sum(
Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
Fields!zQtyBilled.Value , "GroupUserName")) / 17 , (Sum(
Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
Fields!zQtyBilled.Value , "GroupUserName")) / 40 ))
However, my issue is how do I get a grand total of this utilization on the
report footer. I cannot sum () this formula in the report footer. I receive
an error becuase of the "GroupUserName" parameter.
Is there a way to use the sum () function in the Report Footer and just
reference the name of the textbox in the group footer and it's corresponding
value that has already been calculated for each group?I probably do not understand your question very well, but you might try to
simply do the sum without regard to the groupings ( simply take out the
groups) and do the sum for the entire data set...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Jack Bender" wrote:
> I have a report grouped on the user. The report calculates various
> utilization totals based on the username. Some users have a Utilization => 100% for only 12 or 17 hours and other have a Utilization = 100% for 40
> hours. At the group level, I can calculate the utilization with the
> following formula...
> =iif ( Fields!zUserID.Value ="USER1 ",
> (Sum( Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> Fields!zQtyBilled.Value , "GroupUserName")) / 12 ,
> iif ( Fields!zUserID.Value ="USER2 ", (Sum(
> Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> Fields!zQtyBilled.Value , "GroupUserName")) / 17 , (Sum(
> Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> Fields!zQtyBilled.Value , "GroupUserName")) / 40 ))
> However, my issue is how do I get a grand total of this utilization on the
> report footer. I cannot sum () this formula in the report footer. I receive
> an error becuase of the "GroupUserName" parameter.
> Is there a way to use the sum () function in the Report Footer and just
> reference the name of the textbox in the group footer and it's corresponding
> value that has already been calculated for each group?
>|||The issue here is that I need a conditional total and percentage calculation
based on the users that billed time in the report. If User 1 and/or User 2
are in the report, then they have different criterias for utilization
calcualtons than everyone else. All other users are based on 40 hours a
week. I determine this already at the group footer, so it would be much
easier just to grand total the cell value of the group footer then to
re-engineer the formula for the report footer.
"Wayne Snyder" wrote:
> I probably do not understand your question very well, but you might try to
> simply do the sum without regard to the groupings ( simply take out the
> groups) and do the sum for the entire data set...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Jack Bender" wrote:
> > I have a report grouped on the user. The report calculates various
> > utilization totals based on the username. Some users have a Utilization => > 100% for only 12 or 17 hours and other have a Utilization = 100% for 40
> > hours. At the group level, I can calculate the utilization with the
> > following formula...
> >
> > =iif ( Fields!zUserID.Value ="USER1 ",
> > (Sum( Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> > Fields!zQtyBilled.Value , "GroupUserName")) / 12 ,
> >
> > iif ( Fields!zUserID.Value ="USER2 ", (Sum(
> > Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> > Fields!zQtyBilled.Value , "GroupUserName")) / 17 , (Sum(
> > Fields!zBillingWorkQty.Value , "GroupUserName") + Sum(
> > Fields!zQtyBilled.Value , "GroupUserName")) / 40 ))
> >
> > However, my issue is how do I get a grand total of this utilization on the
> > report footer. I cannot sum () this formula in the report footer. I receive
> > an error becuase of the "GroupUserName" parameter.
> >
> > Is there a way to use the sum () function in the Report Footer and just
> > reference the name of the textbox in the group footer and it's corresponding
> > value that has already been calculated for each group?
> >
> >

Good Dashboard Tools

I have a multidimensional application and have built an interface that
exports the application data to a SQL Server 2005 data cube. My users are
clamoring for dashboards. Can anybody recommend good tools for building web
based dashboards that get their data from a SQL Server 2005 data cube?
TIA,
josh
p.s. Sorry for the cross-post. I'm a newbie to these news groups and
wasn't sure where to place this question.
Check out panorama software at:
http://www.panorama.com/
Its a powerfull enterprise web based Olap tool that include
a very good dashboard and KPI analytic platform.
See under products list:
http://www.panorama.com/products/index.html
At that site, you can follow some of thier customers stories as well.
Rea
"Josh Sale" wrote:

> I have a multidimensional application and have built an interface that
> exports the application data to a SQL Server 2005 data cube. My users are
> clamoring for dashboards. Can anybody recommend good tools for building web
> based dashboards that get their data from a SQL Server 2005 data cube?
> TIA,
> josh
> p.s. Sorry for the cross-post. I'm a newbie to these news groups and
> wasn't sure where to place this question.
>
>
|||Hi Rea,
I have a company and we created a new vision Report Generator (RepGen) for
simple reporting to Dashboard for stafs.
I can sent documents if you interest.
Thank you,
Veysel Ispir
Istanbul, Turkiye
"Rea" <Rea@.discussions.microsoft.com> wrote in message
news:9F8E3699-B68A-484C-940E-A4C1AE2DD27A@.microsoft.com...[vbcol=seagreen]
> Check out panorama software at:
> http://www.panorama.com/
> Its a powerfull enterprise web based Olap tool that include
> a very good dashboard and KPI analytic platform.
> See under products list:
> http://www.panorama.com/products/index.html
> At that site, you can follow some of thier customers stories as well.
> Rea
>
> "Josh Sale" wrote:
|||Hi Josh ,
I have a company and we created a new vision Report Generator (RepGen) for
simple reporting to Dashboard for stafs.
I can sent documents if you interest.
Thank you,
Veysel Ispir
Istanbul, Turkiye
"Josh Sale" <jsale@.tril dot cod> wrote in message
news:exaBYwKIHHA.3952@.TK2MSFTNGP02.phx.gbl...
>I have a multidimensional application and have built an interface that
>exports the application data to a SQL Server 2005 data cube. My users are
>clamoring for dashboards. Can anybody recommend good tools for building
>web based dashboards that get their data from a SQL Server 2005 data cube?
> TIA,
> josh
> p.s. Sorry for the cross-post. I'm a newbie to these news groups and
> wasn't sure where to place this question.
>