Column Level Permissions For Server 7
Jun 24, 2000Is there a graphical tool to manage column-level permissions just as it was possible in Enterprise Manager for Server 6.X ?
View 1 Replies
ADVERTISEMENT
Grant Backup Permissions At Server Level ?
Sep 5, 2006Hi There
The backup database permission seems to only be at a database level , grant backup database and db_backupoperator role only exist at a database level.
However i want to permission to a user to backup any database, and he must not be a sysadmin. I cannot find a server level role that can backup databases.
How can i do this? There are alot of database so granting the permission for every database seems a waste of time, also one would have to remember every time a new database is created to grant this permission.
There must be a way to do this at a server level?
Thanx
Column Level Or Database Level Encryption/decryption....
Jan 16, 2008I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
Row Level Permissions
Sep 26, 2007is there a simple procedure to run to find what the row level permissions are on each user?
thanks.
SQL Server 2008 :: Script To List All Database Level Permissions In A Database?
Aug 4, 2015I found this script online..
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
Add Column Permissions To A New Column In SQL Server 2005
Jun 5, 2007Can someone explain to me how to go about adding column permissions to a new column in SQL server 2005? I added a new column to an existing table, to which I want to add security. Thanks in advance!
View 1 Replies View RelatedDoes Xp_cmdshell Proxy Account Need Admin-level Permissions?
Oct 4, 2007Re: SQL Server 2005
Does the xp_cmdshell proxy account need admin-level permissions on the server?
The reason I ask this is because I keep getting "Access is Denied" errors when trying to run this command as a non-admin:
master..xp_cmdshell dtexec 'some package'
The 'some package' has an "execute process task" which calls a batch file on the server.
If the proxy account is NOT a local admin, the "execute process tasks" fails with an "Access is Denied" error.
If the proxy account is a local admin, it executes fine.
We have given "Everyone" FULL CONTROL of all the folders that are affected by the batch file, and it still does not work.
I am out of ideas at this point. It just does not work unless it's an admin.
Are we missing something here?
Row-level Security: Permissions Required On Base Table?
Jun 2, 2007I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.
Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.
Execute Dynamic SQL Stored Proc Without Specifying Table Level Permissions
Sep 17, 2007I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
SQL Server Admin 2014 :: Column Level Data Encryption
Jun 17, 2015I need to encrypt some column level data in multiple tables in SQL server 2014. I've never tried encryption in SQL server 2014. How can I achieve it?
View 4 Replies View RelatedHow To Determine Which Service Pack Level Your SQL Server Is Running And Column Name Spacing Bug?
Aug 10, 2007I have visual studio 2005 with XP home. I'm trying to find out which service pack my SQL server is running as I've been told that Service pack 2 has a few bugs.
For example ADO Applications Report a Syntax Error When Column Names Contain Spaces. This appears to be an error i am recieving. I just wanted to confirm that it is a microsoft bug rather then something Im doing wrong.
Microsoft meantions the bug here: http://support.microsoft.com/kb/264687
I've been looking everywhere. I do not have the "query analyzer window" where you can find this out as I have VS 2005. How would I find this out.
Thank you
Column Level Constraints
Jul 17, 2001Hello all.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
tia,
Jeremy
Corruption At Column-Level~~
Dec 22, 2007Is it possible to have a corruption in one of the columns in a table at a specific row? If yes, what could be the possible causes for such corruption?
Why I am asking? Because it happened that I wasn't able to read the value of a specific column but I was able to update it with other value! it sounds weird if you are able to write but not to read!!
**I wasn't able to read but after I updated it with another value...it was readable...
Please advise...??
Concurrency Down To The Column Level - Sql
May 15, 2007
Using VS05 pro SP1...
(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)
This is for a telephony application using Last In Wins concurrency...
Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.
We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...
- Fill table A and table B identically
- change column1 value in table A
- Update table A
- change column2 value in table B
- Update table B - this will generate a concurrency error.
Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.
So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.
I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.
Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.
Thanks!
Bob
Column Level Security
Apr 29, 2008
Hey,
I have 3 columns in a table Ex:
Select Column1,Column2,Column from TableName
No. 1. Person A should have permission to read values only (Column1, Column3 of the table) -
2. Person B, should have permission to read only (Column 2).
Here my question is , I have to write one single stored procedure to statisfy both conditions. Which means, if person A execute this stored procedure , he shoud get only column 1 & 3 values . similarly other person b should get column 2 value. Ex:
Column 1 - Empid
Column 2 - SSN (Only for Top user display)
Column 3 - Join Date
Person A & B as a SQL or Windows login
Thanks
Global Column Level Security - Best Way?
Apr 30, 2008
Hi,
I am trying to find the best way to implement a complex column based security.
Basically i need to secure data among lot of tables with millions of rows, so that some users may see some columns and others see diferent ones...
Whats the best way to implement this kind of procedure securely and flexible?
Best Regards,
Permissions On A Column
Jul 20, 2005hi,i run a sqlserver 2000 and im having problems setting a permission acolumn in a table..under a database i have a User that has dataread rights on each tablein the database, but in one table i want to prevent the user fromseeing a column in one perticular table.i have created the user under security and then i choose the databaseuser - properties..and i set a X in the specified column...when i log on as the datareader user i cant see any colummn at all inthe table..what have a done wrong?Steve
View 3 Replies View RelatedT-SQL (SS2K8) :: Combining Result In Column Level
May 14, 2014Below is my sample table and data
With Item as(
Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all
Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all
Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all
Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all
Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all
Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all
Select 7 as ItemId,'ItemName7' as ItemName,700 as position),
Mapping as (
Select 1 as Parent, 2 as child union all
Select 1 as Parent, 3 as child union all
Select 1 as Parent, 4 as child union all
Select 5 as Parent, 6 as child union all
Select 5 as Parent, 7 as child )Expected Result:
ParentItemIdParentItemNameParentpositionChildItemIdChildItemNameChildposition
1ItemName11002ItemName2200
1ItemName11003ItemName3300
1ItemName11004ItemName4400
5ItemName55006ItemName6600
5ItemName55007ItemName7700
I was thinking to achieve using union all but if i use union all it will combine the result in rows level. but i need in column level.
Unable To Resolve Column Level Collations
May 14, 2008Hi,
When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.".
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.
On further trial and error I found that if I remove the fast load option, it works without glitch.
Its kind of wierd though....
Any body has faced this situation?
Thanks
Karunakaran
Native Exception On Column-Level In SQLCE
Nov 30, 2007Hi,
I have developed a Smart Device Application using VB.NET 2003 on top of Windows Mobile 2003 platform which is running on Symbol PPT 8846, a model used in our construction sites for gathering data bar-coded on Pipes, trucks...etc. This application uses SQL CE as its back-end database and since its difficult to cover our sites with WI-FI coverage, most of the time the application is running offline. As for that, on a daily basis, synchronization is done in the offices before the system is released into sites. I have been doing several implementations in several projects and till now everything is running fine but until I got a native exception on exporting the data back into the server database. The server database or main database is using Fox Pro and this is what led me to build a Web Service for communication between SQL CE and Fox Pro. Anyways, going back to that mysterious exception, I did some of my intensive debug on what caused it. As a start, I went through the code line by line to check if there was a memory leak from P/Invokes; but I was shocked that the exception was raised from "SQLCEDataAdapter.Fill(Dataset)" function. First thought came to mind was to hard reset and to re-install the application but no luck. Since it was about SQLCE, I moved towards checking the data found in the SQLCE database and tried to de-fragment the data found under the table needed for export. What I mean by de-fragmenting is that I grouped the data under different classes or families and created different tables depending on these classes. Then tried exporting again and Bingo the data was exported except one record...That was so weird!! huh? (The database had 515 records)
So I went for the Query Analyzer to try to view that record but no hope, it was taking too long to view the record, I just left it for more than 15 minutes and still no response from the Analyzer...it was so strange since there was only one record in that table!!!?!!! As going further, I was so curious why this record causing that native and inability to be viewed by the Analyzer?? I did testing on the column-level of that record and found out that you can view all the columns except two : nvarchar(200) and nvarchar (20)....I was able to resolve this problem with an SQL statement to update both columns with empty or dummy values...!!!!!
But after this resolution, I really don't know how it could be controlled later on!! and why it happened??
I would be glad if anyone can give me any advise on why such problem popped out and especially on the column-level in SQL CE?? And how to overcome it?
Thanks for your time and effort,
Looking forward to hear from as soon as possible...
Just for Clarification:
.NET CF 1.0 SP3
SQLCE 2.0
were used
Information For Column-Level Merge Replication
Aug 22, 2005Please provide me a column-level merge replication in SQL Server 2005. In the SQL Sever books online, I could able to get very few informaiton about this topic. Please provide me a best practices for using this kind of conflict resolution in Merge-replication.
View 5 Replies View RelatedSetting Column Permissions
Jan 7, 2001Howdy
I know you can set permissions on a column via T-SQL, can it be done via Enterprise Manager? If so, how do you do it?
I've looked everywhere but dont seem to be able to find how to do in EM.
Thanks
W.
Really Challenging Complex Column Level Processing Question
Oct 19, 2007Hi,
I have to implement a complex algorithm that processes each row and each column.I have bunch complex steps at the end of which a table gets populated with the processed columns.
My question is as to what is the best way to do this?CLR integration or T-SQL?
Also I would appreciate any ideas as to how to go about using either approaches.
One of the steps of processing involved per column is as follows:-
1)For each column,find successive invalid values from the start of the column.Invalid value= any value <0
2)find the invalid value depth of each column(no of successive invalid values from start)
3)If after these invalid vlaues,there is a valid value and another invalid value,replace current invalid value with valid value.
ie replace invalid value only if it has a valid value above it.
4)find the column with maximum invalue value depth and delete that many rows from the table.
Here's am example:-
Suppose there are 2 columsn colA and ColB.The columns have different datatypes eg decimal,int,string etc.
For simplicity colA and colB are ints.
RowID is keeping track of the row number.
suppose colA has the following data
RowID ColA
-----------
1 0
2 -5
3 -3
4 1
5 4
6 -9
7 5
8 8
Step1)successive invalid values from start=0,-5,-3
Step2)Invalid value depth=3(because there are 3 rows from step 1)
Step3)0,-5,-3 do not have any valid value above them.But -9 has a valid value 4 above it.So replace -9 with 4.
so colA after the algo will look as follows
RowID ColA
------------
1 0
2 -5
3 -3
4 1
5 4
6 4(replaced -9 with 4)
7 5
8 8
Now do the next column colB
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 -5
8 -8
Step1)successive invalid values from start=-6,0,0,-7
Step2)depth of invalid values=4
Step3)Next invalid value -5 occurs at RowID 7
and has a valid value 8 above it.
Replace -5 with previous valid vlaue ie 8.
RowID 8 has invalid value -8 .Its previous invalid value(-5) got replaced by a valid value 8.So replace RowID8 also with value of RowID 7 ie 8
Output at the end of these steps
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 8(replaced -5 with 8)
8 8(replaced -8 with 8)
Step4:Get the maximum invalid value depth
In this case ColB had depth=4 which is greater than ColA which had dept=3.so delete 4 rows from the beginning of the table
So the table will be
RowID colA colB
----------------------------------------
5 4 4
6 4(replaced -9 with 4) 8
7 5 8 (replaced -5 with 8)
8 8 8(replaced -8 with 8)
Thanks in advance for your help
coolaqua
SQL Security :: User Permissions For XML Column
Jul 7, 2015We have a user who is set to following permission on a DB,
server role: public
db permission: db_datareader
But when user tries to use following query on xml column he getting error as below,
SELECT ticket_id FROM dbo.Form WITH(NOLOCK)
WHERE LEN(form_document.value('(/Form_Fields/Form_Field[@field_type_desc="Number"]/@field_value)[1]','varchar(100)')) > 0
Error:
The EXECUTE permission was denied on the object 'testDB_Form_Schema', database 'testDB', schema 'dbo'.Do i have provide any additional permissions to query xml content ?
A T-SQL Function To Check Column Level Data For Special Charaters
Nov 30, 2007I need to create a function that will check data inputted by a user into a column anc check for special charaters. If any of these exist then block the insert.
Any help will be appreciated!
Paul
Generates Msg 107, Level 16, State 2, Line 2 - Column Prefix Does Not Exist (Oh Yes It Does.. Anyone Got A Solution ?)
Aug 8, 2007
hi All,
Following code generates error 107 (SQL Server 2000, SP3a):
SELECT
*
FROM
dbo.AssessmentItemHierarchy Parent
INNER JOIN dbo.RaterCategory
INNER JOIN dbo.RaterType
ON RaterCategory.ID = RaterType.RaterCategoryID
INNER JOIN dbo.AssessmentRater
ON AssessmentRater.RaterTypeID = RaterType.ID
INNER JOIN dbo.AssessmentItem
ON AssessmentItem.ID = Parent.ChildItemID
INNER JOIN dbo.Assessment
ON AssessmentItem.AssessmentID = Assessment.ID
INNER JOIN dbo.AssessmentResponse
ON AssessmentResponse.AssessmentItemID = AssessmentItem.ID
ON AssessmentResponse.AssessmentRaterID = AssessmentRater.ID
INNER JOIN dbo.ImageInstance
ON ImageInstance.ID = RaterType.ImageInstanceID
INNER JOIN dbo.AssessmentItem ParentGUID
ON Parent.ParentItemID = ParentGUID.ID
Error on Run:
Msg 107, Level 16, State 2, Line 2
The column prefix 'Parent' does not match with a table name or alias name used in the query.
The only work-around I have found is a rewrite of order:
SELECT
*
FROM
AssessmentItemHierarchy Parent
INNER JOIN RaterCategory
INNER JOIN RaterType
INNER JOIN AssessmentRater
INNER JOIN AssessmentItem
INNER JOIN Assessment ON AssessmentItem.AssessmentID = Assessment.ID
INNER JOIN AssessmentResponse ON AssessmentItem.ID = AssessmentResponse.AssessmentItemID
ON AssessmentRater.ID = AssessmentResponse.AssessmentRaterID
ON RaterType.ID = AssessmentRater.RaterTypeID
ON RaterCategory.ID = RaterType.RaterCategoryID
INNER JOIN ImageInstance
ON RaterType.ImageInstanceID = ImageInstance.ID
ON Parent.ChildItemID = AssessmentItem.ID
INNER JOIN AssessmentItem ParentGUID ON Parent.ParentItemID = ParentGUID.ID
Not very pretty... anyone know why I'm getting this error message with the first version of my query?
Miss Match Between Column Type In A Cursor. Msg 8114, Level 16, State 5
Nov 16, 2006hi
I have a float column . only in cursor I get a type missmatch.
does any one knows it ?
the error
Msg 8114, Level 16, State 5, Line 14
Error converting data type varchar to float.
the code
DECLARE @RON_FLOAT FLOAT
DECLARE RON_CURSOR CURSOR FOR
SELECT RON_FLOAT
FROM RON_TABLE1
OPEN RON_CURSOR
FETCH NEXT FROM RON_CURSOR
INTO @RON_FLOAT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'VALUE IS ' + @RON_FLOAT
FETCH NEXT FROM RON_CURSOR
INTO @RON_FLOAT
END
CLOSE RON_CURSOR
DEALLOCATE RON_CURSOR
the code for the table
CREATE TABLE [dbo].[RON_TABLE1](
[RON_FLOAT] [float] NULL,
[RON_CHAR] [nchar](10) COLLATE Hebrew_BIN NULL
)
Unable To Resolve Column Level Collations. Bulk-copy Cannot Continue
May 13, 2008Hi,
When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.".
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.
Some body can shed light on this?
Thanks
Karun
Reporting Services :: SSRS Interactive Filter At The Column Level (Like In Excel)?
May 21, 2015Is it possible to have Interactive Filters in an SSRS report like in Excel at Column Level as highlighted below.
View 2 Replies View RelatedColumn Level Constraints... I Need A Constraint, Not A Rule, Or A Trigger Or A Stored Proc... Thnx.
Jul 17, 2001Hello all.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
tia,
Jeremy
SQL Security :: Enforcing Unique Constraint On A Column Encrypted With Cell Level Encryption?
May 11, 2015I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!'
   CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs'
   CREATE SYMMETRIC KEY ERKEY
   WITH ALGORITHM = AES_256
   ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
       Email
      -------
   1 | 0x00703529AF46D24BA863A3534260374E01000000328909B51BA44A49510F24DF31
C46F2E30977626D96617E2BD13D9115EB578852EEBAE326B8F3E2D422230478A29767C
   2 | 0x00703529AF46D24BA863A3534260374E01000000773E06E1B53F2C57F97C54370FECBB45B
C8A154FEA5CEEB9B6BB1133305282328AAFAD65B9BDC595F0006474190F6482
   3 | 0x00703529AF46D24BA863A3534260374E01000000C9EDB1C83B52E60598038D832D34
D75867AB0ABB23F9044B7EBC76832F22C432A867078D10974DC3717D6086D3031BDB
But, how do I work around this?
High Level && Detail Level Design Documents
Nov 19, 2007
Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance
Matrix - Centering Higher Level Column Group Values In Viewing Area Instead Of Entire Cell Band Width
Dec 28, 2007I have 2 higher level column groupings of month name and year above my actual date groups. It looks a little weird aligning them left but there is no guarantee that centering them will even allow them to show until I've scrolled right to the middle of the cell width that they occupy.
Is there a feature that comes with, or a well known trick for making them center in the area that is being viewed instead of the potentially very wide cell that they occupy?