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

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

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

Grabbing characters from a string

Hello

I want to write a stored procedure (using Enterprise Manager) that can grab
the digits that are inbetween the two dashes (-) in strings like:
123-150-40
1-123-8
32-4215-61

The digits to the left, right and inbetween the dashes could be any length,
so a static "get the 5th, 6th and 7th digit" stored procedure won't work.

Many thanks,

--
Chris Michael
www.INTOmobiles.com
Download 100s of ringtones, wallpapers & logos every month for only 1.50
per weekChris Michael (news@.intomobiles.com) writes:
> I want to write a stored procedure (using Enterprise Manager) that can
> grab the digits that are inbetween the two dashes (-) in strings like:
> 123-150-40
> 1-123-8
> 32-4215-61
> The digits to the left, right and inbetween the dashes could be any
> length, so a static "get the 5th, 6th and 7th digit" stored procedure
> won't work.

And you want the result to be? Do you want:

12315040
11238
32421561

That is, one single number formed? That would be easy with help of
the replace() function.

Or do you want triplets like:

123, 150, 40
1, 123, 8
32, 4215, 61

And in such case, is there always exactly two dashes, or can you have

123-3455-2345-23345-2349-2-23

If you always have two dashes, using a combination of substring(),
patindex(), reverse(), right() and left() might do the trick.

All functions I have mentioned here, are listed in Books Online under
Functions, String Functions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Got an expensive server, SQL is very slow on writes

Hi,

i am experiencing SQl write performance problems on a very shiny server. Got data files on a Raid 1+0, log files on a separate drive, all SCSI, Win2003 server, 6G RAM, 2 Xeon processors. I've created a small benchmarking program and run it on my desktop pc and this 'big' server. Here are the results:

Desktop: SQL server inserts: 78 Seconds, Direct writes to the harddisk(Just write a string to the file 10000 times): 13 seconds

SQLServer: SQL server inserts: 422 Seconds, Direct writes to the harddisk: 16 seconds

So, for some reason, my 'shiny' machine is 6 times slower on writes than my desktop. When i tried comparing the select performance, my shiny server is 10 times faster than my desktop.

Initially i had Raid5 on my server and it had poorer direct write performance but now, direct writes seem to be ok, so, i recon this is a problem related to SQL server.

What can i do to improve the insert performance?

Thanks in advanceYour performance should double by placing the database file and transaction log file on different drives.

HTH|||As i mentioned previously, i already have my data and log files in different physical drives but it it does not make a difference.
Playing around with Raid configurations i managed to significally improve the speed of direct writes to hardisk (not within SQL) but sql write speed still stayed the same. I have a feeling that this is either Win2003 server or sql server matter. Cannot find any info about it.

Had someone come accross the same problem?|||Maybe check if your RAID-controller has its writecache disabled. How did you test? Within Queryanalyzer or custom Tool.?|||I wrote a small c# utility to do the test. Tool does 2 types of test:

1. It runs 10,000 insert quieries against a simple table

2. It writes a string into the flat file using direct file access 10,000 times.

I thought that writecache might be an issue but my direct writes (test 2) perform very well (equal to my desktop pc, whereas test 1 is 6 times slower than on my desktop) so i think that bottleneck is not in disk/raid configuration but somewhere else.

Not sure where though:( Same thing happens with SQL7.

Currently trying to install Win2k server op.|||For anyone having the same kind of a problem, issue was resolved by by installing a battery backed write cache on the SCSI controller. Achieved 24 times speed boost at once.|||... though enabling write cache is not a good idea anyways since it can lead to data corruption, even during normal operation.|||I've paid £250 for this write cache chip, it's backup onboard battery will last for 72 hours. I am pretty confident that my cached data will be save. Besides that, apparently write caching with battery backup is microsoft's recommendation. (Q230785)|||It's not a matter of how long your battery lasts. I read that the datapages can get out of sync with write caching enabled which leads to corrupt data files. But not 100% sure could be an issue of SQL 7 only.

... maybe one of our friendly MVP-SQL-Gurus here can confirm or disproof that hardware write caching can be a problem? :)

- Moon

Good idea or not to enable write caching ..

Is it a good idea or not to enable write caching on the array controller ?
All our disks are direct attached storage.
Thanks
Only if it is battery backed should you enable it. But if it is then
enabling it can provide a dramatic boost for writes especially for
checkpoints. You may even want to change the default read to write cache
ratio to give it more write cache.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23Dw36D7VGHA.1160@.TK2MSFTNGP09.phx.gbl...
> Is it a good idea or not to enable write caching on the array controller ?
> All our disks are direct attached storage.
> Thanks
>

Good idea or not to enable write caching ..

