I've done this a couple of weeks ago -
Created LOGIN
, ROLE
and USER
. Added USER
to ROLE
and granted explicit permissions to the ROLE
itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.
First I created the login:
IF NOT EXISTS
(
SELECT
"name"
FROM
"master"."dbo"."syslogins"
WHERE
"name" = 'your_login'
)
BEGIN
CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF;
END
I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.
Followed by the user:
IF NOT EXISTS
(
SELECT
1
FROM
"sys"."database_principals"
WHERE
"name" = N'your_username'
)
BEGIN
CREATE USER your_username FOR LOGIN your_login;
END
And the role third:
IF NOT EXISTS
(
SELECT
1
FROM
"sys"."database_principals"
WHERE
"name" = N'your_role_name'
)
BEGIN
CREATE ROLE "your_role_name";
END
Finally I added the user to the created role:
EXECUTE sp_addrolemember N'your_role_name', N'your_username';
After that, permissions can be set for each type of object, eg
GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;
You don't have to add denydatareader
or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT
permission doesn't include the SELECT
permission on the same object. ( at least in my case it didn't ;) )
If you have problems executing or selecting from your UDF
, check if you have granted all permissions on the referenced objects in the UDF
itself!
Update after Cobus' update in OP:
I've executed the queries you provided based on my initial answer, results following:
select * from dbo.testtable
-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
update dbo.testtable set id = 4 where id = 2
-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
-- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
update dbo.testtable set id = 4
-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
insert into dbo.testtable (id) values (5)
-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
It behaves as intended. Do you have any further permissions granted / set or properties which allow you to access dbo-schema?
New in SQL Server 2012 and also in 2014 are two new encryption functions that will return a binary stream as you are describing. CREATE CERTIFICATE was also updated to take BINARY input for public and private keys.
CERTENCODED will return the public key portion of the certificate in BINARY form.
CERTPRIVATEKEY will return the private key portion of the certificate in BINARY form. this is encrypted so the password to decrypt it would also be needed.
The above could be saved in a binary form and written to a disk, or stored in textual representation of the binary form and written to disk.
Should you need to create it again, use the BINARY options of the CREATE CERTIFICATE function.
Edit: There is nothing in 2008R2 that can do the same that I know of through system functions.
Best Answer
The query returns a float data type, which is a binary structure that has no comma or dot decimal separator. It is the rendering application that converts the value into a string format for display purposes, which can honor the client's regional settings if programmed to do so.
Although you could change the T-SQL to return a formatted string like the example below, be aware client sorting will use string instead of numeric rules. Note this example explicitly specifies culture
en_US
, which uses a dot decimal separator, to override the current session language setting. Also addedORDER BY
for deterministic results with theTOP
clause.