Can anyone give me some insight into how to grant CREATE
DATABASE rights to a user, but without him/her abled to
look at other objects.
For example, by granting this right, it seems this users
is abled to view all the logins etc within Enterprise
Managers (SQL7).
And wot would be the implications, since Access Xp
Projects required CREATE DATABASE rights in order to
create a new project? Is there another way?
Thks.
WayneDid you try
GRANT CREATE DATABASE TO loginname
Or giving the login the dbcreator role?
Note, however that this makes the login the owner of that database
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wayne" <Wayne.Chan@.Bradford.NHS.UK> wrote in message
news:02ba01c3a901$5cda2ed0$a501280a@.phx.gbl...
> Can anyone give me some insight into how to grant CREATE
> DATABASE rights to a user, but without him/her abled to
> look at other objects.
> For example, by granting this right, it seems this users
> is abled to view all the logins etc within Enterprise
> Managers (SQL7).
> And wot would be the implications, since Access Xp
> Projects required CREATE DATABASE rights in order to
> create a new project? Is there another way?
> Thks.
> Waynesql
Showing posts with label objects. Show all posts
Showing posts with label objects. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
grant to all objects
Is there an easy way to assign permissions to all objects for a user/group?
For instance, in the following BOL example, SELECT permissions are granted t
o
the public role:
GRANT SELECT
ON authors
TO public
GO
In the above example, the table object 'authors' is explicitly listed; is
there a way to use the same statement to grant the same select permission to
the public role for all objects? Or would I have to explicitly indicate each
object? In the latter case, I could use a cursor to dynamically create the
grant statements for each of my objects, however, I was hoping to find a
simpler way to have this done.
Thanks for all your responses in advance.Firstly, I think the BOL example is dumb - I wouldn't go assigning ANY
permissions to the public role in ANY database.
That said, you'd have to assign individual object permissions
individually, preferably to a role that you create but to a specific
user would work too (just much uglier and more admin overhead when users
come & go and when restoring DB backups to other servers). You can do
this in a small cursor loop which is very easy to do. Here's one I
whipped up in about 5 minutes when I read your post that will assign all
possible permissions for all user tables, views, procs & UDFs in the
current database to a specified user or role (untested):
declare @.cmd nvarchar(1000)
declare @.objname sysname
declare @.owner sysname
declare @.objtype char(2)
declare objs cursor for
select [name], user_name(uid) as owner, type from dbo.sysobjects
where type in ('U', 'P', 'V', 'FN')
order by type, [name]
for read only
open objs
fetch next from objs into @.objname, @.owner, @.objtype
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'grant ' +
case (@.objtype)
when ('U') then ('select, insert, update, delete,
references')
when ('V') then ('select, insert, update, delete,
references')
when ('P') then ('execute')
when ('FN') then ('execute')
end + ' on [' + @.owner + '].[' + @.objname + '] to <my
user/role>'
exec (@.cmd)
end
fetch next from objs into @.objname, @.owner, @.objtype
end
close objs
deallocate objs
How easy is that?
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Rob wrote:
>Is there an easy way to assign permissions to all objects for a user/group?
>For instance, in the following BOL example, SELECT permissions are granted
to
>the public role:
>GRANT SELECT
>ON authors
>TO public
>GO
>In the above example, the table object 'authors' is explicitly listed; is
>there a way to use the same statement to grant the same select permission t
o
>the public role for all objects? Or would I have to explicitly indicate eac
h
>object? In the latter case, I could use a cursor to dynamically create the
>grant statements for each of my objects, however, I was hoping to find a
>simpler way to have this done.
>Thanks for all your responses in advance.
>sql
For instance, in the following BOL example, SELECT permissions are granted t
o
the public role:
GRANT SELECT
ON authors
TO public
GO
In the above example, the table object 'authors' is explicitly listed; is
there a way to use the same statement to grant the same select permission to
the public role for all objects? Or would I have to explicitly indicate each
object? In the latter case, I could use a cursor to dynamically create the
grant statements for each of my objects, however, I was hoping to find a
simpler way to have this done.
Thanks for all your responses in advance.Firstly, I think the BOL example is dumb - I wouldn't go assigning ANY
permissions to the public role in ANY database.
That said, you'd have to assign individual object permissions
individually, preferably to a role that you create but to a specific
user would work too (just much uglier and more admin overhead when users
come & go and when restoring DB backups to other servers). You can do
this in a small cursor loop which is very easy to do. Here's one I
whipped up in about 5 minutes when I read your post that will assign all
possible permissions for all user tables, views, procs & UDFs in the
current database to a specified user or role (untested):
declare @.cmd nvarchar(1000)
declare @.objname sysname
declare @.owner sysname
declare @.objtype char(2)
declare objs cursor for
select [name], user_name(uid) as owner, type from dbo.sysobjects
where type in ('U', 'P', 'V', 'FN')
order by type, [name]
for read only
open objs
fetch next from objs into @.objname, @.owner, @.objtype
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'grant ' +
case (@.objtype)
when ('U') then ('select, insert, update, delete,
references')
when ('V') then ('select, insert, update, delete,
references')
when ('P') then ('execute')
when ('FN') then ('execute')
end + ' on [' + @.owner + '].[' + @.objname + '] to <my
user/role>'
exec (@.cmd)
end
fetch next from objs into @.objname, @.owner, @.objtype
end
close objs
deallocate objs
How easy is that?
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Rob wrote:
>Is there an easy way to assign permissions to all objects for a user/group?
>For instance, in the following BOL example, SELECT permissions are granted
to
>the public role:
>GRANT SELECT
>ON authors
>TO public
>GO
>In the above example, the table object 'authors' is explicitly listed; is
>there a way to use the same statement to grant the same select permission t
o
>the public role for all objects? Or would I have to explicitly indicate eac
h
>object? In the latter case, I could use a cursor to dynamically create the
>grant statements for each of my objects, however, I was hoping to find a
>simpler way to have this done.
>Thanks for all your responses in advance.
>sql
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
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
Monday, March 26, 2012
GRANT PERMISSION TO ALL OBJECTS ON A DATABASE
How do I write T-SQL command to grant a permission to ALL objects in a
database.
Thanks.
Esmeralda
Esmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permiss?o de EXEC para o usuário especificado
CompatXvel com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
--permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda
|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda
database.
Thanks.
Esmeralda
Esmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permiss?o de EXEC para o usuário especificado
CompatXvel com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
--permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda
|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda
GRANT PERMISSION TO ALL OBJECTS ON A DATABASE
How do I write T-SQL command to grant a permission to ALL objects in a
database.
Thanks.
EsmeraldaEsmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProced
ure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permiss?o de EXEC para o usuário especificado
Compat_vel com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda
database.
Thanks.
EsmeraldaEsmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProced
ure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permiss?o de EXEC para o usuário especificado
Compat_vel com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda
GRANT PERMISSION TO ALL OBJECTS ON A DATABASE
How do I write T-SQL command to grant a permission to ALL objects in a
database.
Thanks.
EsmeraldaEsmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permissão de EXEC para o usuário especificado
CompatÃvel com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda
database.
Thanks.
EsmeraldaEsmeralda,
What the permission do you want ?
I send for you one sample for this case:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sp_GrantExec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GrantExec]
GO
CREATE PROCEDURE sp_GrantExec (@.Username VARCHAR(256))
/* Funcionalidade: Concede permissão de EXEC para o usuário especificado
CompatÃvel com: SQL Server 7 e 2000
Desenvolvido por: Rodrigo Fernandes
Data: 29/12/2004 */
AS
-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) db_owner
-- (2) db_ddladmin AND db_securityadmin
IF (IS_MEMBER('db_owner') = 0) AND
(IS_MEMBER('db_securityadmin') = 0 OR IS_MEMBER('db_ddladmin') = 0)
BEGIN
RAISERROR(15247,-1,-1)
RETURN(1)
END
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = @.Username)
BEGIN
PRINT 'THE USER DOES NOT EXIST IN DATABASE !'
RETURN(1)
END
ELSE
BEGIN
DECLARE @.Granth VARCHAR(8000)
DECLARE @.Objname SYSNAME
DECLARE Objname_csr CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype IN ('P', 'FN')
AND category = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
OPEN Objname_csr
FETCH NEXT FROM Objname_csr INTO @.Objname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Granth = 'GRANT EXEC ON ' + @.Objname + ' TO ' + @.Username
EXEC (@.Granth)
PRINT @.Granth
FETCH NEXT FROM Objname_csr INTO @.Objname
END
CLOSE Objname_csr
DEALLOCATE Objname_csr
RETURN(0)
END
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"LaEsmeralda" wrote:
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda|||Please specify version. If on 2005, you can do:
GRANT SELECT ON DATABASE::dbname TO username.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:A5ED3CAB-00E4-4F6C-9546-BB70B842384A@.microsoft.com...
> How do I write T-SQL command to grant a permission to ALL objects in a
> database.
> Thanks.
> Esmeralda
grant permission to all objects
hi,
how should i grant permission to all objects in a database for an
existing user.
R.Kalaivanan
Kalaivanan.It really depends on what the permissions are. Using the inbuild database
roles is my first stop, and if you are essentially wanting to grant all
permissions on existing objects, then the db_owner role would be useful. If
you wanted a subset of that, then the other roles might be useful (eg
db_datareader) along with schema permissions eg to execute all stored
procedures.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Adding to Paul's comments, if you DO NOT want the user to have permission to
change anything about the tables, views, stored procedures, etc., then
db_owner most likely isn't the best choice. You may need to create your own
database role, giving it the permissions that are required.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uunE5ez1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It really depends on what the permissions are. Using the inbuild database
> roles is my first stop, and if you are essentially wanting to grant all
> permissions on existing objects, then the db_owner role would be useful.
> If you wanted a subset of that, then the other roles might be useful (eg
> db_datareader) along with schema permissions eg to execute all stored
> procedures.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>sql
how should i grant permission to all objects in a database for an
existing user.
R.Kalaivanan
Kalaivanan.It really depends on what the permissions are. Using the inbuild database
roles is my first stop, and if you are essentially wanting to grant all
permissions on existing objects, then the db_owner role would be useful. If
you wanted a subset of that, then the other roles might be useful (eg
db_datareader) along with schema permissions eg to execute all stored
procedures.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Adding to Paul's comments, if you DO NOT want the user to have permission to
change anything about the tables, views, stored procedures, etc., then
db_owner most likely isn't the best choice. You may need to create your own
database role, giving it the permissions that are required.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uunE5ez1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It really depends on what the permissions are. Using the inbuild database
> roles is my first stop, and if you are essentially wanting to grant all
> permissions on existing objects, then the db_owner role would be useful.
> If you wanted a subset of that, then the other roles might be useful (eg
> db_datareader) along with schema permissions eg to execute all stored
> procedures.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>sql
grant permission to all objects
hi,
how should i grant permission to all objects in a database for an
existing user.
R.Kalaivanan
Kalaivanan.
It really depends on what the permissions are. Using the inbuild database
roles is my first stop, and if you are essentially wanting to grant all
permissions on existing objects, then the db_owner role would be useful. If
you wanted a subset of that, then the other roles might be useful (eg
db_datareader) along with schema permissions eg to execute all stored
procedures.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Adding to Paul's comments, if you DO NOT want the user to have permission to
change anything about the tables, views, stored procedures, etc., then
db_owner most likely isn't the best choice. You may need to create your own
database role, giving it the permissions that are required.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uunE5ez1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It really depends on what the permissions are. Using the inbuild database
> roles is my first stop, and if you are essentially wanting to grant all
> permissions on existing objects, then the db_owner role would be useful.
> If you wanted a subset of that, then the other roles might be useful (eg
> db_datareader) along with schema permissions eg to execute all stored
> procedures.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
how should i grant permission to all objects in a database for an
existing user.
R.Kalaivanan
Kalaivanan.
It really depends on what the permissions are. Using the inbuild database
roles is my first stop, and if you are essentially wanting to grant all
permissions on existing objects, then the db_owner role would be useful. If
you wanted a subset of that, then the other roles might be useful (eg
db_datareader) along with schema permissions eg to execute all stored
procedures.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Adding to Paul's comments, if you DO NOT want the user to have permission to
change anything about the tables, views, stored procedures, etc., then
db_owner most likely isn't the best choice. You may need to create your own
database role, giving it the permissions that are required.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uunE5ez1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It really depends on what the permissions are. Using the inbuild database
> roles is my first stop, and if you are essentially wanting to grant all
> permissions on existing objects, then the db_owner role would be useful.
> If you wanted a subset of that, then the other roles might be useful (eg
> db_datareader) along with schema permissions eg to execute all stored
> procedures.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
grant permission to all objects
hi,
how should i grant permission to all objects in a database for an
existing user.
R.Kalaivanan
Kalaivanan.It really depends on what the permissions are. Using the inbuild database
roles is my first stop, and if you are essentially wanting to grant all
permissions on existing objects, then the db_owner role would be useful. If
you wanted a subset of that, then the other roles might be useful (eg
db_datareader) along with schema permissions eg to execute all stored
procedures.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Adding to Paul's comments, if you DO NOT want the user to have permission to
change anything about the tables, views, stored procedures, etc., then
db_owner most likely isn't the best choice. You may need to create your own
database role, giving it the permissions that are required.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uunE5ez1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It really depends on what the permissions are. Using the inbuild database
> roles is my first stop, and if you are essentially wanting to grant all
> permissions on existing objects, then the db_owner role would be useful.
> If you wanted a subset of that, then the other roles might be useful (eg
> db_datareader) along with schema permissions eg to execute all stored
> procedures.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
how should i grant permission to all objects in a database for an
existing user.
R.Kalaivanan
Kalaivanan.It really depends on what the permissions are. Using the inbuild database
roles is my first stop, and if you are essentially wanting to grant all
permissions on existing objects, then the db_owner role would be useful. If
you wanted a subset of that, then the other roles might be useful (eg
db_datareader) along with schema permissions eg to execute all stored
procedures.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Adding to Paul's comments, if you DO NOT want the user to have permission to
change anything about the tables, views, stored procedures, etc., then
db_owner most likely isn't the best choice. You may need to create your own
database role, giving it the permissions that are required.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uunE5ez1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> It really depends on what the permissions are. Using the inbuild database
> roles is my first stop, and if you are essentially wanting to grant all
> permissions on existing objects, then the db_owner role would be useful.
> If you wanted a subset of that, then the other roles might be useful (eg
> db_datareader) along with schema permissions eg to execute all stored
> procedures.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
Monday, March 12, 2012
Good sql server programming tool
Hi,
Are there any commercial tools available that allow easy management of sql
server objects? Intellisense, object grouping, hiding system objects etc.?
Thanks
Nick
In article <cfdaep$jpb$1@.phys-news-1.nl.colt.net>,=20
Nick.DELETETHISStansbury@.Sage-Partners.com says...
> Hi,
> Are there any commercial tools available that allow easy management of =
sql
> server objects? Intellisense, object grouping, hiding system objects etc.=
?
>=20
> Thanks
>=20
> Nick
>=20
>=20
>=20
Nick,
We have an IDE designed specifically for the development of SQL code=20
objects that is currently in closed beta. Features include source=20
control, logical grouping, intellisense and much more. =20
We are still accepting select sites to participate in our beta testing,=20
and offer free licenses to participating sites that aggressively utilize=20
the product during the beta phase and provide us quality feedback. =A0If=20
you are interested, please send details regarding your testing=20
environment to info@.perfectionedge.com.
Best regards,
David Barber
Perfection Edge
http://www.perfectionedge.com
|||Nick Stansbury wrote:
> Hi,
> Are there any commercial tools available that allow easy management
> of sql server objects? Intellisense, object grouping, hiding system
> objects etc.?
> Thanks
> Nick
Try Speed IDE from Imceda over at http://www.imceda.com.
David G.
Are there any commercial tools available that allow easy management of sql
server objects? Intellisense, object grouping, hiding system objects etc.?
Thanks
Nick
In article <cfdaep$jpb$1@.phys-news-1.nl.colt.net>,=20
Nick.DELETETHISStansbury@.Sage-Partners.com says...
> Hi,
> Are there any commercial tools available that allow easy management of =
sql
> server objects? Intellisense, object grouping, hiding system objects etc.=
?
>=20
> Thanks
>=20
> Nick
>=20
>=20
>=20
Nick,
We have an IDE designed specifically for the development of SQL code=20
objects that is currently in closed beta. Features include source=20
control, logical grouping, intellisense and much more. =20
We are still accepting select sites to participate in our beta testing,=20
and offer free licenses to participating sites that aggressively utilize=20
the product during the beta phase and provide us quality feedback. =A0If=20
you are interested, please send details regarding your testing=20
environment to info@.perfectionedge.com.
Best regards,
David Barber
Perfection Edge
http://www.perfectionedge.com
|||Nick Stansbury wrote:
> Hi,
> Are there any commercial tools available that allow easy management
> of sql server objects? Intellisense, object grouping, hiding system
> objects etc.?
> Thanks
> Nick
Try Speed IDE from Imceda over at http://www.imceda.com.
David G.
Good practice when working with objects using SQLServer as a secure store.
I'm sure this has been asked plenty of times before, so I'm after a link to a good answer.
I have tens of thousands of milk crates, holding dozens of different types of milk in hundreds of locations. I am used to working with objects but not databases. For this situation however I want the security of SQLServer transactions to track, for example, when a robot moves a crate from one location to another.
I am thinking of using SQLServer as a store. On startup I want to get my ecosystem of objects out of the store. While I am running, I'll just use objects. When I change an object property I want it to securely persist. I don't want to snapshot the whole menagerie of object states, just update the values that changed. Which will sometimes include the addition or deletion of objects. How do I do this? Is there an example somewhere that does this (or approximately this)?
I use VB and have Visual Studio 2005. (Which, by the way, is stunning. I thought all that "you will use less time and code more and better" talk was just hype. But its for real. Amazing product.)
tia
John
I think what you're asking is more on the client (VB programming) side than strictly in the database layer. I'd recommend checking out some of the "best practices" books and sites - you can check this one out to start:
http://msdn2.microsoft.com/en-us/vbasic/ms789183.aspx
Sorry if that's too basic - you may have already seen that site.
Buck Woody
http://www.buckwoody.com
Sunday, February 26, 2012
Gnerating SQL scripts for database creation
In Enterprise Manager there exists the ability to have SQL scripts
generated for the objects of each database. I do not see the ability to
have an an SQL script generated for the database itself. Does such an
ability exist ? I am using SQL Server 7, so maybe this ability does not
exist in that version but does in a later version.
I believe that it was added in 2000, but make sure you study the option ins the "Generate script"
dialog to make sure (I don't have a 7.0 to test on). Also, you might find something useful here:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:edm%23EKWWFHA.3840@.tk2msftngp13.phx.gbl...
> In Enterprise Manager there exists the ability to have SQL scripts generated for the objects of
> each database. I do not see the ability to have an an SQL script generated for the database
> itself. Does such an ability exist ? I am using SQL Server 7, so maybe this ability does not exist
> in that version but does in a later version.
generated for the objects of each database. I do not see the ability to
have an an SQL script generated for the database itself. Does such an
ability exist ? I am using SQL Server 7, so maybe this ability does not
exist in that version but does in a later version.
I believe that it was added in 2000, but make sure you study the option ins the "Generate script"
dialog to make sure (I don't have a 7.0 to test on). Also, you might find something useful here:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:edm%23EKWWFHA.3840@.tk2msftngp13.phx.gbl...
> In Enterprise Manager there exists the ability to have SQL scripts generated for the objects of
> each database. I do not see the ability to have an an SQL script generated for the database
> itself. Does such an ability exist ? I am using SQL Server 7, so maybe this ability does not exist
> in that version but does in a later version.
Sunday, February 19, 2012
Global access for Everyone Group
I have been tasked with taking over a Crystal XI Enterprise installation that uses the Everyone group to grant view access to all objects/folders. Where do I turn this off globally? In other words, I want the Everyone group to have no access rights to anything - so I can use created groups to administer access rights for obejcts/folders. Thanks for any help in advance.I have found the correct place for this under Settings. Thanks for your patience.
GK-Permissions on all database objects
Hi,
I'm looking for a way to view / report all permissions on all database objec
ts ( logins / roles / users and all object permissions ( select, delete, upd
ate execute etc. ) ).
If you can help please do...
Gkramer
The NetherlandsHi,
Please refer books online for the system procedure,
sp_helprotect
Thanks
Hri
MCDBA
"Gkramer" <anonymous@.discussions.microsoft.com> wrote in message
news:8626D21C-7192-4313-8CC1-0EDE6CCC2B43@.microsoft.com...
> Hi,
> I'm looking for a way to view / report all permissions on all database
objects ( logins / roles / users and all object permissions ( select,
delete, update execute etc. ) ).
> If you can help please do...
> Gkramer
> The Netherlands
I'm looking for a way to view / report all permissions on all database objec
ts ( logins / roles / users and all object permissions ( select, delete, upd
ate execute etc. ) ).
If you can help please do...
Gkramer
The NetherlandsHi,
Please refer books online for the system procedure,
sp_helprotect
Thanks
Hri
MCDBA
"Gkramer" <anonymous@.discussions.microsoft.com> wrote in message
news:8626D21C-7192-4313-8CC1-0EDE6CCC2B43@.microsoft.com...
> Hi,
> I'm looking for a way to view / report all permissions on all database
objects ( logins / roles / users and all object permissions ( select,
delete, update execute etc. ) ).
> If you can help please do...
> Gkramer
> The Netherlands
GK - Persmission on each database ( objects )
Hi,
Second attempt to post this same message ( yesterdays post is not to be foud
in this forum ? ).
I like to retrieve ALL permissions on all abjects for each user ( each role,
each login ) in ALL of the databases within one server environment. I tried
the standard "sp_" procs but they are to limited for this.
I have to deal with approx 250 databases on approx 60 servers so I like to h
ave something which I can use on server level and not on table level ( like
the standard procs ).
As an Oracle DBA I'm not that familiar with scripting on SQL server 2000 so
l need your help on this one.
who can help me out?
Thanks in advance,
Regards, GKramer
The netherlands.Hi,
Have a look into syspermissions system table, which stores all the
previleges granted using GRANT statement.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:D6C88E0C-8C67-4DAE-AB3E-9CBCD061AA28@.microsoft.com...
> Hi,
> Second attempt to post this same message ( yesterdays post is not to be
foud in this forum ? ).
> I like to retrieve ALL permissions on all abjects for each user ( each
role, each login ) in ALL of the databases within one server environment. I
tried the standard "sp_" procs but they are to limited for this.
> I have to deal with approx 250 databases on approx 60 servers so I like to
have something which I can use on server level and not on table level ( like
the standard procs ).
> As an Oracle DBA I'm not that familiar with scripting on SQL server 2000
so l need your help on this one.
> who can help me out?
> Thanks in advance,
> Regards, GKramer
> The netherlands.
>|||Hi,
Have a look into syspermissions system table, which stores all the
previleges granted using GRANT statement.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:D6C88E0C-8C67-4DAE-AB3E-9CBCD061AA28@.microsoft.com...
> Hi,
> Second attempt to post this same message ( yesterdays post is not to be
foud in this forum ? ).
> I like to retrieve ALL permissions on all abjects for each user ( each
role, each login ) in ALL of the databases within one server environment. I
tried the standard "sp_" procs but they are to limited for this.
> I have to deal with approx 250 databases on approx 60 servers so I like to
have something which I can use on server level and not on table level ( like
the standard procs ).
> As an Oracle DBA I'm not that familiar with scripting on SQL server 2000
so l need your help on this one.
> who can help me out?
> Thanks in advance,
> Regards, GKramer
> The netherlands.
>|||Hari,
Thanks for your quick response, but where do the id's refer to ? ( Where c
an I find the ERD according to the sys-tables )
1 id int 4 0
0 grantee smallint 2 0
0 grantor smallint 2 0
0 actadd smallint 2 0
0 actmod smallint 2 0
Guus Kramer|||Hi,
Details will be there in spt_values table in Master database.
0 - means it is Public
For Grantor and Grantee execute along with user_name function.
user_name(grantee),user_name(grantor)
For actadd and actmode join it with spt_values table.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:96235761-C242-4D52-8FAE-22E3B6C8A424@.microsoft.com...
> Hari,
> Thanks for your quick response, but where do the id's refer to ? ( Where
can I find the ERD according to the sys-tables )
> 1 id int 4 0
> 0 grantee smallint 2 0
> 0 grantor smallint 2 0
> 0 actadd smallint 2 0
> 0 actmod smallint 2 0
> Guus Kramer|||Look at sp_helprotect and the PERMISSIONS function also.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Cindy,
As I told the forum the SP_ proc are way to limited. I need something wihich
generates an output like this;
database -- login -- (connected to ) role -- object(s) -- object(s) permissi
on
I'm not familiar with scripting MS sql ( I'm a former Oracle DBA and 3 month
s on the (SQLserver) job now ) and I can not find any documentation of how t
he systables are related ( ERD ).
Please help me on this because I have to examin 300 database on 60 server!!
Best regards,
Guus Kramer,
The Netherlands
Second attempt to post this same message ( yesterdays post is not to be foud
in this forum ? ).
I like to retrieve ALL permissions on all abjects for each user ( each role,
each login ) in ALL of the databases within one server environment. I tried
the standard "sp_" procs but they are to limited for this.
I have to deal with approx 250 databases on approx 60 servers so I like to h
ave something which I can use on server level and not on table level ( like
the standard procs ).
As an Oracle DBA I'm not that familiar with scripting on SQL server 2000 so
l need your help on this one.
who can help me out?
Thanks in advance,
Regards, GKramer
The netherlands.Hi,
Have a look into syspermissions system table, which stores all the
previleges granted using GRANT statement.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:D6C88E0C-8C67-4DAE-AB3E-9CBCD061AA28@.microsoft.com...
> Hi,
> Second attempt to post this same message ( yesterdays post is not to be
foud in this forum ? ).
> I like to retrieve ALL permissions on all abjects for each user ( each
role, each login ) in ALL of the databases within one server environment. I
tried the standard "sp_" procs but they are to limited for this.
> I have to deal with approx 250 databases on approx 60 servers so I like to
have something which I can use on server level and not on table level ( like
the standard procs ).
> As an Oracle DBA I'm not that familiar with scripting on SQL server 2000
so l need your help on this one.
> who can help me out?
> Thanks in advance,
> Regards, GKramer
> The netherlands.
>|||Hi,
Have a look into syspermissions system table, which stores all the
previleges granted using GRANT statement.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:D6C88E0C-8C67-4DAE-AB3E-9CBCD061AA28@.microsoft.com...
> Hi,
> Second attempt to post this same message ( yesterdays post is not to be
foud in this forum ? ).
> I like to retrieve ALL permissions on all abjects for each user ( each
role, each login ) in ALL of the databases within one server environment. I
tried the standard "sp_" procs but they are to limited for this.
> I have to deal with approx 250 databases on approx 60 servers so I like to
have something which I can use on server level and not on table level ( like
the standard procs ).
> As an Oracle DBA I'm not that familiar with scripting on SQL server 2000
so l need your help on this one.
> who can help me out?
> Thanks in advance,
> Regards, GKramer
> The netherlands.
>|||Hari,
Thanks for your quick response, but where do the id's refer to ? ( Where c
an I find the ERD according to the sys-tables )
1 id int 4 0
0 grantee smallint 2 0
0 grantor smallint 2 0
0 actadd smallint 2 0
0 actmod smallint 2 0
Guus Kramer|||Hi,
Details will be there in spt_values table in Master database.
0 - means it is Public
For Grantor and Grantee execute along with user_name function.
user_name(grantee),user_name(grantor)
For actadd and actmode join it with spt_values table.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:96235761-C242-4D52-8FAE-22E3B6C8A424@.microsoft.com...
> Hari,
> Thanks for your quick response, but where do the id's refer to ? ( Where
can I find the ERD according to the sys-tables )
> 1 id int 4 0
> 0 grantee smallint 2 0
> 0 grantor smallint 2 0
> 0 actadd smallint 2 0
> 0 actmod smallint 2 0
> Guus Kramer|||Look at sp_helprotect and the PERMISSIONS function also.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Cindy,
As I told the forum the SP_ proc are way to limited. I need something wihich
generates an output like this;
database -- login -- (connected to ) role -- object(s) -- object(s) permissi
on
I'm not familiar with scripting MS sql ( I'm a former Oracle DBA and 3 month
s on the (SQLserver) job now ) and I can not find any documentation of how t
he systables are related ( ERD ).
Please help me on this because I have to examin 300 database on 60 server!!
Best regards,
Guus Kramer,
The Netherlands
GK - Granting on all objects but not on JOBs ???
Hi,
I just wonder why I can grant / revoke on all objects ( tables, stored proce
dures etc ) and NOT on JOBs?
Thanx in advance,
GKramerHi,
You have to be member of SYSADMIN server role or member of DB_OWNER role
in MSDB database.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:92043A96-9386-486B-BC43-644C6EBB9CE6@.microsoft.com...
> Hi,
> I just wonder why I can grant / revoke on all objects ( tables, stored
procedures etc ) and NOT on JOBs?
> Thanx in advance,
> GKramer|||Jobs can only be viewed by a sysadmin or the job owner.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I just wonder why I can grant / revoke on all objects ( tables, stored proce
dures etc ) and NOT on JOBs?
Thanx in advance,
GKramerHi,
You have to be member of SYSADMIN server role or member of DB_OWNER role
in MSDB database.
Thanks
Hari
MCDBA
"GKramer" <anonymous@.discussions.microsoft.com> wrote in message
news:92043A96-9386-486B-BC43-644C6EBB9CE6@.microsoft.com...
> Hi,
> I just wonder why I can grant / revoke on all objects ( tables, stored
procedures etc ) and NOT on JOBs?
> Thanx in advance,
> GKramer|||Jobs can only be viewed by a sysadmin or the job owner.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Giving a user permissions on objects in a schema
Hi,
SQL Server Security is not my strong point so forgive me for asking stupid questions.
I have a bunch of tables and sprocs within a schema 'MySchema'. I have a user 'MyUser' defined in the database.
I would like to give MyUser permission to SELECT from tables and EXECUTE all sprocs in MySchema. What is the simplest way of doing that? Will the following:
GRANT EXECUTE ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION AS [db_owner]
GRANT SELECT ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION
accomplish that? (I can't test it out at the moment because our DBA isn't around and I don't have permission)
With best practices in mind - is what I am doing here considered "ok". Any suggestions/comments are welcome.
-Jamie
P.S. Can anyone recommend any documentation that talks about what best practices should be in the use of schemas. BOL is a bit sparse. Thanks.
Hi,
In General we do
1). Create a Role
2). Assign a proper permission/privilege using Grant as you describes
3). Create user/group
4). Map users/user group to earlier create Role
Best Practise describes
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
Here is a Check List for Server Security
http://www.sqlsecurity.com/FAQs/SQLSecurityChecklist/tabid/57/Default.aspx
"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans"
|||
Hemantgiri S. Goswami wrote:
Hi,
In General we do
...
Good stuff. Thank you very much.
|||
Hemantgiri,
I have one more question around this.
I have created a schema MySchema.|||A ROLE is a PRINCIPAL type which doesn't need any owner. A SECURABLE needs an owner
Subscribe to:
Posts (Atom)