Is it a good idea or not to enable write caching on the array controller ?
All our disks are direct attached storage.
ThanksOnly if it is battery backed should you enable it. But if it is then
enabling it can provide a dramatic boost for writes especially for
checkpoints. You may even want to change the default read to write cache
ratio to give it more write cache.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23Dw36D7VGHA.1160@.TK2MSFTNGP09.phx.gbl...
> Is it a good idea or not to enable write caching on the array controller ?
> All our disks are direct attached storage.
> Thanks
>

Good idea or not to enable write caching ..

Is it a good idea or not to enable write caching on the array controller ?
All our disks are direct attached storage.
ThanksOnly if it is battery backed should you enable it. But if it is then
enabling it can provide a dramatic boost for writes especially for
checkpoints. You may even want to change the default read to write cache
ratio to give it more write cache.
--
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23Dw36D7VGHA.1160@.TK2MSFTNGP09.phx.gbl...
> Is it a good idea or not to enable write caching on the array controller ?
> All our disks are direct attached storage.
> Thanks
>

Good book for learning the Stored Proc language

Though I have been using SQL Server for some years, it is just recently that I have need to learn how to write SP's. I would like a recommendation for a book that is rich with examples (my best learning style).

Many thanks...

Todd

I highly recommend two Microsoft Press books with Itzak Ben-Gan as the primary author.

Inside T-SQL Programming

Inside T-SQL Querying

Also with Microsoft Press, Burst/Forte's title: Programming SQL Server 2005

Going nuts here!

Alright- I plugged this code in to tell my form to write to SQLServer when I click the Submit button. However I error out when I load the form:

dim Conn as new OleDbConnection("DSN=MyDB")

Sub On_Click(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "'," & _
"'" & params(1) & "'," & _
"'" & params(2) & "'")

ExecuteStatement(strSQL)
end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand(strSQL, Conn)

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as Exception
end try
objCmd.Connection.Close()
end function

I get this error:
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30471: Expression is not an array or a method, and cannot have an argument list.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30205: End of statement expected.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(15) : error BC30035: Syntax error.

"'" & params(0) & "'," & _

Any ideas??try this :

strSQL="INSERT INTO tblSCRequest (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

better yet, use parameterized queries. (1) you dont have to worry about these quotes (2) you are safe from sql injection attacks.

hth|||So what if for simplicity sake I try this-

strSQL=""INSERT INTO tblSCRequest (strRequestor) VALUES (" & _
";" & params (0) & "')"

Would this work? Also, I saw your link to the param site. If I get this working I'm going to use that going forward. Thanks.|||no a ; indicates end of sql statement. use parameterized queries. thats the best way to go about it.

hth|||Okay. I have this problem now-

Sub Submit(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO Comm (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

ExecuteStatement(strSQL)

end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand()

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as exception
end try
objCmd.Connection.Close()
end function

I get an error saying "System.NullReferenceException: Object reference not set to an instance of an object." And it is pointing to line 31 as the culprit-

Line 29: catch ex as exception
Line 30: end try
Line 31: objCmd.Connection.Close()<---Error line
Line 32: end function|||(1) use parameterized queries. ( i suggested this earlier too)
(2) make sure you have values in all the parameters.
(3) put the connection.close in the finally block

hth

Global expression apply to all fields in report

Hi All,

I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.

Any helps are appreciated.

Cheers

Nick

Moved to SSRS forum....(from SSIS)|||

Hi Nick,

You can declare a public variable in the Custom code section of the SSRS reports. The syntax for this variable declaration should be VB. Once declared, it can be used in any part of the report by referring to the field as Code."Variable Name". eg. Code.IntCounter.

Regards,

Pradeep

|||

Yeah, i did this way, and it works.. Thanks mate

But i have a little question, it always hightlight the global variable name and indicate it is not declared object in expression area. even through it still works.

Cheers

Nick

Global expression apply to all fields in report

Hi All,

I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.

Any helps are appreciated.

Cheers

Nick

Moved to SSRS forum....(from SSIS)|||

Hi Nick,

You can declare a public variable in the Custom code section of the SSRS reports. The syntax for this variable declaration should be VB. Once declared, it can be used in any part of the report by referring to the field as Code."Variable Name". eg. Code.IntCounter.

Regards,

Pradeep

|||

Yeah, i did this way, and it works.. Thanks mate

But i have a little question, it always hightlight the global variable name and indicate it is not declared object in expression area. even through it still works.

Cheers

Nick

global code

Hi ,
I wrote a function in the report property code :
Public Function AA(field as string)
AA = field
End Function
When I try to write an expretion like :
=AA(Me.Value)
I get and error of AA is not recognize.
What is the problem ?
10x.You need to call your function as =Code.AA(Me.Value) . This will
solve your problem mentioned error.
I am doubtfull about what you exactly mean by Me.Vlaue
Hope this helps.
Thanks,
Mahesh